如何设计表选择合适的数据类型

在使⽤MySQL创建数据表时都会遇到⼀个问题，即如何为字段选择合适的数据类型。例如，创建⼀张员⼯表⽤来记录员⼯的信息，这时对员⼯的各种属性如何来进⾏ 定义？也许读者会想，这个问题很简单，每个字段可以使⽤很多种数据类型来定义，⽐如int、float、double、decimal等。其实正因为可选择的数据类型太多，才需要依据⼀些原则来“挑选”最适合的数据类型。

1. CHAR与VARCHAR

CHAR和VARCHAR类型类似，都⽤来存储字符串，但它们保存和检索的⽅式不同。CHAR属于固定长度的字符类型，⽽VARCHAR属于可变长度的字符类型。

表中最后一行只使用在mysql运行在非严格模式下，如果运行在严格模式下，超过列长度的值讲不会被保存，并且出现错误提示 

从CHAR(4)和VARCHAR(4)列检索的值并不总是相同，因为检索时从CHAR列删 除了尾部的空格。

由于CHAR是固定长度的，所以它的处理速度⽐VARCHAR快得多，但是其缺点 是浪费存储空间，程序需要对⾏尾空格进⾏处理，所以对于那些长度变化不⼤并且对 查询速度有较⾼要求的数据可以考虑使⽤CHAR类型来存储。

另外，随着MySQL版本的不断升级，VARCHAR数据类型的性能也在不断改进并 提⾼，所以在许多的应⽤中，VARCHAR类型被更多地使⽤。

在MySQL中，不同的存储引擎对CHAR和VARCHAR的使⽤原则有所不同，这⾥ 简单概括如下。

MyISAM存储引擎：建议使⽤固定长度的数据列代替可变长度的数据列。
MEMORY 存储引擎：⽬前都使⽤固定长度的数据⾏存储，因此⽆论使⽤ CHAR 或VARCHAR列都没有关系。 两者都是作为CHAR类型处理。
InnoDB存储引擎：建议使⽤VARCHAR类型。对于InnoDB数据表，内部的⾏存储格式没有区分固定长度和可 变长度列（所有数据⾏都使⽤指向数据列值的头指针），因此在本质上，使⽤固定长度的CHAR列不⼀定⽐使⽤可 变长度VARCHAR列性能要好。因⽽，主要的性能因素是数据⾏使⽤的存储总量。由于CHAR平均占⽤的空间多于 VARCHAR，因此使⽤VARCHAR来最⼩化需要处理的数据⾏的存储总量和磁盘I/O是⽐较好的。

1. TEXT与BLOB

⼀般在保存少量字符串的时候，我们会选择CHAR或者VARCHAR；⽽在保存较⼤⽂本时，通常会选择使⽤TEXT或者BLOB。⼆者之间的主要差别是BLOB能⽤来保 存⼆进制数据，⽐如照⽚；⽽TEXT只能保存字符数据，⽐如⼀篇⽂章或者⽇记。 TEXT和BLOB中又分别包括TEXT、MEDIUMTEXT、LONGTEXT和BLOB、MEDIUMBLOB、LONGBLOB三种不同的类型，它们之间的主要区别是存储⽂本长度 不同和存储字节不同，⽤户应该根据实际情况选择能够满⾜需求的最⼩存储类型。

（1）BLOB和TEXT值会引起⼀些性能问题，特别是在执⾏了⼤量的删除操作 时。
删除操作会在数据表中留下很⼤的“空洞”，以后填⼊这些“空洞”的记录在插⼊的 性能上会有影响。为了提⾼性能，建议定期使⽤OPTIMIZE TABLE功能对这类表进⾏ 碎⽚整理，避免因为“空洞”导致性能问题。
下⾯的例⼦描述了OPTIMIZE TABLE的碎⽚整理功能。

创建表和添加测试数据。

查看该表占用空间的大小。164M多

