[閒聊]無聊但是不閒的時候寫的 mysql41+概念篇

phpBB-TW Knowledge Base
收集網友們在竹貓所發表的教學主題或文章,以利分享!
(僅供瀏覽,由版主群維護)
回覆文章
神川小羽
調皮の小羽
調皮の小羽
文章: 1461
註冊時間: 2004-05-01 05:55
來自: 謎樣之筱語
聯繫:

[閒聊]無聊但是不閒的時候寫的 mysql41+概念篇

文章 神川小羽 »

以下內容主要是傳遞概念與思維的方向,並不是告訴你用哪種步驟可以去解決什麼
但是如果你了解這些概念你就應該能夠解決你遇到這些相關的問題。

MySQL在4.1以後版本最主要的革新有兩項,其中一個是驗證密碼的方式(特別說明! 密碼的革新只有4.1跟5.1這個版本)
4.1剛出來的時候許多人對於密碼的設置是哀號遍野,不過這問題目前已經沒有那麼困擾了
但是既然提到了這項東西,那麼我下面也會順便解釋一下是爲何會造成這樣的問題。
在來第二個革新就是開始支援UTF-8的字元集以及連線校對的指令,這個非常重要並且也是我寫這篇最主要的主角。

先來解釋密碼的革新部份好了

早期MYSQL的密碼是存放於MySQL資料庫(這個資料庫是安裝時就會產生的)其中的資料表user裡面的
我指的早期是4.1以前的版本。這樣說可能不是那麼容易明白,所以我準備了兩個環境來解釋

我先說明一下兩個環境的概況
=======================
阿呆1號 - 環境
OS:Win XP sp2
PHP:4.4.4
MySQL:4.024
phpMyAdmin:2.7.0-pl1
=======================
阿瓜2號 - 環境
OS:Linux Fedora 6
PHP:5.1.6
MySQL:5.0.27
phpMyAdmin:2.10.0.2
=======================

下圖可以看到這是4.0.24的版本
圖檔
下圖可以發現密碼的欄位的型態是varchar16
圖檔
也就是說在MySQL4.1以前的版本的密碼可以存放的最大長度是16(這是密碼欄位的最大寬度)

4.1跟5.1版本的密碼長度則就比較長了,長度是41(只有4.1跟5.1的版本的密碼長度41)
不過我臨時找不到能夠讓我攝圖的4.1的伺服器,所以我就用我這台MySQL5.0.27的版本來攝圖
(基本上4.1以上到的密碼欄位寬度是一樣的都是41)
圖檔
你在下圖可以發現到寬度已經不像4.1版本之前的是16了,而變成了41
圖檔
密碼用的加密演算PASSWORD()通常的長度16,但是4.1跟5.1的版本用PASSWORD()進行加密的長度是41
感覺很混亂對不對,的確很多仍在使用4.1的版本是很甘苦的,我要在更詳細的解釋清楚一下差別

MySQL4.1以前版本
---- 使用PASSWORD函數 加密的密碼的字串總長度 = 16
---- 密碼欄位寬度 = 16

MySQL4.1版本
---- 使用PASSWORD函數 加密的密碼的字串總長度 = 41
---- 密碼欄位寬度 = 41

MySQL5.0 的版本
---- 使用PASSWORD函數 加密的密碼的字串總長度 = 16
---- 密碼欄位寬度 = 41

MySQL5.1 的版本
---- 使用PASSWORD函數 加密的密碼的字串總長度 = 41
---- 密碼欄位寬度 = 41

可以發現4.1跟5.1的密碼加密方式最特別,因為只有4.1跟5.1的版本加密出來的加密演算長度為41
並且還有一個特別的地方那就是比較長的密碼的開頭一定是由*開頭的
如果你發現你的資料庫的密碼的值開頭為*那就表示是使用4.1或是5.1版本的PASSWORD 產生出來的,這有識別的作用。

關於密碼的故事我想就此打住了,畢竟這不是我寫這篇主要的內容。

