每一个常量，变量和参数都有数据类型，它用来指定一定的存储格式、约束和有效范围。MySQL提供了多种数据类型，主要包括数值型、字符串类型、日期和时间类型。不同的MySQL版本支持的数据类型可能会稍有不同，用户可以通过查询相应版本的帮助文件来获得具体信息。本章将以MySQL 5.0为例，详细介绍MySQL中的各种数据类型。
3.1 数值类型
MySQL支持所有标准SQL中的数值类型，其中包括严格数值类型（INTEGER、SMALLINT、DECIMAL和NUMERIC），以及近似数值数据类型（FLOAT、REAL和DOUBLE PRECISION），并在此基础上做了扩展。扩展后增加了TINYINT、MEDIUMINT和BIGINT这3种长度不同的整型，并增加了BIT类型，用来存放位数据。表3-1中列出了MySQL 5.0中支持的所有数值类型，其中INT是INTEGER的同名词，DEC是DECIMAL的同名词。
表3-1 MySQL中的数值类型
整 数 类 型 | 字 节 | 最 小 值 | 最 大 值 |
TINYINT | 1 | 有符号-128 无符号 0 | 有符号 127 无符号 255 |
SMALLINT | 2 | 有符号-32768 无符号 0 | 有符号 32767 无符号 65535 |
MEDIUMINT | 3 | 有符号-8388608 无符号 0 | 有符号 8388607 无符号 1677215 |
INT、INTEGER | 4 | 有符号-2147483648 无符号 0 | 有符号 2147483647 无符号 4294967295 |
BIGINT | 8 | 有符号-9223372036854775808 无符号 0 | 有符号 9223372036854775807 无符号 18446744073709551615 |
浮点数类型 | 字 节 | 最 小 值 | 最 大 值 |
FLOAT | 4 | ±1.175494351E-38 | ±3.402823466E+38 |
DOUBLE | 8 | ±2.2250738585072014E-308 | ±1.7976931348623157E+308 |
续表
定点数类型 | 字 节 | 描 述 | |
DEC(M,D)， | M+2 | 最大取值范围与DOUBLE相同，给定DECIMAL的有效取值范围由M和D决定 | |
位 类 型 | 字 节 | 最 小 值 | 最 大 值 |
BIT(M) | 1～8 | BIT(1) | BIT(64) |
在整数类型中，按照取值范围和存储方式不同，分为tinyint、smallint、mediumint、int和bigint这5个类型。如果超出类型范围的操作，会发生“Out of range”错误提示。为了避免此类问题发生，在选择数据类型时要根据应用的实际情况确定其取值范围，最后根据确定的结果慎重选择数据类型。
对于整型数据，MySQL还支持在类型名称后面的小括号内指定显示宽度，例如int(5)表示当数值宽度小于5位的时候在数字前面填满宽度，如果不显示指定宽度则默认为int(11)。一般配合zerofill使用，顾名思义，zerofill就是用“0”填充的意思，也就是在数字位数不够的空间用字符“0”填满。以下几个例子分别描述了填充前后的区别。
（1）创建表t1，有id1和id2两个字段，指定其数值宽度分别为int和int(5)。
mysql> create table t1 (id1 int,id2 int(5));
Query OK, 0 rows affected (0.03 sec)
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id1 | int(11) | YES | | NULL | |
| id2 | int(5) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
（2）在id1和id2中都插入数值1，可以发现格式没有异常。
mysql> insert into t1 values(1,1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+------+
| id1 | id2 |
+------+------+
| 1 | 1 |
+------+------+
1 row in set (0.00 sec)
（3）分别修改id1和id2的字段类型，加入zerofill参数：
mysql> alter table t1 modify id1 int zerofill;
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> alter table t1 modify id2 int(5) zerofill;
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+------------+-------+
| id1 | id2 |
+------------+-------+
| 0000000001 | 00001 |
+------------+-------+
1 row in set (0.00 sec)
可以发现，在数值前面用字符“0”填充了剩余的宽度。大家可能会有所疑问，设置了宽度限制后，如果插入大于宽度限制的值，会不会截断或者插不进去报错？答案是肯定的：不会对插入的数据有任何影响，还是按照类型的实际精度进行保存，这时，宽度格式实际已经没有意义，左边不会再填充任何的“0”字符。下面在表t1的字段id1中插入数值1，id2中插入数值1111111，位数为7，大于id2的显示位数5，再观察一下测试结果：
mysql> insert into t1 values(1,1111111);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------------+---------+
| id1 | id2 |
+------------+---------+
| 0000000001 | 00001 |
| 0000000001 | 1111111 |
+------------+---------+
2 rows in set (0.00 sec)
很显然，如上面所说，id2中显示了正确的数值，并没有受宽度限制影响。
所有的整数类型都有一个可选属性UNSIGNED（无符号），如果需要在字段里面保存非负数或者需要较大的上限值时，可以用此选项，它的取值范围是正常值的下限取0，上限取原值的2倍，例如，tinyint有符号范围是-128～+127，而无符号范围是0～255。如果一个列指定为zerofill，则MySQL自动为该列添加UNSIGNED属性。
另外，整数类型还有一个属性：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
)
（2）往id1、id2和id3这3个字段中插入数据1.23。
mysql> insert into t1 values(1.23,1.23,1.23);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from t1;
+------+------+------+
| id1 | id2 | id3 |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
+------+------+------+
1 row in set (0.00 sec)
可以发现，数据都正确地插入了表t1。
（3）再向id1和id2字段中插入数据1.234，而id3字段中仍然插入1.23。
mysql> insert into t1 values(1.234,1.234,1.23);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+------+------+
| id1 | id2 | id3 |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
+------+------+------+
2 rows in set (0.00 sec)
可以发现，id1、id2、id3都插入了表t1，但是id1和id2由于标度的限制，舍去了最后一位，数据变为了1.23。
（4）同时向id1、id2、id3字段中都插入数据1.234。
mysql> insert into t1 values(1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------+
| Note | 1265 | Data truncated for column 'id3' at row 1 |
+-------+------+------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from t1;
+------+------+------+
| id1 | id2 | id3 |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
+------+------+------+
3 rows in set (0.00 sec)
此时发现，虽然数据都插入进去，但是系统出现了一个warning，报告id3被截断。如果是在传统的SQLMode（第16章将会详细介绍SQLMode）下，这条记录是无法插入的。
（5）将id1、id2、id3字段的精度和标度全部去掉，再次插入数据1.23。
mysql> alter table t1 modify id1 float;
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> alter table t1 modify id2 double;
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> alter table t1 modify id3 decimal;
Query OK, 3 rows affected, 3 warnings (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> desc t1;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id1 | float | YES | | NULL | |
| id2 | double | YES | | NULL | |
| id3 | decimal(10,0) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into t1 values(1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------+
| Note | 1265 | Data truncated for column 'id3' at row 1 |
+-------+------+------------------------------------------+
1 row in set (0.00 sec)
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(M)可以用来存放多位二进制数，M范围从1～64，如果不写则默认为1位。对于位字段，直接使用SELECT命令将不会看到结果，可以用bin()（显示为二进制格式）或者hex()（显示为十六进制格式）函数进行读取。
下面的例子中，对测试表t2中的bit类型字段id做insert和select操作，这里重点观察一下select的结果：
mysql> desc t2;
+-------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id | bit(1) | YES | | NULL | |
+-------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into t2 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t2;
+------+
| id |
+------+
| |
+------+
1 row in set (0.00 sec)
可以发现，直接select * 结果为NULL。改用bin()和hex()函数再试试：
mysql> select bin(id),hex(id) from t2;
+---------+---------+
| bin(id) | hex(id) |
+---------+---------+
| 1 | 1 |
+---------+---------+
1 row in set (0.00 sec)
结果可以正常显示为二进制数字和十六进制数字。
数据插入bit类型字段时，首先转换为二进制，如果位数允许，将成功插入；如果位数小于实际定义的位数，则插入失败，下面的例子中，在t2表插入数字2，因为它的二进制码是“10”，而id的定义是bit(1)，将无法进行插入：
mysql> insert into t2 values(2);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'id' at row 1 |
+---------+------+------------------------------------------------------+
1 row in set (0.01 sec)
将ID定义修改为bit(2)后，重新插入，插入成功：
mysql> alter table t2 modify id bit(2);
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into t2 values(2);
Query OK, 1 row affected (0.00 sec)
mysql> select bin(id),hex(id) from t2;
+---------+---------+
| bin(id) | hex(id) |
+---------+---------+
| 1 | 1 |
| 10 | 2 |
+---------+---------+
2 rows in set (0.00 sec)
3.2 日期时间类型
MySQL中有多种数据类型可以用于日期和时间的表示，不同的版本可能有所差异，表3-2中列出了MySQL 5.0中所支持的日期和时间类型。
表3-2 MySQL中的日期和时间类型
日期和时间类型 | 字 节 | 最 小 值 | 最 大 值 |
DATE | 4 | 1000-01-01 | 9999-12-31 |
DATETIME | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
TIMESTAMP | 4 | 19700101080001 | 2038年的某个时刻 |
TIME | 3 | -838:59:59 | 838:59:59 |
YEAR | 1 | 1901 | 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值(从5.5.27开始，2位格式的yeah已经不被支持)。
从表3-2中可以看出，每种日期时间类型都有一个有效值范围，如果超出这个范围，在默认的SQLMode下，系统会进行错误提示，并将以零值来进行存储。不同日期类型零值的表示如表3-3所示。
表3-3 MySQL中日期和时间类型的零值表示
数 据 类 型 | 零 值 表 示 |
DATETIME | 0000-00-00 00:00:00 |
DATE | 0000-00-00 |
TIMESTAMP | 00000000000000 |
TIME | 00:00:00 |
YEAR | 0000 |
DATE、TIME和DATETIME是最常使用的3种日期类型，以下例子在3种类型字段插入了相同的日期值，来看看它们的显示结果：
首先创建表t，字段分别为date、time、datetime三种日期类型：
mysql> create table t (d date,t time,dt datetime);
Query OK, 0 rows affected (0.01 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.01 sec)
用now()函数插入当前日期：
mysql> insert into t values(now(),now(),now());
Query OK, 1 row affected (0.00 sec)
查看显示结果：
mysql> select * from t;
+------------+----------+---------------------+
| d | t | dt |
+------------+----------+---------------------+
| 2007-07-19 | 17:41:13 | 2007-07-19 17:41:13 |
+------------+----------+---------------------+
1 row in set (0.00 sec)
显而易见，DATETIME是DATE和TIME的组合，用户可以根据不同的需要，来选择不同的日期或时间类型以满足不同的应用。
TIMESTAMP也用来表示日期，但是和DATETIME有所不同，后面的章节中会专门介绍。下例对TIMESTAMP类型的特性进行一些测试。
创建测试表t，字段id1为TIMESTAMP类型：
mysql> create table t (id1 timestamp);
Query OK, 0 rows affected (0.03 sec)
mysql> desc t;
+-------+-----------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-------+
| id2 | timestamp | YES | | CURRENT_TIMESTAMP | |
+-------+-----------+------+-----+-------------------+-------+
1 row in set (0.00 sec)
可以发现，系统给tm自动创建了默认值CURRENT_TIMESTAMP（系统日期）。插入一个NULL值试试：
mysql> insert into t values(null);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+---------------------+
| t |
+---------------------+
| 2007-07-04 16:37:24 |
+---------------------+
1 row in set (0.00 sec)
果然，t中正确插入了系统日期。注意，MySQL只给表中的第一个TIMESTAMP字段设置默认值为系统日期，如果有第二个TIMESTAMP类型，则默认值设置为0值，测试如下：
mysql> alter table t add id2 timestamp;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t \G;
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE 't' (
'id1' timestamp NOT NULL default CURRENT_TIMESTAMP,
'id2' timestamp NOT NULL default '0000-00-00 00:00:00'
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec))
当然，可以修改id2的默认值为其他常量日期，但是不能再修改为current_timestmap，因为MySQL规定TIMESTAMP类型字段只能有一列的默认值为current_timestmap，如果强制修改，系统会报如下错误提示：
mysql> alter table t modify id2 timestamp default current_timestamp;
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
TIMESTAMP还有一个重要特点，就是和时区相关。当插入日期时，会先转换为本地时区后存放；而从数据库里面取出时，也同样需要将日期转换为本地时区后显示。这样，两个不同时区的用户看到的同一个日期可能是不一样的，下面的例子演示了这个差别。
（1）创建表t8，包含字段id1（TIMESTAMP）和id2（DATETIME），设置id2的目的是为了和id1做对比：
CREATE TABLE 't8' (
'id1' timestamp NOT NULL default CURRENT_TIMESTAMP,
'id2' datetime default NULL
)
Query OK, 0 rows affected (0.03 sec)
（2）查看当前时区：
mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| time_zone | SYSTEM |
+---------------+--------+
1 row in set (0.00 sec)
可以发现，时区的值为“SYSTEM”，这个值默认是和主机的时区值一致的，因为我们在中国，这里的“SYSTEM”实际是东八区（+8:00）。
（3）用now()函数插入当前日期：
mysql> select * from t8;
+---------------------+---------------------+
| id1 | id2 |
+---------------------+---------------------+
| 2007-09-25 17:26:50 | 2007-09-25 17:26:50 |
+---------------------+---------------------+
1 row in set (0.01 sec)
结果显示id1和id2的值完全相同。
（4）修改时区为东九区，再次查看表中日期：
mysql> set time_zone='+9:00';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t8;
+---------------------+---------------------+
| id1 | id2 |
+---------------------+---------------------+
| 2007-09-25 18:26:50 | 2007-09-25 17:26:50 |
+---------------------+---------------------+
1 row in set (0.00 sec)
结果中可以发现，id1的值比id2的值快了1个小时，也就是说，东九区的人看到的“2007-09-25 18:26:50”是当地时区的实际日期，也就是东八区的“2007-09-25 17:26:50”，如果还是以“2007-09-25 17:26:50”理解时间必然导致误差。
TIMESTAMP的取值范围为19700101080001到2038年的某一天，因此它不适合存放比较久远的日期，下面简单测试一些这个范围：
mysql> insert into t values (19700101080001);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+---------------------+
| t |
+---------------------+
| 1970-01-01 08:00:01 |
+---------------------+
1 row in set (0.00 sec)
mysql> insert into t values (19700101080000);
Query OK, 1 row affected, 1 warning (0.00 sec)
其中19700101080000超出了tm的下限，系统出现警告提示。查询一下，发现插入值变成了0值。
mysql> select * from t;
+---------------------+
| t |
+---------------------+
| 1970-01-01 08:00:01 |
| 0000-00-00 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)
再来测试一下TIMESTAMP的上限值：
mysql> insert into t values('2038-01-19 11:14:07');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+---------------------+
| t |
+---------------------+
| 2038-01-19 11:14:07 |
+---------------------+
1 row in set (0.00 sec)
mysql> insert into t values('2038-01-19 11:14:08');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from t;
+---------------------+
| t |
+---------------------+
| 2038-01-19 11:14:07 |
| 0000-00-00 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)
从上面例子可以看出，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”进行填补。
TIMESTAMP的插入和查询都受当地时区的影响，更能反应出实际的日期。而DATETIME则只能反应出插入时当地的时区，其他时区的人查看数据必然会有误差的。
TIMESTAMP的属性受MySQL版本和服务器SQLMode的影响很大，本章都是以MySQL 5.0为例进行介绍，在不同的版本下可以参考相应的MySQL帮助文档。
YEAR类型主要用来表示年份，当应用只需要记录年份时，用YEAR比DATE将更节省空间。下面的例子在表t中定义了一个YEAR类型字段，并插入一条记录：
mysql> create table t(y year);
Query OK, 0 rows affected (0.01 sec)
mysql> desc t;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| y | year(4) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into t values(2100);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+------+
| y |
+------+
| 2100 |
+------+
1 row in set (0.00 sec)
MySQL以YYYY格式检索和显示YEAR值，范围是1901～2155。当使用两位字符串表示年份时，其范围为“00”到“99”。
“00”到“69”范围的值被转换为2000～2069范围的YEAR值。
“70”到“99”范围的值被转换为1970～1999范围的YEAR值。
细心的读者可能发现，在上面的例子中，日期类型的插入格式有很多，包括整数（如2100）、字符串（如2038-01-19 11:14:08）、函数（如NOW()）等，大家可能会感到疑惑，到底什么样的格式才能够正确地插入到对应的日期字段中呢？下面以DATETIME为例进行介绍。
YYYY-MM-DD HH:MM:SS或YY-MM-DD HH:MM:SS格式的字符串。允许“不严格”语法：任何标点符都可以用做日期部分或时间部分之间的间隔符。例如，“98-12-31 11:30:45”、“98.12.31 11+30+45”、“98/12/31 11*30*45”和“98@12@31 11^30^45”是等价的。对于包括日期部分间隔符的字符串值，如果日和月的值小于10，不需要指定两位数。“1979-6-9”与“1979-06-09”是相同的。同样，对于包括时间部分间隔符的字符串值，如果时、分和秒的值小于10，不需要指定两位数。“1979-10-30 1:2:3”与“1979-10-30 01:02:03”相同。
YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的没有间隔符的字符串，假定字符串对于日期类型是有意义的。例如，“19970523091528”和“970523091528”被解释为“1997-05-23 09:15:28”，但“971122129015”是不合法的（它有一个没有意义的分钟部分），将变为“0000-00-00 00:00:00”。
YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的数字，假定数字对于日期类型是有意义的。例如，19830905132800和830905132800被解释为“1983-09-05 13:28:00”。数字值应为6、8、12或者14位长。如果一个数值是8或14位长，则假定为YYYYMMDD或YYYYMMDDHHMMSS格式，前4位数表示年。如果数字是6或12位长，则假定为YYMMDD或YYMMDDHHMMSS格式，前2位数表示年。其他数字被解释为仿佛用零填充到了最近的长度。
函数返回的结果，其值适合DATETIME、DATE或者TIMESTAMP上下文，例如NOW()或CURRENT_DATE。
对于其他数据类型，其使用原则与上面的内容类似，限于篇幅，这里就不再赘述。
最后通过一个例子，说明如何采用不同的格式将日期“2007-9-3 12:10:10”插入到DATETIME列中。
mysql> create table t6(dt datetime);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t6 values('2007-9-3 12:10:10');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t6 values('2007/9/3 12+10+10');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t6 values('20070903121010');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t6 values(20070903121010);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t6;
+---------------------+
| 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)
3.3 字符串类型
MySQL中提供了多种对字符数据的存储类型，不同的版本可能有所差异。以5.0版本为例，MySQL包括了CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET等多种字符串类型。表3-4中详细列出了这些字符类型的比较。
表3-4 MySQL中的字符类型
字符串类型 | 字 节 | 描述及存储需求 |
CHAR（M） | M | M为0～255之间的整数 |
VARCHAR（M） | M为0～65535之间的整数，值的长度+1个字节 | |
TINYBLOB | 允许长度0～255 字节，值的长度+1个字节 | |
BLOB | 允许长度0～65535 字节，值的长度+2 个字节 | |
MEDIUMBLOB | 允许长度0～167772150字节，值的长度+3个字节 | |
LONGBLOB | 允许长度 0～4294967295字节，值的长度+4个字节 | |
TINYTEXT | 允许长度0～255 字节，值的长度+2个字节 | |
TEXT | 允许长度0～65535 字节，值的长度+2个字节 | |
MEDIUMTEXT | 允许长度0～167772150字节，值的长度+3个字节 | |
LONGTEXT | 允许长度 0～4294967295字节，值的长度+4个字节 | |
VARBINARY（M） | 允许长度0～M个字节的变长字节字符串，值的长度+1个字节 | |
BINARY（M） | M | 允许长度0～M个字节的定长字节字符串 |
下面将分别对这些字符串类型做详细的介绍。
3.3.1 CHAR和VARCHAR类型
CHAR和VARCHAR很类似，都用来保存MySQL中较短的字符串。二者的主要区别在于存储方式的不同：CHAR列的长度固定为创建表时声明的长度，长度可以为从0～255的任何值；而VARCHAR列中的值为可变长字符串，长度可以指定为0～255（5.0.3以前）或者65535（5.0.3以后）之间的值。在检索的时候，CHAR列删除了尾部的空格，而VARCHAR则保留这些空格。下面的例子中通过给表vc中的VARCHAR(4)和char(4)字段插入相同的字符串来描述这个区别。
（1）创建测试表vc，并定义两个字段v VARCHAR(4)和c CHAR(4)：
mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
Query OK, 0 rows affected (0.16 sec)
（2）v和c列中同时插入字符串“ab ”：
mysql> INSERT INTO vc VALUES ('ab ', 'ab ');
Query OK, 1 row affected (0.05 sec)
（3）显示查询结果：
mysql> select length(v),length(c) from vc;
+-----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
| 4 | 2 |
+-----------+-----------+
1 row in set (0.01 sec)
可以发现，c字段的length只有2。给两个字段分别追加一个“+”字符看得更清楚：
mysql> SELECT CONCAT(v, '+'), CONCAT(c, '+') FROM vc;
+----------------+----------------+
| CONCAT(v, '+') | CONCAT(c, '+') |
+----------------+----------------+
| ab + | ab+ |
+----------------+----------------+
1 row in set (0.00 sec)
显然，CHAR列最后的空格在做操作时都已经被删除，而VARCHAR依然保留空格。
3.3.2 BINARY和VARBINARY类型
BINARY和VARBINARY类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不包含非二进制字符串。在下面的例子中，对表t中的binary字段c插入一个字符，研究一下这个字符到底是怎么样存储的。
（1）创建测试表t，字段为c BINARY(3)：
mysql> CREATE TABLE t (c BINARY(3));
Query OK, 0 rows affected (0.14 sec)
（2）往c字段中插入字符“a”：
mysql> INSERT INTO t SET c='a';
Query OK, 1 row affected (0.06 sec)
（3）分别用以下几种模式来查看c列的内容：
mysql> select *,hex(c),c='a',c='a\0',c='a\0\0' from t;
+------+--------+-------+---------+-----------+
| c | hex(c) | c='a' | c='a\0' | c='a\0\0' |
+------+--------+-------+---------+-----------+
| a | 610000 | 0 | 0 | 1 |
+------+--------+-------+---------+-----------+
1 row in set (0.00 sec)
可以发现，当保存BINARY值时，在值的最后通过填充“0x00”（零字节）以达到指定的字段定义长度。从上例中看出，对于一个BINARY(3)列，当插入时'a'变为'a\0\0'。
3.3.3 ENUM类型
ENUM中文名称叫枚举类型，它的值范围需要在创建表时通过枚举方式显式指定，对1～255个成员的枚举需要1个字节存储；对于255～65535个成员，需要2个字节存储。最多允许有65535个成员。下面往测试表t中插入几条记录来看看ENUM的使用方法。
（1）创建测试表t，定义gender字段为枚举类型，成员为'M'和'F'：
mysql> create table t (gender enum('M','F'));
Query OK, 0 rows affected (0.14 sec)
（2）插入4条不同的记录：
mysql> INSERT INTO t VALUES('M'),('1'),('f'),(NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from t;
+--------+
| gender |
+--------+
| M |
| M |
| F |
| NULL |
+--------+
4 rows in set (0.01 sec)
从上面的例子中，可以看出ENUM类型是忽略大小写的，对'M'、'f '在存储的时候将它们都转成了大写，还可以看出对于插入不在ENUM指定范围内的值时，并没有返回警告，而是插入了enum('M','F')的第一值'M'，这点用户在使用时要特别注意。
另外，ENUM类型只允许从值集合中选取单个值，而不能一次取多个值。
3.3.4 SET类型
Set和ENUM类型非常类似，也是一个字符串对象，里面可以包含0～64个成员。根据成员的不同，存储上也有所不同。
1～8成员的集合，占1个字节。
9～16成员的集合，占2个字节。
17～24成员的集合，占3个字节。
25～32成员的集合，占4个字节。
33～64 成员的集合，占8个字节。
Set和ENUM除了存储之外，最主要的区别在于Set类型一次可以选取多个成员，而ENUM则只能选一个。下面的例子在表t中插入了多组不同的成员：
Create table t (col set （'a','b','c','d'）;
insert into t values('a,b'),('a,d,a'),('a,b'),('a,c'),('a');
mysql> select * from t;
+------+
| 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”，这一点请注意。
3.4 小结
本章主要介绍了MySQL支持的各种数据类型，并通过多个实例对它们的使用方法做了详细的说明。学完本章后，读者可以对每种数据类型的用途、物理存储、表示范围等有一个概要的了解。这样在面对具体应用时，就可以根据相应的特点来选择合适的数据类型，使得我们能够争取在满足应用的基础上，用较小的存储代价换来较高的数据库性能。
