MySQL 数据类型
每一个常量，变量和参数都有数据类型，它用来指定一定的存储格式、约束和有效范围。 MySQL 供了多种数据类型，主要包括数值型、字符串类型、日期和时间类型。不同的 MySQL 版本支持的数据类型可能会稍有不同，用户可以通过查询相应版本的帮助文件来获得具体信 息。
数值类型
MySQL 支持所有标准 SQL 中的数值类型，其中包括严格数值类型（INTEGER、SMALLINT、 DECIMAL 和 NUMERIC），以及近似数值数据类型（FLOAT、REAL 和 DOUBLE PRECISION）， 并在此基础上做了扩展。扩展后增加了 TINYINT、MEDIUMINT 和 BIGINT 这 3 种长度不同的整 型，并增加了 BIT 类型，用来存放位数据。
整数类型
在整数类型中，按照取值范围和存储方式不同，分为 tinyint、smallint、mediumint、int、 bigint 这 5 个类型。如果超出类型范围的操作，会发生“Out of range”错误示。为了避免此 类问题发生，在选择数据类型时要根据应用的实际情况确定其取值范围，后根据确定的结果慎重选择数据类型。
整数类型还有一个属性：AUTO_INCREMENT。在需要产生唯一标识符或顺序值时， 可利用此属性，这个属性只用于整数类型。AUTO_INCREMENT 值一般从 1 开始，每行增加 1。 在插入 NULL 到一个 AUTO_INCREMENT 列时，MySQL 插入一个比该列中当前大值大 1 的 值。一个表中多只能有一个AUTO_INCREMENT列。对于任何想要使用AUTO_INCREMENT 的 列，应该定义为 NOT NULL，并定义为 PRIMARY KEY或定义为 UNIQUE 键。例如，可按下列 任何一种方式定义 AUTO_INCREMENT 列：
CREATE TABLE AI (ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY); CREATE TABLE AI(ID INT AUTO_INCREMENT NOT NULL ,PRIMARY KEY(ID)); CREATE TABLE AI (ID INT AUTO_INCREMENT NOT NULL ,UNIQUE(ID));
小数类型
对于小数的表示，MySQL 分为两种方式：浮点数和定点数。浮点数包括 float（单精度） 和 double（双精度）， 而定点数则只有 decimal 一种表示。定点数在 MySQL 内部以字符串形 式存放，比浮点数更精确，适合用来表示货币等精度高的数据。
浮点数和定点数都可以用类型名称后加“(M,D)”的方式来进行表示，“(M,D)”表示该 值一共显示M 位数字（整数位+小数位），其中 D 位位于小数点后面，M 和 D 又称为精度和 标度。例如，定义为 float(7,4)的一个列可以显示为-999.9999。MySQL 保存值时进行四舍五 入，因此如果在 float(7,4)列内插入 999.00009，近似结果是 999.0001。值得注意的是，浮点 数后面跟“(M,D)”的用法是非标准用法，如果要用于数据库的迁移，则好不要这么使用。 float 和 double 在不指定精度时，默认会按照实际的精度（由实际的硬件和操作系统决定） 来显示，而 decimal 在不指定精度时，默认的整数位为 10，默认的小数位为 0。
Float 、double 、decimal类型
1）创建测试表，分别将 id1、id2、id3 字段设置为 float(5,2)、double(5,2)、decimal(5,2)。
CREATE TABLE `t1` ( `id1` float(5,2) default NULL, `id2` double(5,2) default NULL, `id3` decimal(5,2) default NULL );
插入数据
insert into t1 values(1.23,1.23,1.23);
查询数据
select * from t1;
insert into t1 values(1.234,1.234,1.234);
此时发现，虽然数据都插入进去，但是系统出现了一个 warning，报告 id3 被截断。
将 id1、id2、id3 字段的精度和标度全部去掉，再次插入数据 1.23。
alter table t1 modify id1 float;
alter table t1 modify id2 double;
alter table t1 modify id3 decimal;
再次插入
insert into t1 values(1.234,1.234,1.234);
mysql> select * from t1;
+-------+-------+------+
| id1 | id2 | id3 |
+-------+-------+------+
| 1.234 | 1.234 | 1 |
+-------+-------+------+
1 row in set (0.00 sec)这个时候，可以发现 id1、id2字段中可以正常插入数据，而 id3 字段的小数位被截断。
上面这个例子验证了上面到的浮点数如果不写精度和标度，则会按照实际精度值显示，如 果有精度和标度，则会自动将四舍五入后的结果插入，系统不会报错；定点数如果不写精度 和标度，则按照默认值 decimal(10,0)来进行操作，并且如果数据超越了精度和标度值，系统则会报错。BIT类型
对于 BIT（位）类型，用于存放位字段值，BIT(M)可以用来存放多位二进制数，M 范围从 1～ 64，如果不写则默认为 1 位。对于位字段，直接使用 SELECT 命令将不会看到结果，可以用 bin()（显示为二进制格式）或者 hex()（显示为十六进制格式）函数进行读取。
create table t2(id bit(1));mysql> insert into t2 values(1);
Query OK, 1 row affected (1.76 sec)
mysql> select * from t2;
+------+
| id |
+------+
| |
+------+
1 row in set (0.00 sec)
使用bin函数和hex函数。结果可以正常显示为二进制数字和十六进制数字
mysql> select bin(id),hex(id) from t2;
+---------+---------+
| bin(id) | hex(id) |
+---------+---------+
| 1 | 1 |
+---------+---------+
1 row in set (0.00 sec)
插入数据的位数不能超过bit(1)，超出则会报错，举个例子如下：
mysql> insert into t2 values(2);
ERROR 1406 (22001): Data too long for column 'id' at row 1
需要进行修改表结构，在进行插入数据测试 一下。
mysql> alter table t2 modify id bit(2);
Query OK, 0 rows affected (0.38 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> insert into t2 values(2);
Query OK, 1 row affected (0.14 sec)
mysql> select bin(id),hex(id) from t2;
+---------+---------+
| bin(id) | hex(id) |
+---------+---------+
| 1 | 1 |
| 10 | 2 |
+---------+---------+
2 rows in set (0.00 sec)再次使用bin函数和hex函数即可查询到数了，通过结果来看hex函数获取的是数据值，bin返回一个字符串值，即作为参数传递的十进制数的二进制等值。如果数字为NULL，则返回NULL。
日期时间类型
MySQL 中有多种数据类型可以用于日期和时间的表示，不同的版本可能有所差异
date、time、datetime 三种日期类型
举个例子来说明：
mysql> create table t3(d date,t time,dt datetime);
Query OK, 0 rows affected (0.41 sec)
mysql> desc t3;
+-------+----------+------+-----+---------+-------+
| 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)用 now()函数插入当前日期：
mysql> insert into t3 values(now(),now(),now());
Query OK, 1 row affected, 1 warning (1.50 sec)
mysql> select * from t3;
+------------+----------+---------------------+
| d | t | dt |
+------------+----------+---------------------+
| 2019-03-23 | 11:45:14 | 2019-03-23 11:45:14 |
+------------+----------+---------------------+
1 row in set (0.00 sec)显而易见，DATETIME是DATE和TIME的组合，用户可以根据不同的需要，来选择不同的 日期或时间类型以满足不同的应用。
TIMESTAMP
TIMESTAMP日期类型也用来表示日期。
mysql> create table t4(id1 timestamp);
Query OK, 0 rows affected (1.98 sec)
mysql> desc t4;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
1 row in set (0.00 sec)可以发现，系统给TIMESTAMP 自动创建了默认值 CURRENT_TIMESTAMP（系统日期） 。插入一个 NULL 值试试
mysql> insert into t4 values(null);
Query OK, 1 row affected (0.39 sec)
mysql> select * from t4;
+---------------------+
| id1 |
+---------------------+
| 2019-03-23 11:48:38 |
+---------------------+
1 row in set (0.00 sec)果然，t中正确插入了系统日期。注意，MySQL只给表中的第一个TIMESTAMP字段设置 默认值为系统日期，如果有第二个TIMESTAMP类型，则默认值设置为0值，测试如下：
版本差异：
如果表中已经类型已经有timestamp再次添加没有默认值是会报错的。在5.0版本不会报错的，在5.7版本是会报错的。5.0版本会默认添加0。
mysql> alter table t4 add id2 timestamp; ERROR 1067 (42000): Invalid default value for 'id2'
TIMESTAMP的取值范围为19700101080001到2038年的某一天，因此它不适合存放比较 久远的日期，
TIMESTAMP的取值范围为19700101080001到2038年的某一天，因此它不适合存放比较 久远的日期，下面简单测试一些这个范围：
试一下下限值：
mysql> insert into t5 values (19700101080001);
Query OK, 1 row affected (0.13 sec)
mysql> select * from t5;
+---------------------+
| id1 |
+---------------------+
| 1970-01-01 08:00:01 |
+---------------------+
1 row in set (0.00 sec)
mysql> insert into t5 values (19700101080000);
ERROR 1292 (22007): Incorrect datetime value: '19700101080000' for column 'id1' at row 1再试一下上限值：
mysql> insert into t values('2038-01-19 11:14:07');
ERROR 1146 (42S02): Table 'test.t' doesn't exist
mysql> insert into t5 values('2038-01-19 11:14:07');
Query OK, 1 row affected (0.20 sec)
mysql> select * from t5;
+---------------------+
| id1 |
+---------------------+
| 2038-01-19 11:14:07 |
+---------------------+
2 rows in set (0.00 sec)
mysql> insert into t5 values('2038-01-19 11:14:08');
ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 11:14:08' for column 'id1' at row 1
TIMESTAMP和DATETIME的区别？
TIMESTAMP支持的时间范围较小，其取值范围从19700101080001到2038年的某个 时间，而DATETIME是从1000-01-01 00:00:00到9999-12-31 23:59:59，范围更大。
表中的第一个TIMESTAMP列自动设置为系统时间。如果在一个TIMESTAMP列中插入 NULL，则该列值将自动设置为当前的日期和时间。在插入或更新一行但不明确给 TIMESTAMP列赋值时也会自动设置该列的值为当前的日期和时间，当插入的值超出 取值范围时，MySQL认为该值溢出，使用“0000-00-00 00:00:00”进行填补5.0版本是这样，5.7版本会直接报错，所以建议设置默认值。
TIMESTAMP的插入和查询都受当地时区的影响，更能反应出实际的日期。而 DATETIME则只能反应出插入时当地的时区，其他时区的人查看数据必然会有误差 的。
TIMESTAMP的属性受MySQL版本和服务器SQLMode的影响很大。
YEAR类型代表
YEAR 类型主要用来表示年份，当应用只需要记录年份时，用 YEAR 比 DATE 将更节省空间。 下面的例子在表 t 中定义了一个 YEAR 类型字段，并插入一条记录：
mysql> create table t6(y year);
Query OK, 0 rows affected (0.42 sec)
mysql> desc t6;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| y | year(4) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into t6 values(2100);
Query OK, 1 row affected (0.20 sec)
mysql> select * from t6;
+------+
| y |
+------+
| 2100 |
+------+
1 row in set (0.00 sec)
mysql>
MySQL 以 YYYY 格式检索和显示 YEAR 值，范围是 1901～2155。当使用两位字符串表示 年份时，其范围为“00”到“99”。
说明如何采用不同的格式将日期“2007-9-3 12:10:10”插入到 DATETIME 列中
mysql> create table t7(dt datetime);
Query OK, 0 rows affected (0.67 sec)
mysql> insert into t7 values('2007-9-3 12:10:10');
Query OK, 1 row affected (0.13 sec)
mysql> insert into t7 values('2007/9/3 12+10+10');
Query OK, 1 row affected (0.14 sec)
mysql> insert into t7 values('20070903121010');
Query OK, 1 row affected (0.14 sec)
mysql> insert into t7 values(20070903121010);
Query OK, 1 row affected (0.13 sec)
mysql> select * from t7;
+---------------------+
| dt |
+---------------------+
| 2007-09-03 12:10:10 |
| 2007-09-03 12:10:10 |
| 2007-09-03 12:10:10 |
| 2007-09-03 12:10:10 |
+---------------------+
4 rows in set (0.00 sec)字符串类型
MySQL 中供了多种对字符数据的存储类型，不同的版本可能有所差异。以 5.0 版本为例， MySQL 包括了 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET 等多种 字符串类型。
CHAR和VARCHAR类型
举个例子说明
mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
Query OK, 0 rows affected (0.34 sec)
mysql> INSERT INTO vc VALUES ('ab ', 'ab ');
Query OK, 1 row affected (0.14 sec)
mysql> select length(v),length(c) from vc;
+-----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
| 4 | 2 |
+-----------+-----------+
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.01 sec)显然，CHAR 列后的空格在做操作时都已经被删除，而 VARCHAR 依然保留空格。
BINARY 和 VARBINARY 类型
BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串 而不包含非二进制字符串。在下面的例子中，对表 t 中的 binary 字段 c 插入一个字符， 研究一下这个字符到底是怎么样存储的。
mysql> CREATE TABLE t8 (c BINARY(3));
Query OK, 0 rows affected (0.34 sec)
mysql> INSERT INTO t8 SET c='a';
Query OK, 1 row affected (0.03 sec)
mysql> select *,hex(c),c='a',c='a\0',c='a\0\0' from t8;
+------+--------+-------+---------+-----------+
| c | hex(c) | c='a' | c='a\0' | c='a\0\0' |
+------+--------+-------+---------+-----------+
| a | 610000 | 0 | 0 | 1 |
+------+--------+-------+---------+-----------+
1 row in set (0.08 sec)可以发现，当保存 BINARY 值时，在值的后通过填充“0x00” （零字节）以达到指定的 字段定义长度。从上例中看出，对于一个 BINARY(3)列，当插入时'a'变为'a\0\0'。
ENUM 类型
ENUM中文名称叫枚举类型，它的值范围需要在创建表时通过枚举方式显式指定，对1～ 255 个成员的枚举需要 1 个字节存储；对于 255～65535 个成员，需要 2 个字节存储。多 允许有 65535 个成员。下面往测试表 t 中插入几条记录来看看 ENUM 的使用方法。
mysql> create table t9 (gender enum('M','F'));
Query OK, 0 rows affected (0.41 sec)
mysql> desc t9;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| gender | enum('M','F') | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> INSERT INTO t9 VALUES('M'),('1'),('f'),(NULL);
Query OK, 4 rows affected (0.48 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from t9;
+--------+
| gender |
+--------+
| M |
| M |
| F |
| NULL |
+--------+
4 rows in set (0.00 sec)从上面的例子中，可以看出 ENUM 类型是忽略大小写的，对'M'、'f'在存储的时候将它们都转 成了大写，还可以看出对于插入不在 ENUM 指定范围内的值时，并没有返回警告，而是插入了 enum('M','F')的第一值'M'，这点用户在使用时要特别注意。 另外，ENUM 类型只允许从值集合中选取单个值，而不能一次取多个值。
SET 类型
Set 和 ENUM 类型非常类似，也是一个字符串对象，里面可以包含 0～64 个成员。根据 成员的不同，存储上也有所不同。
Set 和 ENUM 除了存储之外，主要的区别在于 Set 类型一次可以选取多个成员，而 ENUM 则只能选一个。下面的例子在表 t 中插入了多组不同的成员
mysql> create table t10(col set('a','b','c','d'));
Query OK, 0 rows affected (0.27 sec)
mysql> desc t10;
+-------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| col | set('a','b','c','d') | YES | | NULL | |
+-------+----------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into t10 values('a,b'),('a,d,a'),('a,b'),('a,c'),('a');
Query OK, 5 rows affected (0.14 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from t10;
+------+
| col |
+------+
| a,b |
| a,d |
| a,b |
| a,c |
| a |
+------+
5 rows in set (0.00 sec)SET 类型可以从允许值集合中选择任意 1 个或多个元素进行组合，所以对于输入的值只要是在允许值的组合范围内，都可以正确地注入到 SET 类型的列中。对于超出允许值范围的值例 如（'a,d,f'）将不允许注入到上面例子中设置的 SET 类型列中，而对于（'a,d,a'）这样包含重 复成员的集合将只取一次，写入后的结果为“a,d”。这一点需要注意
小结
对每种数据类型的用途、物理存储、表示范围等有一个概要 的了解。这样在面对具体应用时，就可以根据相应的特点来选择合适的数据类型，使得我们 能够争取在满足应用的基础上，用较小的存储代价换来较高的数据库性能。