主要的主角就是MySQLl4.1版本終於支援純正的UTF-8編碼,或許你可能會覺得怪怪的
為何我要刻意加上「純正」兩個字對吧,其實在4.1以前的版本並沒有真的支援UTF-8,下圖就是
攝取我的4.0.24版本的環境,你可以發現的確真的沒有UTF-8編碼對不對呢
圖檔
這張圖片可以看的出來,資料庫預設的編碼是採用「latin1字元集」並且提供了許多其他的字元集,但是就是沒有UTF-8
你可能會納悶為何那些還在使用甚至是MySQL3.X的資料庫,他們的網站也是用UTF-8編碼的,不是不支援嗎?

其實這些網站並存入資料庫的是以「latin1字元集」存進資料庫的(也就是西歐語)
因為不支援嚕,所以MySQL用預設的「latin1字元集」來存的。

鬱悶了吧,再這邊我要強調一個重點
在MySQL4.1以前的版本以UTF-8架設的網站都不是真的使用UTF-8編碼存入資料庫的
而這樣也是可以用不過還是有差異的,這是個遺憾….但是早期我們都這樣來弄。

我們假設有一段字內容是『小羽調皮又愛搗蛋』用UTF-8寫入資料庫
在4.1以前的版本使用latin1字元集對於這幾個字的字元數認定是24
但是使用4.1(含或以後)的版本用UTF-8寫入並正確的使用utf8為指定的字元集來進行連線,資料庫認定的字元數則是8

我要強調的是,這並不是在說UTF-8一個字有多少『敗特』,而是有多少個字元,這是爲了要解決長期以來的痛
以前資料庫最常遇到的難題就是中文字如果在BIG5環境下的時候長度其實是2英文數字則是1
這樣造成很難正確切割一段字串,而現在使用UTF-8也會遇到這樣的問題,因為老是不知道該如何才能正確的切斷字串。

真正的utf-8對於字元應該要是一個字元就是一個字元,不論這字是英文還是中文甚至是韓文日文,我再舉例

『我最喜歡ncc新聞』

那麼這段文字如果我要攝取前6個字元,正確的應該是要抓取到「我最喜歡nc」
如果你有過將phpBB轉編碼成UTF-8並且不是使用MySQL4.1+utf8_general_ci
或是使用的資料庫版本是4.1以前的版本幾乎都會遇到灌入時主題因為超過了欄位指定的長度而被切斷的慘劇
當時對於UTF-8的技術不夠普及所以很多人都犯過這樣的錯誤(包括我)。

這是因為phpBB2.x對於主題的長度預設值60,所以當然下場就是被切斷,切斷就算了
更慘的是不是精準的切斷,這樣就延生出被切斷後字串的最後一個字給切壞了。

用說的可能還不太有說服力,所以我還是實際用行動來解釋這個東西,請先看下圖。
圖檔
這是我在4.0.24下的SQL指令查詢的結果圖片『注意 這範例圖片是MySQL4.0.24的環境』
由這張圖片可以知道4.0.24的資料庫使用將UTF-8的文字以【latin1字元集】存入
所以一個字元是3,那麼我要求取出6就變成只有取出『讓討』這樣而已了。

在來我們來看看在使用MySQL4.1以上 + utf8_general_ci 的資料庫『這範例圖片是MySQL5.0.27的環境』
圖檔
這樣的結果就非常令人滿意了,我要求取出前6個字元我的MySQL5這台伺服準確的把『讓討論區的連』取出來

如此便可以發現,同樣的語法在不同的環境出來的結果是不同的,並且也證明了真正支援
UTF-8的MySQL4.1以上版本能夠正確精準的取出你指定要的字元數。



另外還有個很重要的概念,MYSQL4.1以上必須要正確的告訴資料庫要使用哪種方式來連線
這樣說可能讓人有點迷惑。

簡單來說就是當你撰寫程式或使用的程式在要跟資料庫連線的時候,必須先告訴資料庫你這次連線要使用哪種字元集
所以用set的指令下達參數讓資料庫知道,如果你沒有告訴資料庫你要使用哪種字元集來連線的話,資料庫將使用他的預設值。

在這之前我先來說說『預設值』好了,我們先來看看下方這張圖片,有三個重要的預設值
在這張表上所謂的整體值,就是資料庫預設默認的預設值。而下表中可以看的出來(注意 我說的是整體值)

character set client 的『整體值』= latin1
character set connection 的『整體值』= latin1
character set results 的『整體值』= latin1
圖檔
如果你沒有修改過預設值而且你的資料庫是4.1(含)以上的話,你看到的情況應該也是跟上圖一樣才對。

