mysql使用筆記（四）

標籤：

一、選擇合適的資料類型

1. CHAR vs VCHAR 
    char是固定長度的字元類型，而varchar是可變長度的字元類型。char(M)的資料列中，每個值都佔用M個位元組，如果某個長度小於M，mysql就會在它的右邊用空白字元補足，在檢索操作的時候最後一個非空白字元右邊的所有字元都會被去掉，因此需要注意，去掉右邊的空格有可能不只去掉補足的空格，還有可能去掉原來字串中右邊已有的空格！ 
    在varchar(M)的資料列中，每個值只佔用剛好夠用的位元組數再加上一個用來記錄其長度的位元組（即總長度為L+1個位元組）。 
    由於char固定長度，因此處理速度比vchar快，但是佔用空間比vchar大，在使用的時候需要具體情況具體分析，一般的原則是： 
（1）對於myisam表，盡量使用char，對於那些經常需要修改而容易形成片段的myisam和isam資料表就更是如此； 
（2）對於innodb表，盡量使用varchar。

2. TEXT vs BLOB 
    儲存少量字串時使用char或varchar，而儲存較大的文本時，選擇text或者blob。二者的區別是blob還可以儲存位元據，比片；而text只能儲存純文字資料。text和blob又分別細分為text,mediumtext,longtext和blob,mediumblob,longblob三種不同的長度類型。 
    blob和text值會引起一些效能問題，特別是在執行了大量的刪除操作時。 
刪除操作會在資料表中留下很大的“空洞”，以後填入這些“空洞”的記錄在插入的效能上會有影響。為了提高效能，建議定期使用 optimize table功能對這類表進行磁碟重組，避免因為“空洞”導致效能問題。 
    可以使用合成的索引來提高大文字欄位（blob或者text）的查詢效能。合成索引就是根據大文字欄位的內容建立一個散列值，並把這個值儲存在單獨的資料列中，接下來就可以根據檢索散列值找到資料行。但是，這種技術只能用於精確匹配的查詢（散列值對於類似<, >, >= 等範圍搜尋沒有用）。 
可以使用MD5(),SHA1(),CRC32()等來產生散列值。比如

    create table t(id varchar(100), context blob, hash_value varchar(40));    insert into t values(1, repeate(‘beijing‘,2), md5(context)),    (2, repeate(‘beijing‘,200), md5(context)),    (100, repeate(‘helloworld‘,20), md5(context));    select * from t where hash_value=md5(repeat(‘beijing‘, 2));

 

    如果需要對blob或text欄位進行模糊比對，mysql提供了首碼索引，即首碼%，比如select * from t where context=‘hello%‘。

3. 浮點數 vs 定點數 
（1）浮點數存在表示誤差，定點數採用字串進行儲存，精度高 
（2）對貨幣等精度敏感的資料，應該採用定點數進行儲存 
（3）編程中，如果用到浮點數，要特別注意誤差問題，並盡量避免做浮點數比較 
（4）要注意浮點數中一些特殊值的處理

4. 日期類型選擇 
（1）要根據實際需要選擇能夠滿足應用的最小儲存的日期類型。比如，如果僅需要記錄年份，則用1個位元組來儲存的YEAR，而不用考慮4個位元組的DATE 
（2）如果要記錄日期和時間，且記錄的年份久遠，最好使用DATETIME而不是TIMESTAMP，因為DATETIME的範圍比TIMESTAMP廣 
（3）如果記錄的日期需要讓不同時區的使用者使用，最好使用TIMESTAMP，因為TIMESTAMP和實際時區匹配

二、 mysql中的字元集

常用字元集比較：

字元集	是否定長	編碼方式	其他說明
ASCII	是	單位元組7位編碼	最早的奠基性字元
IOS-8859-1/latin1	是	單位元組8位編碼	西歐字元集
GB2312	是	雙位元組編碼	早期標準，不推薦使用
GBK	是	雙位元組編碼	不少系統支援
GB18030	否	2位元組或4位元組編碼	開始有一些支援，但資料庫不常見
UTF-32	是	四位元組編碼	UCS-4原始編碼，現在很少使用
UCS-2	是	2位元組編碼
UTF-16	否	2位元組或4位元組編碼	java和windows xp/nt等內部使用
UTF-8	否	1~4位元組編碼	互連網和unix/linux廣泛支援的unicode字元集

    其中，GBK每個漢字佔用2個位元組，而UTF-8每個漢字佔用3個位元組。

mysql使用筆記（四）