然后我们删除id为1 的数据后，查看该表的存储空间的大小。再次查看表的大小，占用的大小确实没有减少，

optimize table text_ceshi  对表进行优化操作后查看占用空间大小。发现表的数据文件缩小，“空洞”空间 被回收了。

（2）可以使⽤合成的（Synthetic）索引来提⾼⼤⽂本字段（BLOB或TEXT）的查 询性能。
简单来说，合成索引就是根据⼤⽂本字段的内容建⽴⼀个散列值，并把这个值存 储在单独的数据列中，接下来就可以通过检索散列值找到数据⾏了。但是，要注意这 种技术只能⽤于精确匹配的查询（散列值对于类似“<”或“>=”等范围搜索操作符是没有
⽤处的）。可以使⽤MD5()函数⽣成散列值，也可以使⽤SHA1()或CRC32()，或者使
⽤⾃⼰的应⽤程序逻辑来计算散列值。请记住数值型散列值可以很⾼效率地存储。同 样，如果散列算法⽣成的字符串带有尾部空格，就不要把它们存储在CHAR或 VARCHAR列中，它们会受到尾部空格去除的影响。合成的散列索引对于那些BLOB 或TEXT数据列特别有⽤。⽤散列标识符值查找的速度⽐搜索BLOB列本⾝的速度快很 多。

创建表和添加数据。

 create table t_blob (id varchar(100),context blob,hash_value varchar(40)); 
insert into t_blob values(1,repeat('beijing',2),md5(context));
 insert into t_blob values(2,repeat('beijing',2),md5(context)); 
insert into t_blob values(3,repeat('beijing 2008',2),md5(context)); 
 select * from t_blob

如果要查询 context值为“beijing 2008beijing 2008”的记录，则可以通过相应的散列 值来查询：

上面例子只能⽤于精确匹配，在⼀定程 度上减少了I/O，从⽽提⾼了查询效率。如果需要对BLOB或者CLOB字段进⾏模糊查 询，MySQL提供了前缀索引，也就是只为字段的前n列创建索引，举例如下：

 create index idx_blob on t_blob(context(100)); 

“%”不能放在最前⾯，否则索引将不会被使⽤。

（3）在不必要的时候避免检索⼤型的BLOB或TEXT值。
例如，SELECT       *查询就不是很好的想法，除⾮能够确定作为约束条件的WHERE
⼦句只会找到所需要的数据⾏。否则，很可能毫⽆⽬的地在⽹络上传输⼤量的值。这    也是 BLOB 或TEXT标识符信息存储在合成的索引列中对⽤户有所帮助的例⼦。⽤户 可以搜索索引列，决定需要的哪些数据⾏，然后从符合条件的数据⾏中检索BLOB或 TEXT值。
（4）把BLOB或TEXT列分离到单独的表中。 在某些环境中，如果把这些数据列移动到第⼆张数据表中，可以把原数据表中的
数据列转换为固定长度的数据⾏格式，那么它就是有意义的。这会减少主表中的碎
⽚，可以得到固定长度数据⾏的性能优势。它还可以使主数据表在运⾏ SELECT *查 询的时候不会通过⽹络传输⼤量的BLOB或TEXT值。

3 浮点数与定点数

浮点数⼀般⽤于表⽰含有⼩数部分的数值。当⼀个字段被定义为浮点类型后，如果插⼊数据的精度超过该列定义的实际精度，则插⼊值会被四舍五⼊到实际定义的精 度值，然后插⼊，四舍五⼊的过程不会报错。在MySQL中float、double（或real）⽤来 表⽰浮点数。

定点数不同于浮点数，定点数实际上是以字符串形式存放的，所以定点数可以更精确地保存数据。如果实际插⼊的数值精度⼤于实际定义的精度，则 MySQL 会进⾏ 警告（默认的SQLMode下），但是数据按照实际精度四舍五⼊后插⼊；如果SQLMode 是在TRADITIONAL （传统模式）下，则系统会直接报错，导致数据⽆法插⼊。在 MySQL中，decimal（或numberic）⽤来表⽰定点数。

 create table t_float (f float( 8,1)); 