簡單來說,在4.1(含4.1)以後的版本都應該要在連線時先行宣告你要使用哪種字元集
你不宣告資料庫就會使用默認的字元集來進行這次的連線。那麼該怎樣宣告呢
基本上會使用到三個宣告分別是

SET character_set_client = x;
SET character_set_results = x;
SET character_set_connection = x;

現在你要必須要認識這三個參數代表的涵義
似乎有寫朋友雖然幫忙解決其他人對於編碼的問題卻沒有告訴他這些東西代表什麼,這樣好像有點可惜。

※並且請注意在下達編碼的時候UTF-8編碼請寫成小寫的utf8 並且沒有『-』減號

基本上4.1(含)以上的查詢流程必須要先告訴資料庫一些東西。
(character_set_client) = 告訴資料庫送來的資料是哪種編碼
(character_set_connection) = 告訴資料庫用哪種編碼來連結,也可以說成要把資料轉成哪種編碼
(character_set_results) = 告訴資料庫用哪種編碼來顯示這次連線的結果

這樣看起來好像還是很難懂,那我們把上面的涵義在白化一些,想想看你現在要連線資料庫了
那麼資料庫其實並不是很清楚你過來的資料是哪種編碼
所以你對資料庫說:「我送來的資料是我指定的character_set_client」
這下資料庫就知道你送來的資料的編碼了

那麼資料庫再問你:「要把資料進行怎樣的處裡呢?」
你就用character_set_connection 告訴資料庫
請幫我把我送來的資料轉換哪種編碼進行處理

資料庫還有一個疑問於是又問你,資料庫問你:「請問要怎樣呈現呢?」
這時候則用(character_set_results)來告訴資料庫
我還是要強調,你如果不告訴資料庫的話它就是用預設值來處理。

或許你還不是很懂這樣的應用,所以下面我舉例來解析這些差異

但是我仍強烈的建議最好存的方式與呈現的方式都是同一種編碼 不然很可能會出現奇怪的問題
例如用UTF-8編碼的網站用latin1 不就延生出我上面提到的字元數無法準確計算的問題嚕。

A網站的網站編碼用UTF-8並且資料庫也是UTF-8來儲存資料
B網站的網站編碼用BIG5但是資料庫是UTF-8來儲存資料
C網站的網站編碼用BIG5並且資料庫也是BIG5來儲存資料
D網站的網站編碼用UTF-8但是資料庫是BIG5來儲存資料 (我想應該沒有人會用這樣的架構吧)

A網站的連線參數
SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;
註: 可以用↓這樣的語法來代表三種都是utf8 (在三種條件都一樣的時候可以這樣下)
SET names utf8;

B網站的連線參數
SET character_set_client = big5;
SET character_set_results = big5;
SET character_set_connection = utf8;

C網站的連線參數
SET character_set_client = big5;
SET character_set_results = big5;
SET character_set_connection = big5;
註: 可以用↓這樣的語法來代表三種都是big5 (在三種條件都一樣的時候可以這樣下)
SET names big5;

D網站的連線參數
SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = big5;

以上的屁話其實蠻多的,事實上我寫這篇我也不知道是要表達什麼,就當作純粹的交流,或許我說的並不是正確的。
我也不會保證我說的就是對的,而你可能或許會認為我某些地方的描述是錯誤的
這樣也很好啦,如果你願意告訴我我哪裏寫錯了也可以告訴我,有錯誤並且找到真實的答案有助於加強記憶。
最後由 神川小羽 於 2007-04-28 03:37 編輯,總共編輯了 1 次。
-.-
michaelchain
星球公民
星球公民
文章: 129
註冊時間: 2006-10-17 03:13

文章 michaelchain »

精彩的文章要推一下,允許轉載嘛?

同時再推薦另一篇英文的教學文章:
http://dev.mysql.com/tech-resources/art ... icode.html 全文鏈接

[部分內容]
Unicode and Other Funny Characters

This is the second of our on-going series of articles that explain some the new features in MySQL 4.1, which as of this writing is in the gamma phase of its development cycle, on the way to a production-ready release in the near future.

By Jim Winstead

