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

主題已鎖定
頭像
心靈捕手
默默耕耘的老師
默默耕耘的老師
文章: 8222
註冊時間: 2003-01-01, 09:01
來自: Taiwan

[轉貼] 無聊但是不閒的時候寫的 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的版本
image002.jpg
下圖可以發現密碼的欄位的型態是varchar16
image004.jpg
也就是說在MySQL4.1以前的版本的密碼可以存放的最大長度是16(這是密碼欄位的最大寬度)

4.1跟5.1版本的密碼長度則就比較長了,長度是41(只有4.1跟5.1的版本的密碼長度41)
不過我臨時找不到能夠讓我攝圖的4.1的伺服器,所以我就用我這台MySQL5.0.27的版本來攝圖
(基本上4.1以上到的密碼欄位寬度是一樣的都是41)
image006.jpg
你在下圖可以發現到寬度已經不像4.1版本之前的是16了,而變成了41
image008.jpg
密碼用的加密演算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編碼對不對呢
image010.jpg
這張圖片可以看的出來,資料庫預設的編碼是採用「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,所以當然下場就是被切斷,切斷就算了
更慘的是不是精準的切斷,這樣就延生出被切斷後字串的最後一個字給切壞了。

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

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

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



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

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

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

character set client 的『整體值』= latin1
character set connection 的『整體值』= latin1
character set results 的『整體值』= latin1
image016.jpg
如果你沒有修改過預設值而且你的資料庫是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 次。

--
資料來源:
http://phpbb-tw.net/phpbb/viewtopic.php?f=2&t=48740
主題已鎖定

回到「架站技術」