Mysql 選擇合適的資料類型

1.1 選擇資料類型的基本原則

前提：使用合適的儲存引擎

選擇原則：根據選定的儲存引擎，確定如何選擇合適的資料類型

下面的選擇方案按儲存引擎分類：

1.      
MyISAM資料存放區引擎和資料列

MyISAM資料表，最好使用固定長度的資料列代替可變長度的資料列。

 

2．MEMORY儲存引擎和資料列

MEMORY表目前都使用固定長度的資料行儲存，因此無論使用CHAR或VARCHAR列都沒有關係。兩者都是作為CHAR類型處理。

3．InnoDB儲存引擎和資料列

建議使用VARCHAR類型

對於InnoDB資料表，內部的行儲存格式沒有區分固定長度和可變長度列（所有行都使用指向資料列值的頭指標），因此在本質上，使用固定長度的CHAR列不一定比使用可變長VARCHAR列簡單。因而主要因素是資料行使用的儲存總量。由於CHAR平均佔用空間多餘VARCHAR，因此使用VARCHAR來最下化需要處理的資料行的儲存總量和磁碟I/O是比較好的。

 

 

1.2 固定長度資料列與可變長度資料列

       3.2.1 CHAR與VARCHAR

A．CHAR和VARCHAR類型類似，但它們儲存和檢索的方式不同。它們的最大長度和是否尾部空格補充等方面也不同。在儲存和檢索過程中不進行大小寫轉換。

下面顯示了各種字串值儲存到CHAR（4）和VARCHAR（4）列後的結果。

值	CHAR(4)	儲存需求	VARCHAR(4)	儲存需求
‘’	‘    ‘	4個位元組	‘’	1個位元組
‘ab’	‘ab  ‘	4個位元組	‘ab’	3個位元組
‘abcd’	‘abcd’	4個位元組	‘abcd’	5個位元組
‘abcdef’	‘abcd’	4個位元組	‘abcd’	5個位元組

註：上表中最後一行的值運行在sql_mode=ANSI或為空白的模式下(即非strict 模式)

如果運行在strict 模式下，超過列長度的值不會儲存，並且會出現錯誤。

 

mysql> create table vc (v varchar(4),c
char(4));

Query OK, 0 rows affected (0.00 sec)

 

mysql> INSERT INTO vc values('ab  ','ab 
');

Query OK, 1 row affected (0.00 sec)

 

mysql> SELECT * FROM vc;

+------+------+

| v   
| c    |

+------+------+

| ab  
| ab   |

+------+------+

1 row in set (0.00 sec)

 

mysql> SELECT
CONCAT(v,'+'),CONCAT(c,'+') FROM vc;

+---------------+---------------+

| CONCAT(v,'+') | CONCAT(c,'+') |

+---------------+---------------+

| ab 
+         | ab+           |

+---------------+---------------+

1 row in set (0.00 sec)

結論是：char類型會刪除尾部的空格

 

1.2.2 text和blob

在使用text和blob欄位類型時需要注意下面幾點，以便充分發揮資料庫的效能：

1.      
BLOG和TEXT值也會引起自己的一些問題，特別是執行了大量的刪除和更新操作時。

刪除這種值會在資料表中留下很多片段，以後填入這些片段的記錄可能長度不同，為了提高效能，建議定期使用OPTIMIZE TABLE功能對這類表進行磁碟重組。

 

2.      
使用合成的索引。

合成的索引列在某些時候是有用的。一種辦法是根據其他列的內容建立一個散列值，並把這個值儲存在單獨的資料列中。接下來就可以通過檢索散列值找到資料行了。

但這種技術只能用於精確匹配的查詢(散列值對於類似>或>=等範圍搜尋操作符是沒有用處的)。

3.      
在不必要的時候避免檢索大型的BLOB或TEXT值。

例如，SELECT *查詢不是很好的想法，會毫無目的在網路上傳輸大量的值。這也是BLOB或TEXT標識符資訊儲存在合成索引列中對我們有所協助的例子。

4.      
把BLOB或TEXT列分離到單獨的表中。

在某些情況下，如果把這些資料列移動到第二張資料表中，可以讓你把原資料表中的資料列轉換為固定長度的資料行格式，那麼它就是有意義的。這會減少主表中的片段，使資料表得到固定長度資料行的效能優勢。還會使在主表上運行SELECT *時不會傳輸大量的資料。

 

 

3.3 浮點數和定點數

mysql> CREATE TABLE test (c1
float(10,2), c2 decimal(10,2));

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test values(131072.32,
131072.32);

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test;

+-----------+-----------+

| c1       
| c2        |

+-----------+-----------+

| 
13105.32 |  13105.32 |

| 131072.31 | 131072.32 |

+-----------+-----------+

2 rows in set (0.00 sec)

從上看到131072.32變成了131072.31，這就是浮點數的不精確性造成的。

在mysql中float、double或real是浮點數，decimal是定點數。

浮點數相對於定點數的優點是在長度一定的情況下，浮點數能夠表示更大的資料範圍。

缺點就是會引起精度問題。

 

所以在做選擇時，需要注意以下一點：

1、 
浮點數存在誤差。

2、 
對貨幣等精度敏感的資料，應該用定點數表示或儲存。

3、 
編程中，如果用到浮點數，要特別注意誤差問題，並盡量避免做浮點數比較。

4、 
要注意浮點數中一些特殊值的處理。