程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MYSQL入門知識 >> MySQL中的數據類型

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

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入門知識