MySQL支持的数据类型

本文将介绍MySQL所支持的数据类型，内容整理于书籍。
1、数值类型
TINYINT（1字节-2**8）、SMALLINT（2字节-2**16）、MEDIUMINT（3字节-2**24）、INT(INTEGER)（4字节-2**32）、BIGINT（8字节-2**64）

对于整型数据，MySQL还支持在类型名称后面的小括号内指定显示宽度，例如int（5）表示当数值宽度小于5位的时候在数字前面填满宽度。如果不显示指定宽度默则默认为int（11）。一般配合zerofill使用。顾名思义，zero就是用“0”填充的意思，也就是在数字位数不够的空间用字符“0”填满。

所有的整数类型都有一个可选属性UNSIGNED，如果需要在字段里面保存非负数或者需要较大的上限值时，可以用此选项，它的取值范围是正常值的下限取0，上限取原值的2倍。

对于小树表示，MySQL分为两种方式：浮点数和定点数。

• 浮点数包括单精度和双精度
• 而定点数只有decimal一种表示。定点数在MySQL内部以字符串形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。

浮点数如果不写精度和标度，则会按照实际精度值显示，如果有精度和标度，则会自动将四舍五入后的结果插入，系统不会报错；定点数如果不写精度和标度，则按照默认值decimal（10，0）来进行操作。并且如果数据超越了精度和标度值，系统则会报错。

对于BIT（位）类型，用于存放位字段值，BIT(M)可以用来存放多位二进制数，M范围从1~64，如果不写则默认为1位。对于位字段，直接使用SELECT命令将不会看到结果，可以用bin（）（显示二进制格式）或者hex（）（显示为十六进制格式）函数进行操作。

2、日期时间类型

• DATE（1000-01-01到9999-12-31）
• DATETIME（1000-01-01 00：00：00到9999-12-31 00：00：00）
• TIMESTAMP （19700101080001到2038年的某个时刻）
• TIME （-838：59：59到839：59：59）
• YEAR（1903-2155）

主要区别如下：

• 如果用来表示年月日，通常使用DATE来表示
• 如果用来表示年月日时分秒，通常用DATETIME来表示
• 如果只用来表示时分秒，通常用TIME来表示
• 如果需要经常插入或者更新日期为当前系统时间，则通常使用TIMESTAMP来表示。TIMESTAMP值返回后显示为“YYYY-MM-DD HH:MM:SS”格式的字符串，显示宽度固定为19个字符。如果想要获取数字值，应在TIMESTAMP列添加+0.
• 如果只表示年份可以用YEAR来表示，它比DATE占用更少的空间。YEAR有2位或4位格式的年。默认是4位格式。在4位允许的值是1901~2155和0000，在2位的格式中，允许的值是70~69，表示从1970到2069.MySQL以YYYY格式显示YEAR值。

mysql> create table t(d date,t time,dt datetime);
Query OK, 0 rows affected (0.04 sec)

mysql> desc t;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d     | date     | YES  |     | NULL    |       |
| t     | time     | YES  |     | NULL    |       |
| dt    | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into t values(now(),now(),now());


Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2013-08-10 | 21:48:57 | 2013-08-10 21:48:57 |
+------------+----------+---------------------+
1 row in set (0.00 sec)

mysql> create table t2(id1 timestamp,id2 timestamp);
Query OK, 0 rows affected (0.01 sec)

mysql> desc t2;
+-------+-----------+------+-----+---------------------+-----------------------------+
| Field | Type      | Null | Key | Default             | Extra                       |
+-------+-----------+------+-----+---------------------+-----------------------------+
| id1   | timestamp | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| id2   | timestamp | NO   |     | 0000-00-00 00:00:00 |                             |
+-------+-----------+------+-----+---------------------+-----------------------------+
2 rows in set (0.00 sec)

系统会为第一个timestamp自动创建默认值CURRENT_TIMESTAMP。
TIMESTAMP还有一个重要的特点，就是和时区相关。
当插入日期时，会先转换为本地时区后存放；而从数据库读出来，也同样需要将日期转换为本地时区后显示。这样两个不同时区的用户看到的同一个日期可能是一不一样的。

mysql>  create table t8(id1 timestamp,id2 datetime default NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> desc t8;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id1   | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| id2   | datetime  | YES  |     | NULL              |                             |
+-------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.00 sec)
mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | SYSTEM |
+---------------+--------+
1 row in set (0.00 sec)
mysql> insert into t8(id2) values (now());
Query OK, 1 row affected (0.01 sec)

mysql> select * from t8;
+---------------------+---------------------+
| id1                 | id2                 |
+---------------------+---------------------+
| 2013-08-10 22:00:39 | 2013-08-10 22:00:39 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone='+9:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t8;
+---------------------+---------------------+
| id1                 | id2                 |
+---------------------+---------------------+
| 2013-08-10 23:00:39 | 2013-08-10 22:00:39 |
+---------------------+---------------------+
1 row in set (0.00 sec)

TIMESTMAP和DATETIME的表示方法非常类似，区别主要以下几点：

