关于MySQL数据类型的一些注意问题

用了3年的MySQL了，一直对这方面比较忽视，总是固定套着SQL模板写，很多细节方面没有注意，今天详细的看了一下MySQL方面的书籍，特意记下来，希望能够重视这些小问题。

1、整数类型

1）长度排序：TINYINT(1字节，-128~127) <  SMALLINT(2字节) < MEDIUMINT(3字节) < INT(4字节) <  BIGINT(8字节)。由此可见，int类型的长度还是很长的。

2）NUMERIC和DECIMAL类型 属于可指定小数位和整数位的类型，往往用于存储 货币或有精度要求的存储。如：NUMERIC（5,2），但是要注意的是，

无论NUMERIC还是DECIMAL，括号里的第一位表示的是有效数字长度，第二位表示的是小数点后的位数，

如：NUMERIC（5,2）的范围是  -999.99~999.99，而不是 -99999.99~99999.99， 上述的5 代表的是有效数字长度。

2、日期和时间

1）DATE 只记录日期，有年、月、日，但不记录时间，从DATE 转换为DATETIME或TIMESTAMP，得到的是 ‘YYYY-MM-DD 00:00:00’。

2）DATETIME除了范围比TIMESTAMP广以外，TIMESTAMP的主要优势在于：

UPDATE的时候，如果TIMESTAMP的新值和原来的值一样，MySQL会忽略这更改（提高效率）；

INSERT插入数据的时候，如果TIMESTAMP没赋值，其会自动默认有插入值插入当前时间！连续的TIMESTAMP只会默认插入第一个为当前时间。

3）Year 如果只插入或更新两位数，这两位数小于等于69，则对应的是2000~2069 年份，如果大于等于70，则对应的是1970~1999年份。

3、字符串

1）长度小于 4 的 VARCHAR 被改变为 CHAR。

2）如果在一个表中的任何列有可变长度，结果是整个行是变长的。

在这种情况下，如果一张表包含任何变长的列(VARCHAR、TEXT 或 BLOB)，所有大于 3 个字符的 CHAR列被改变为 VARCHAR列。

3）BLOB和 TEXT类型的唯一区别是，BLOB类型在排序时区分大小写，而TEXT不区分。

4）由于 BLOB 和 TEXT 值的大小变化很大，如果进行的删除和更新很多，则存储它们的表出现高碎片率会很高。应该定期地运行 OPTIMIZE TABLE减少碎片率以保持良好的性能。

4、ENUM和 SET 类型区别

1）ENUM 固定占两个字节，可以有 65535 个成员。SET 为可变长，最多占8个字节，最多可有64个成员。

2）ENUM 的值只能是成员中的一个，而SET的值可以为多个成员，甚至空字符串（“”），表示没有任何成员。

ENUM和SET都可存放NULL值，只要没有指定为NOT NULL的话。

如：color ENUM(―red,black,green,yellow)

property SET(―car,house,stock) NOT NULL

那么 color 和 property 可能的值分别为：
color：NULL、red、black、green和yellow
而 property 可能的值就复杂的多：
―
―car
―house
―car,house
―stock
―car,stock
―house,stock
―car,house,stock

造成这种原因是因为：

ENUM 的值存储方式是一个数值对应一个成员。这就意味着2个字节一共65536 个数值，就可以有65536 个成员。但是，0是ENUM 内置的错误成员，所以实际上最多只能有65535 个成员。

SET 是每一个二进制位对应一个成员，0表示这个成员存在，1表示不存在。这就是为什么SET可以出现多个成员同时出现的情况，儿空字符串则相当于：00000000。对于大小为 1 到
8、9 到 16、17 到 24、25 到 32、33 到 64 个成员的集合，其 SET 值分别占用 1、2、3、4 或 8 个字节。