One of the major new features in MySQL 4.1 is strong Unicode support, along with support for specifying character sets at many different levels. This makes it much simpler to handle content in a wide range of languages in your applications, as well as making it possible to handle content in multi-byte character encodings that were not supported in earlier versions of MySQL.
※伺服器主機:Intel Core2 酷睿™2 E6300, 升級到4GB RAM了
※主機作業系統:CentOS 4 Linux
※我的 phpBB 版本:phpBB3.0.?
※我的網址: 中文PHPBB
神川小羽
調皮の小羽
調皮の小羽
文章: 1461
註冊時間: 2004-05-01 05:55
來自: 謎樣之筱語
聯繫:

文章 神川小羽 »

請隨意轉載沒關係,但是我建議你複製圖片放置另外的空間,因為我沒辦法保證我這些圖片我會保留多久
另外雖然我很努力的測試在文章的的內容進行各種求證,並且實際的在我的兩台伺服器中測試是否符合我的見解
但是我實在很難肯定我這些內容是不是任何會造成誤導的錯誤。
-.-
頭像
kkt
竹貓忠實會員
竹貓忠實會員
文章: 625
註冊時間: 2005-06-15 01:37
來自: H.K.
聯繫:

文章 kkt »

多謝小羽大的分享, 怪不得, 我就是一直有存疑為甚麼 phpBB utf-8 版本install SQL 寫入的 table 都是 Latin 1 校對
簡單來說,在4.1(含4.1)以後的版本都應該要在連線時先行宣告你要使用哪種字元集
你不宣告資料庫就會使用默認的字元集來進行這次的連線。那麼該怎樣宣告呢
基本上會使用到三個宣告分別是

SET character_set_client = x;
SET character_set_results = x;
SET character_set_connection = x;
雖然我都把 Latin 1 轉為 unicode_ci 是沒有問題, 但就不明所以..
看後有所領悟, 謝謝!!
舊站台經已結朿了, 學生們可到遊戲站玩玩 Flash Game (不必註冊) 免費 Flash Game
在此學到很多架站知識, 永遠多謝:
心靈捕手老師, ~倉木麻衣~, 依夢兒 及 神川小羽各位大大相助
chiouss
竹貓忠實會員
竹貓忠實會員
文章: 1741
註冊時間: 2002-11-29 18:19

Re: [閒聊]無聊但是不閒的時候寫的 mysql41+概念篇

文章 chiouss »

神川小羽 寫:MySQL5.X 的版本
---- 使用PASSWORD函數 加密的密碼的字串總長度 = 16
---- 密碼欄位寬度 = 41

可以發現4.1的密碼加密方式最特別,因為只有4.1的版本加密出來的加密演算長度為41
並且還有一個特別的地方那就是4.1的版本加密出來的密碼的開頭一定是由*開頭的
如果你發現你的資料庫的密碼的值開頭為*那就表示是使用4.1版本的PASSWORD 產生出來的,這有識別的作用。
我的機器上面用 MySQL 5.1 產生的 password 也是 41 個字喔..
圖檔
MySQL Reference Manual 上面也是寫 4.1 以後 (嚴格一點說是 4.1.2 以後) 都是這麼長。

就是因為 MySQL 4.1+ 對 Unicode 的支援讓很多人手癢去改 charset,才會有像這篇一樣自爆已後的心得。 :mrgreen:

然後也因為很多人對 charset 和 collation 的觀念錯誤或是沒花時間去了解,所以才會很多人到現在還停留在 MySQL 4.0 (or 3.23) 這些 EOL 的版本上。
神川小羽
調皮の小羽
調皮の小羽
文章: 1461
註冊時間: 2004-05-01 05:55
來自: 謎樣之筱語
聯繫:

文章 神川小羽 »

我修改過密碼長度的描述內容了,這樣的話應該用長密碼有4.1跟5.1
而我這台5.0則還是用短密碼 (懶得切圖了 圖好像有點肥-.-)
圖檔
-.-
~倉木麻衣~
竹貓忠實會員
竹貓忠實會員
文章: 1405
註冊時間: 2004-03-21 21:00

文章 ~倉木麻衣~ »