• TIMESTAMP支持的时间范围较小，其取值范围从19700101080001到2038年某个时间，而DATETIME是从1000-01-01 00：00：00到9999012031 23：59：59范围更大。
• 表中的第一个TIMESTAMP列自动设置为系统时间。如果在一个TIMESTAMP列中插入NULL，则该列值将自动设置为当前的日期和时间。在插入或更新一列但不明确给TIMESTAMP列赋值时会自动设置该列的值的当前的日期和时间，当插入的值超出取值范围时，MySQL认为该值溢出，使用”0000-00-00 00：00：00“进行填补
• TIMESTAMP的插入和查询都受当地时区的影响，更能反映出实际的日期。而DATETIME则只能反映插入时当地的时区，其他时区的人查看数据必然是有误差的。
• TIMESTAMP的属性受MySQL版本和服务器版本SQLMode的影响很大。

3、字符串类型

• CHAR(0~255)
• VARCHAR(0~65535)
• TINYBLOB(0~255)
• BLOB(0~65535)
• MEDIUMBLOB(0~167772150)
• LONGBLOB(0~4294967295)
• TINYTEXT(0~255)
• TEXT(0~65535)
• MEDIUMTEXT(0~167772150)
• LONGTEXT(0~4294967295)
• VARBINARY(M)
• BINART(M)

CHAR和VARCHAR类型
CHAR和VARCHAR很类似，都用来保存MySQL中较短的字符串。二者的主要区别在于存储方式的不同:

• CHAR列的长度固定为创建表时声明的长度，长度可以为从0~255的任何值；
• 而VARCHAR列中的值为可变长字符串，长度可以制定为0~155或者65535之间的值。在检索的时候CHAR列删除了尾部的空格，而VARCHAR则保留这些空格。

BINARY和VARBANARY
BINARY和VARBANARY类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不包含非二进制字符串。
ENUM类型
枚举类型，它的值范围需要在创建表时候通过枚举方式显示制定，对1~255个成员的枚举需要1个字节存储；

mysql> create table t1(gender enum('M','F'));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values('M'),('1'),('f'),(NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+--------+
| gender |
+--------+
| M      |
| M      |
| F      |
| NULL   |
+--------+
4 rows in set (0.00 sec)

SET类型
SET类型和枚举类型类似，也是一个字符串对象，里面可以包含0~64个成员。
SET和ENUM除了存储之外，最主要的区别在于Set类型一次可以选取多个成员，而ENUM只能选一个。
选择合适的数据类型
CHAR（4）和VARCHAR(4)列检索的值并不总是相同，因为检索时从CHAR列删除尾部的空格。由于CHAR是固定长度的，所以它的处理速度比VARCHAR快得多，但是其缺点是浪费存储空间，程序需要对行尾空格进行处理，所以对于那些长度变化不大并且对查询速度有较高要求的数据可以考虑使用CHAR类型来存储。
在MySQL中，不同的存储引擎对CHAR和VARCHAR的使用原则有所不同，这里简单的概括下。

• MyISAM存储引擎：建议使用固定长度的数据列代替可变长度的数据列。
• MEMORY存储引擎：目前都是用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系。两者都是作为CHAR类型处理。
• InnoDB存储引擎：建议使用VARCHAR类型。对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列。因为本质上，使用固定长度的CHAR列不一定比使用可变长度VARCHAR列性能要好。因为，主要性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多余VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。

TEXT和BLOB
一般在保存少量字符串的时候，我们会选择使用CHAR或者VARCHAR；而在保存较大文本的时候，通常会选择使用TEXT或者BLOB，二者之间的主要差别是BLOB能用来保存二进制数据，比如照片。而TEXT只能保存字符数据。

• BLOB和TEXT值会引起一些性能问题，特别是在执行了大量的删除操作后
• 删除操作会在数据表中留下很大的“空洞”，以后填入这些“空洞“的记录在插入的性能上会有影响。为了提高性能，建议定期使用OPTIMIZE TABLE功能进行碎片整理，避免因为”空洞“导致性能问题。
• 可以使用合成的索引来提高大文本字段的查询性能
• 简单来说，合成索引就是根据大文本字段的内容建立一个散列值，并把这个值存储在单独的数据列中，解析来可以通过检索散列值找到数据行了。但是，这种技术只能用于精确匹配的查询（三列出对于类似<或者>=等范围操作符是没有用处的）。
• 在不必要的时候避免检索大型的BLOB或TEXT值
• 把BLOB或TEXT列分离到单独的表中

浮点数和定点数
浮点数一般用于表示含有小数部分的数值。当插入一个数据的精度超过了该列定义的实际精度免责插入值会被四舍五入到实际定义的精度值，然后插入。四舍五入的过程不会报错。
定点数不同于浮点数，定点数实际上是以字符串形式存放的，所以定点数可以更精确地保存数据。
在今后关于浮点数和定点数的应用中，用户要考虑到以下几个原则：

• 浮点数存在误差问题
• 对货币等对精度敏感的数据，应该用定点数表示和存储
• 在编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较
• 要注意浮点数中一些特殊值的处理

日期的类型选择
原则:

• 根据实际需要选择能够满足应用的最小存储的日期类型。
• 如如果要记录年月日时分秒，并且记录的年份比较久远，那么最好使用DATETIME，而不是TIMESTAMP
• 如果记录的日期需要让不同时区的用户使用，那么最好使用TIMESTAMP

参考资料：
以上内容来自深入浅出MySQL