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或为空的模式下(即非严格模式)
如果运行在严格模式下,超过列长度的值不会保存,并且会出现错误。
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、 要注意浮点数中一些特殊值的处理。