insert into t_float values (1.23456); 
 select * from t_float;
insert into t_float values (1.25456); 
select * from t_float;

从上⾯的例⼦中，可以发现对于第⼀次插⼊值1.23456到float（8,1）时，该值被截 断，并保存为1.2，⽽第⼆次插⼊值1.25456到float（8,1）时，该值进⾏了四舍五⼊然 后被截断，并保存为1.3，所以在选择浮点型数据保存⼩数时，要注意四舍五⼊的问 题，并尽量保留⾜够的⼩数位，避免存储的数据不准确。

再看一个例子 如下：具体看下存储的值

CREATE TABLE test_float (c1 float(10,2),c2 decimal(10,2)); 
insert into test_float values(131072.32,131072.32); 
SELECT * from test_float;

从上⾯的例⼦中可以看到，c1列的值由131072.32变成了131072.31，这是上⾯的数 值在使⽤单精度浮点数表⽰时，产⽣了误差。这是浮点数特有的问题。因此在精度要 求⽐较⾼的应⽤中（⽐如货币）要使⽤定点数⽽不是浮点数来保存数据。

浮点数的⽐较也是⼀个普遍存在的问题，下⾯的程序⽚断中对两个浮点数 做减法运算：

public class Test {
public static void main(String[] args) throws Exception { System.out.print("7.22-7.0="  + (7.22f-7.0f));
}
}
对上⾯Java程序的输出结果可能会想当然地认为是0.22，但是，实际结果却是

7.22-7.0=0.21999979，因此，在编程中应尽量避免浮点数的⽐较，如果⾮要使⽤浮点
数的⽐较则最好使⽤范围⽐较⽽不要使⽤“==”⽐较。 

下⾯使⽤定点数来实现上⾯的例⼦：
import  java.math.BigDecimal;
/*
* 提供精确的减法运算。
*@param v1
*@param v2
*/
public class Test {
public static void main(String[] args) throws Exception { System.out.print("7.22-7.0=" + subtract(7.22,7.0));
}
public static double subtract(double v1, double v2) { BigDecimal b1 = new BigDecimal(Double.toString(v1)); BigDecimal b2 = new BigDecimal(Double.toString(v2)); return   b1.subtract(b2).doubleValue();
}
}
上⾯的实例使⽤Java的BigDecimal类实现了定点数的精确计算，所以7.22减7.0的 结果和预想的相同，为7.22-7.0=0.22。
注意：在今后关于浮点数和定点数的应⽤中，⽤户要考虑到以下⼏个原则： 浮点数存在误差问题； 对货币等对精度敏感的数据，应该⽤定点数表⽰或存储；
在编程中，如果⽤到浮点数，要特别注意误差问题，并尽量避免做浮点数⽐较； 要注意浮点数中⼀些特殊值的处理

4 ⽇期类型选择

MySQL提供的常⽤⽇期类型有DATE、TIME      、DATETIME、TIMESTAMP，根据实际需要选择能够满⾜应⽤的最⼩存储的⽇期类型。如果应⽤只需要记录“年份”，那么⽤1个字节来存储的YEAR类型完全可以满⾜，⽽不需要⽤4个字节来存储的DATE类型。这样不仅仅能节约存储，更能够提⾼表的操 作效率。

如果要记录年⽉⽇时分秒，并且记录的年份⽐较久远，那么最好使⽤ DATETIME，⽽不要使⽤ TIMESTAMP。因为TIMESTAMP表⽰的⽇期范围⽐DATETIME要短得多。

如果记录的⽇期需要让不同时区的⽤户使⽤，那么最好使⽤TIMESTAMP，因为⽇期类型中只有它能够和实际 时区相对应。