全面了解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)的檢索並不總是相同的，例如：

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)、使用合成的索引來提高大本文欄位的查詢效能。所謂合成索引就是根據大文本欄位的內容建立一個雜湊值，並且把這個值儲存在單獨的資料列中，然後就可以透過雜湊值找到資料行了。例如：

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欄位建立索引。例如：

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: ALL
possible_keys: idx_blob
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.00 sec)

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

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

三、浮點數和定點數

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

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

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

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类型。

以上是全面了解MySQL中的資料類型的詳細內容。更多資訊請關注PHP中文網其他相關文章！