MySQL中的資料類型

MySQL中的資料類型

一、char和varchar類型

char和varchar類型類似，都用來儲存字串，但是它們儲存和檢索字串的方式不同。char屬於固定長度的字元類型，varchar屬於可變長度的字元類型。例如：對於char(4)和varchar(4)這兩種類型定義來說：

(1)、''在char(4)中佔了4個位元組長度，varchar(4)則只佔用一個位元組的長度；

(2)、'ab'在char(4)中佔了4個位元組長度，varchar(4)中則只佔用了3個位元組的長度；

(3)、'abcd'在char(4)中佔用了4個位元組長度，在varchar(4)中則佔用了5個位元組的長度；

為何在varchar類型中會多出一個位元組長度呢？這是因為varchar類型將這多出的一個位元組用於儲存varchar類型實際使用了多大的長度。char(4)和varchar(4)的檢索並不總是相同的，例如：

12345678910111213

mysql> create table char_and_varchar (v varchar(4),c char(4));Query OK, 0 rows affected (0.20 sec) mysql> insert into char_and_varchar values ('ab  ','ab  ');Query OK, 1 row affected (0.33 sec) mysql> select concat(v,'cd'),concat(c,'cd') from char_and_varchar;+----------------+----------------+| concat(v,'cd') | concat(c,'cd') |+----------------+----------------+| ab  cd        | abcd          |+----------------+----------------+1 row in set (0.35 sec)

由於char是固定長度的，所以它的處理速度比varchar快的多，但其缺點是浪費儲存空間，程式需要對尾部空格進行處理等缺點，所以多那些長度變化不大並且對查詢速度有較高要求的的資料可以考慮使用char類型來儲存。隨著MySQL版本的不斷升級，varchar資料類型的效能也將不斷提升，varchar類型的應用範圍更加廣泛。

在MySQL中，不同的儲存引擎對char和varchar的使用原則有所不同：

(1)、在MyISAM儲存引擎中，建議使用固定長度的欄位類型代替可變長度的欄位類型。
(2)、在Memory儲存引擎中，目前都是用固定長度的資料行儲存，因此無論是char還是varchar類型，都將轉化為char類型處理。
(3)、在InnoDB儲存引擎中，建議使用varchar類型。

二、TEXT和BLOB

在儲存少量字串的時候，可以使用char和varchar資料類型。在儲存較大的文本時，通常會選擇使用text或BLOB。兩者之間的主要差別是：BLOB能用來儲存位元據，例如：照片，而text只能用於儲存字元類型資料。text和BLOB中又分別包括text、mediumtext、longtext和blob、mediumblob、longblob三種不同的類型。它們之間的主要區別是儲存文本的長度不同和儲存位元組不同。

使用BLOB和TEXT類型應注意的一些問題：

(1)、BLOB和TEXT會引起一些效能問題，特別是在執行了大量的刪除操作時。刪除操作會在資料表中留下很大的“空洞”，以後填入這些“空洞”的記錄在插入效能上會有影響。為了提高效能，應定期使用OPTIMIZETABLE功能對這類表進行磁碟重組，避免空洞導致效能問題。

(2)、使用合成的索引來提高大本文欄位的查詢效能。所謂合成索引就是根據大文字欄位的內容建立一個散列值，並把這個值儲存在單獨的資料列中，然後就可以通過散列值找到資料行了。例如：

1234567891011121314151617181920212223242526272829

mysql> create table t (id varchar(100),content blob,hash_value varchar(40));Query OK, 0 rows affected (0.03 sec) mysql> insert into t values (1,repeat('beijing',2),md5(content)); Query OK, 1 row affected (0.33 sec) mysql> insert into t values (2,repeat('beijing',2),md5(content)); Query OK, 1 row affected (0.01 sec) mysql> insert into t values (2,repeat('beijing 2008',2),md5(content));Query OK, 1 row affected (0.01 sec) mysql> select * from t;+------+--------------------------+----------------------------------+| id  | content                  | hash_value                      |+------+--------------------------+----------------------------------+| 1    | beijingbeijing          | 09746eef633dbbccb7997dfd795cff17 || 2    | beijingbeijing          | 09746eef633dbbccb7997dfd795cff17 || 2    | beijing 2008beijing 2008 | 1c0ddb82cca9ed63e1cacbddd3f74082 |+------+--------------------------+----------------------------------+3 rows in set (0.00 sec) mysql> select * from t where hash_value=md5(repeat('beijing 2008',2));+------+--------------------------+----------------------------------+| id  | content                  | hash_value                      |+------+--------------------------+----------------------------------+| 2    | beijing 2008beijing 2008 | 1c0ddb82cca9ed63e1cacbddd3f74082 |+------+--------------------------+----------------------------------+1 row in set (0.00 sec)

合成索引只能用於精確匹配的情境，在一定程度上減少了磁碟I/O，提高了查詢效率。如果需要對BLOB、CLOB欄位進行模糊查詢，可以使用MySQL的首碼索引，即為欄位的前n列建立索引。例如：

12345678910111213141516171819202122232425262728293031323334

mysql> create index idx_blob on t (content(100));Query OK, 0 rows affected (0.09 sec)Records: 0  Duplicates: 0  Warnings: 0 mysql> show index from t \G*************************** 1. row ***************************        Table: t   Non_unique: 1     Key_name: idx_blob Seq_in_index: 1  Column_name: content    Collation: A  Cardinality: 3     Sub_part: 100       Packed: NULL         Null: YES   Index_type: BTREE      Comment: Index_comment: 1 row in set (0.00 sec) mysql> desc select * from t where content like 'beijing%' \G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t         type: ALLpossible_keys: idx_blob          key: NULL      key_len: NULL          ref: NULL         rows: 3        Extra: Using where1 row in set (0.00 sec)

(3)、不要在不必要是檢索大型的BLOB或TEXT欄位。

(4)、把BLOB或TEXT欄位分離到單獨的表中。

三、浮點數和定點數

浮點數一般用於表示含有小數部分的數值。當一個欄位被定義為浮點類型以後，如果插入資料的精度超過了該列定義的實際精度，則插入值會被四捨五入到實際定義的精度值，然後插入，四捨五入的過程不會報錯。MySQL中的float、double（real）用來表示浮點數。

定點數不同於浮點數，定點數實際上是用字串形式存放的，所以定點數可以更精確的存放資料。如果插入資料的精度大於實際定義的精度，則MySQL會發出警示，但資料按照實際精度四捨五入後插入（如果是在傳統模式下插入，則會報錯）。在MySQL中，用decimal（或numberic）來表示定點數。

用浮點數儲存資料會存在誤差，在精度要求比較高的情境（如貨幣），應該使用定點數來存放資料。例如：

12345678910111213

mysql> create table b (c1 float(10,2),c2 decimal(10,2));Query OK, 0 rows affected (0.37 sec) mysql> insert into b values (131072.32,131072.32);Query OK, 1 row affected (0.00 sec) mysql> select * from b;+-----------+-----------+| c1        | c2        |+-----------+-----------+| 131072.31 | 131072.32 |+-----------+-----------+1 row in set (0.00 sec)

四、日期類型

MySQL提供的常用的日期類型有：date、time、datetime、timestamp，日期類型的選用原則：

(1)、應根據實際需要選擇能夠滿足應用的最小儲存的日期類型；

(2)、如果要記錄年月日時分秒，且年代比較久遠，最好使用datetime類型；

(3)、如果記錄的日期要被多時區的使用者所使用，那麼最好使用timestamp類型。

本文永久更新連結地址：