神川小羽 寫:而我這台5.0則還是用短密碼
比較可能的原因應該是以前曾用過OLD_PASSWORD()來設定密碼
或是啟動MySQL時有使用--old-passwords參數

在chiouss提到的文件裡有說到
  • The Password column must be wide enough to hold long hashes (41 bytes). If the column has not been updated and still has the pre-4.1 width of 16 bytes, the server notices that long hashes cannot fit into it and generates only short hashes when a client performs password-changing operations using PASSWORD(), GRANT, or SET PASSWORD. This is the behavior that occurs if you have upgraded to 4.1 but have not yet run the mysql_fix_privilege_tables script to widen the Password column.
  • If the Password column is wide, it can store either short or long password hashes. In this case, PASSWORD(), GRANT, and SET PASSWORD generate long hashes unless the server was started with the --old-passwords option. That option forces the server to generate short password hashes instead
謝絕所有私人訊息詢問外掛相關問題
有問題請直接於版上發表, 集思廣議絕對比專挑特定人士詢問來的好

竹貓禁止發表含破解相關的軟體, 違者砍文
不要跟我講別的地方都可以發, 為什麼竹貓就不行
免費不等於破解, 傻傻的搞不清楚
chiouss
竹貓忠實會員
竹貓忠實會員
文章: 1741
註冊時間: 2002-11-29 18:19

文章 chiouss »

神川小羽 寫:我修改過密碼長度的描述內容了,這樣的話應該用長密碼有4.1跟5.1
而我這台5.0則還是用短密碼 (懶得切圖了 圖好像有點肥-.-)
另一台機器,用 MySQL 5.0.37...
圖檔
我的想法和樓上相同 :Q
chiouss
竹貓忠實會員
竹貓忠實會員
文章: 1741
註冊時間: 2002-11-29 18:19

文章 chiouss »

~倉木麻衣~ 寫:
神川小羽 寫:而我這台5.0則還是用短密碼
比較可能的原因應該是以前曾用過OLD_PASSWORD()來設定密碼
或是啟動MySQL時有使用--old-passwords參數
大概是後者吧?他是直接產生 password hash,而不是給我們看 db 裡面已經 hash 好的紀錄喔

不然就是那個 MySQL source 有被改過 :mrgreen:
神川小羽
調皮の小羽
調皮の小羽
文章: 1461
註冊時間: 2004-05-01 05:55
來自: 謎樣之筱語
聯繫:

文章 神川小羽 »

這樣的確是有點詭異,因為我是直接用fc官方網路安裝的prm套件安裝的
我並沒有修改過預設值

也有可能真的是打包這套rpm的人有先修改過,我翻出my.cnf發現old-passwords是開啟的
-.-
hollowaysxp
竹貓忠實會員
竹貓忠實會員
文章: 535
註冊時間: 2004-06-01 22:39
來自: 仙境傳說回憶最美~台灣桃園
聯繫:

文章 hollowaysxp »

神川小羽 寫:我修改過密碼長度的描述內容了,這樣的話應該用長密碼有4.1跟5.1
而我這台5.0則還是用短密碼 (懶得切圖了 圖好像有點肥-.-)
圖檔
X環境 :-o
我比較喜歡用Putty連過去。沒有習慣開X Window
●架設主機作業系統:FreeBSD 6.2-RELEASE (自行架設)
●我的上網方式:FTTB+VDSL HINET 10M/2M
●我安裝的程式:Apache 2.0.59+ PHP Version 4.4.4 + MySql 4.1.21+phpMyadmin 2.9.1.1
●我的 phpBB3 版本:phpBB3.0.1
●我的 phpBB3 連結網址 http://nitcs.itrello.com/forum/
NITCS歡迎你
NITCS論壇
頭像
心靈捕手
默默耕耘的老師
默默耕耘的老師
文章: 8510
註冊時間: 2004-04-30 01:54
來自: Taiwan

Re: [閒聊]無聊但是不閒的時候寫的 mysql41+概念篇

文章 心靈捕手 »

小羽寫的這篇文章極富參考價值。 (:Y)

p.s.
移動至「教學文件庫」版面。
施比受有福,祝福您好運! ^_^
歡迎光臨★★心靈捕手★★ :: 討論區
https://wang5555.dnsfor.me/phpBB3/
回覆文章

回到「教學文件庫」