5.1 MySQL数据类型介绍
MySQL支持多种数据类型，主要有数值类型、日期/时间类型和字符串类型。
● 数值数据类型：包括整数类型TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、浮点小数数据类型FLOAT和DOUBLE、定点小数类型DECIMAL。
● 日期/时间类型：包括YEAR、TIME、DATE、DATETIME和TIMESTAMP。
● 字符串类型：包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET等。
5.1.1 整数类型
数值型数据类型主要用来存储数字，MySQL提供了多种数值数据类型，不同的数据类型提供不同的取值范围，需要存储的值范围越大，其所需要的存储空间也会越大。MySQL主要提供的整数类型有TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）和BIGINT，如表5-1所示。整数类型的属性字段可以添加AUTO_INCREMENT自增约束条件。
表5-1 MySQL中的整数型数据类型
从表5-1中可以看到，不同类型的整数存储所需的字节数是不同的，占用字节数最少的是TINYINT类型，占用字节数最多的是BIGINT类型，相应的占用字节数越多的类型所能表示的数值范围越大。根据占用的字节数可以求出每一种数据类型的取值范围，例如TINYINT需要1个字节（8 bit）来存储，那么TINYINT无符号数的最大值为28-1，即255；TINYINT有符号数的最大值为27-1，即127。其他类型整数的取值范围的计算方法相同，如表5-2所示。
表5-2 不同整数类型的取值范围
在4.1.1节中，有如下创建表的语句：
CREATE TABLE tb_emp1
(
id INT(11),
name VARCHAR(25),
deptId INT(11),
salary FLOAT
);
id字段的数据类型为INT(11)，后面的数字11表示该数据类型显示宽度，指定能够显示的数值中数字的个数。例如，假设声明一个INT类型的字段：
year INT(4)
该声明指明，在year字段中的数据一般只显示4位数字的宽度。
在这里读者要注意：显示宽度和数据类型的取值范围无关。显示宽度只是指明MySQL最大可能显示的数字个数，数值的位数小于指定的宽度时会由空格填充；如果插入了大于显示宽度的值，只要该值不超过该类型整数的取值范围，数值依然可以插入，而且能够显示出来。例如，假如向year字段插入一个数值19999，当使用SELECT查询该列值的时候，MySQL显示的将是完整的5位数字19999，而不是4位数字的值。
其他整型数据类型也可以在定义表结构时指定所需要的显示宽度，如果不指定，则系统会为每一种类型指定默认的宽度值，如例5.1所示。
【例5.1】创建表tmp1，其中字段x、y、z、m、n的数据类型依次为TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，SQL语句如下：
CREATE TABLE tmp1 (x TINYINT, y SMALLINT, z MEDIUMINT, m INT, n BIGINT );
执行成功之后，用DESC查看表的结构，结果如下：
mysql> DESC tmp1; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | x | tinyint(4) | YES | | NULL | | | y | smallint(6) | YES | | NULL | | | z | mediumint(9) | YES | | NULL | | | m | int(11) | YES | | NULL | | | n | bigint(20) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
可以看到，系统将添加不同的默认显示宽度。这些显示宽度能够保证显示每一种数据类型可以取到取值范围内的所有值。例如，TINYINT有符号数和无符号数的取值范围分别为
-128～127和0～255，由于负号占了一个数字位，因此TINYINT默认的显示宽度为4。同理，其他整数类型的默认显示宽度与其有符号数的最小值的宽度相同。
不同的整数类型有不同的取值范围，并且需要不同的存储空间，因此，应该根据实际需要选择最合适的类型，这样有利于提高查询的效率和节省存储空间。整数类型是不带小数部分的数值，现实生活中很多地方都需要用到带小数的数值。下面介绍MySQL中支持的小数类型。
提示 显示宽度只用于显示，并不能限制取值范围和占用空间。例如，INT(3)会占用4个字节的存储空间，并且允许的最大值也不会是999，而是INT整型所允许的最大值。
5.1.2 浮点数类型和定点数类型
MySQL中使用浮点数和定点数来表示小数。浮点类型有两种：单精度浮点类型（FLOAT）和双精度浮点类型（DOUBLE）。定点类型只有一种：DECIMAL。浮点类型和定点类型都可以用（M，N）来表示，其中M称为精度，表示总共的位数；N称为标度，表示小数的位数。表5-3列出了MySQL中的小数类型和存储需求。
表5-3 MySQL中的小数类型
DECIMAL类型不同于FLOAT和DOUBLE，DECIMAL实际是以串存放的，DECIMAL可能的最大取值范围与DOUBLE一样，但是其有效的取值范围由M和D的值决定。如果改变M而固定D，则其取值范围将随M的变大而变大。从表5-3可以看到，DECIMAL的存储空间并不是固定的，而由其精度值M决定，占用M+2个字节。
FLOAT类型的取值范围如下：
有符号的取值范围：-3.402823466E+38～-1.175494351E-38。
无符号的取值范围：0和1.175494351E-38～3.402823466E+38。
DOUBLE类型的取值范围如下：
有符号的取值范围：-1.7976931348623157E+308～-2.2250738585072014E-308。
无符号的取值范围：0和2.2250738585072014E-308～1.7976931348623157E+308。
提示 不论是定点类型还是浮点类型，如果用户指定的精度超出精度范围，就会四舍五入进行处理。
【例5.2】创建表tmp2，其中字段x、y、z的数据类型依次为FLOAT(5,1)、DOUBLE(5,1)和DECIMAL(5,1)，向表中插入数据5.12、5.15和5.123，SQL语句如下：
CREATE TABLE tmp2 ( x FLOAT(5,1), y DOUBLE(5,1), z DECIMAL(5,1) );
向表中插入数据：
mysql>INSERT INTO tmp2 VALUES(5.12, 5.15, 5.123); Query OK, 1 row affected, 1 warning (0.00 sec)
可以看到在插入数据时，MySQL给出了一个警告信息，使用SHOW WARNINGS语句查看警告信息：
mysql> SHOW WARNINGS; +-------+------+-----------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------+ | Error | 1265 | Data truncated for column 'z' at row 1 | +-------+------+-----------------------------------------+ 1 row in set (0.00 sec)
可以看到FLOAT和DOUBLE在进行四舍五入时没有给出警告，而给出z字段数值被截断的警告。查看结果：
mysql> SELECT * FROM tmp2; +------+------+------+ | x | y | z | +------+------+------+ | 5.1 | 5.2 | 5.1 | +------+------+------+
FLOAT和DOUBLE在不指定精度时，默认会采用实际的精度（由计算机硬件和操作系统决定），DECIMAL如不指定精度则默认为（10,0）。
浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围；它的缺点是会引起精度丢失问题。
提示 在MySQL中，定点数以字符串的形式存储，在对精度要求比较高的时候（如货币、科学数据等）使用DECIMAL的类型比较好，另外两个浮点数进行减法和比较运算时也容易出问题，所以在使用浮点型时需要注意，并应尽量避免做浮点数比较。
5.1.3 日期与时间类型
MySQL中有多种表示日期的数据类型，主要有DATETIME、DATE、TIMESTAMP、TIME和YEAR。例如，当只记录年信息的时候，可以只使用YEAR类型，而没有必要使用DATE类型。每一个类型都有合法的取值范围，当指定不合法的值时系统会将“零”值插入数据库中。本节将介绍MySQL中日期和时间类型的使用方法。表5-4列出了MySQL中的日期与时间类型。
表5-4 日期与时间数据类型
1．YEAR
YEAR类型是一个单字节类型，用于表示年，在存储时只需要1个字节。可以使用各种格式指定YEAR值，如下所示：
（1）以4位字符串或者4位数字格式表示的YEAR，范围为‘1901’～‘2155’。输入格式为‘YYYY’或者YYYY，例如，输入‘2010’或2010，插入到数据库的值均为2010。
（2）以2位字符串格式表示的YEAR，范围为‘00’到‘99’。‘00’～‘69’和‘70’～‘99’范围的值分别被转换为2000～2069和1970～1999范围的YEAR值。‘0’与‘00’的作用相同。插入超过取值范围的值将被转换为2000。
（3）以2位数字表示的YEAR，范围为1～99。1～69和70～99范围的值分别被转换为2001～2069和1970～1999范围的YEAR值。注意：在这里0值将被转换为0000，而不是2000。
提示 两位整数范围与两位字符串范围稍有不同。例如，插入2000年，读者可能会使用数字格式的0表示YEAR，但实际上，这样插入数据库的值为0000，而不是所希望的2000。只有使用字符串格式的‘0’或‘00’，才能被正确解释为2000。非法YEAR值将被转换为0000。
【例5.3】创建数据表tmp3，定义数据类型为YEAR的字段y，向表中插入值2010，‘2010’，‘2166’，SQL语句如下：
首先创建表tmp3：
CREATE TABLE tmp3( y YEAR );
向表中插入数据：
mysql> INSERT INTO tmp3 values(2010),('2010');
再次向表中插入数据：
mysql> INSERT INTO tmp3 values ('2166');
ERROR 1264 (22003): Out of range value for column 'y' at row 1
语句执行之后，MySQL给出了一条错误提示，使用SHOW查看错误信息：
mysql> SHOW WARNINGS; +--------+------+----------------------------------------------+ | Level | Code | Message | +--------+------+----------------------------------------------+ | Error | 1264 | Out of range value for column 'y' at row 1; +--------+------+----------------------------------------------+ 1 row in set (0.00 sec)
可以看到，插入的第3个值2166超过了YEAR类型的取值范围，因此不能正常地执行插入操作，查看结果：
mysql> SELECT * FROM tmp3; +------+ | y | +------+ | 2010 | | 2010 | +------+
由结果可以看到，当插入值为数值类型的2010或者字符串类型的‘2010’时，都正确地储存到了数据库中；而当插入值为‘2166’时，由于超出了YEAR类型的取值范围，因此，不能插入值。
【例5.4】向tmp3表中的y字段插入字符串表示的YEAR值，分别为‘0’、‘00’、‘77’和‘10’，SQL语句如下：
首先删除表中的数据：
DELETE FROM tmp3;
向表中插入数据：
INSERT INTO tmp3 values('0'),('00'),('77'),('10');
查看结果：
mysql> SELECT * FROM tmp3; +------+ | y | +------+ | 2000 | | 2000 | | 1977 | | 2010 | +------+
由结果可以看到，字符串‘0’和‘00’的作用相同，都转换成了2000年；‘77’转换为1977；‘10’转换为2010。
【例5.5】向tmp3表中的y字段插入数字表示的YEAR值，分别为0、78和11，SQL语句如下：
首先删除表中的数据：
DELETE FROM tmp3;
向表中插入数据：
INSERT INTO tmp3 values(0),(78),(11);
查看结果：
mysql> SELECT * FROM tmp3; +------+ | y | +------+ | 0000 | | 1978 | | 2011 | +------+
由结果可以看到，0被转换为0000；78被转换为1978；11被转换为2011。
2．TIME
TIME类型用于表示时间信息，在存储时需要3个字节，格式为‘HH:MM:SS’，HH表示小时，MM表示分钟，SS表示秒。TIME类型的取值范围为-838:59:59～838:59:59，小时部分会如此大的原因是，TIME类型不仅可以用于表示一天的时间（必须小于24小时），还用于表示某个事件过去的时间或两个事件之间的时间间隔（可以大于24小时，或者甚至为负值）。可以使用各种格式指定TIME值，如下所示：
（1）‘D HH:MM:SS’格式的字符串。还可以使用“非严格”的语法：‘HH:MM:SS’、‘HH:MM’、‘D HH:MM’、‘D HH’或‘SS’。这里的D表示日，可以取0～34之间的值。在插入数据库时，D被转换为小时保存，格式为“D*24 + HH”。
（2）‘HHMMSS’格式的、没有间隔符的字符串或者HHMMSS格式的数值。例如，‘101112’被理解为‘10:11:12’，但‘109712’是不合法的（它的分钟部分没有意义），存储时将变为00:00:00。
提示 为TIME列分配简写值时应注意：如果没有冒号，MySQL解释值时，会假定最右边的两位表示秒。例如，读者可能认为‘1112’和1112表示11:12:00（即11点12分），但MySQL将它们解释为00:11:12（即11分12秒）。同样‘12’和12被解释为00:00:12。相反，TIME值中如果使用冒号，则肯定被看作当天的时间。也就是说，‘11:12’表示11:12:00，而不是00:11:12。
【例5．6】 创建数据表tmp4，定义数据类型为TIME的字段t，向表中插入值‘10:05:05’、‘23:23’、‘2 10:10’、‘3 02’、‘10’，SQL语句如下：
首先创建表tmp4：
CREATE TABLE tmp4( t TIME );
向表中插入数据：
mysql> INSERT INTO tmp4 values('10:05:05'), ('23:23'), ('2 10:10'), ('3 02'),('10');
查看结果：
mysql> SELECT * FROM tmp4; +----------+ | t | +----------+ | 10:05:05 | | 23:23:00 | | 58:10:00 | | 74:00:00 | | 00:00:10 | +----------+
由结果可以看到，‘10:05:05’被转换为10:05:05，‘23:23’被转换为23:23:00，‘2 10:10’被转换为58:10:00，‘3 02’被转换为74:00:00，‘10’被转换成00:00:10。
提示 在使用‘D HH’格式时，小时一定要使用双位数值，如果是小于10的小时数，应在前面加0。
【例5.7】向表tmp4中插入值‘101112’、111213、‘0’、107010，SQL语句如下：
首先删除表中的数据：
DELETE FROM tmp4;
向表中插入数据：
mysql>INSERT INTO tmp4 values('101112'),(111213),( '0');
再向表中插入数据：
mysql>INSERT INTO tmp4 values ( 107010); ERROR 1292 (22007): Incorrect time value: '107010' for column 't' at row 1
可以看到，在插入数据时，MySQL给出了一个错误提示信息，使用SHOW WARNINGS查看错误信息，如下所示：
mysql> SHOW WARNINGS; +---------+------+-------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------+ | Error | 1292 | Incorrect time value: '107010' for column 't' at row 1 +---------+------+-------------------------------------------------------+
可以看到，第二次在插入记录的时候，数据超出了范围，原因是107010的分钟部分超过了60，查看结果：
mysql> SELECT * FROM tmp4; +----------+ | t | +----------+ | 10:11:12 | | 11:12:13 | | 00:00:00 | +----------+
由结果可以看到，‘101112’被转换为10:11:12；111213被转换为11:12:13；‘0’被转换为00:00:00，107010因为是不合法的值，因此不能被插入。
也可以使用系统日期函数向TIME字段列插入值。
【例5.8】向tmp4表中插入系统当前时间，SQL语句如下：
首先删除表中的数据：
DELETE FROM tmp4;
向表中插入数据：
mysql> INSERT INTO tmp4 values (CURRENT_TIME) ,(NOW());
查看结果：
mysql> SELECT * FROM tmp4; +----------+ | t | +----------+ | 08:43:51 | | 08:43:51 | +----------+
由结果可以看到，系统当前的日期时间插入到TIME类型列，因为读者输入语句的时间不确定，因此获取的值与这里显示的可能不同，但都是系统当前的日期时间值。
3．DATE类型
DATE类型只用于输入日期值时，没有时间部分，在存储时需要3个字节。日期格式为‘YYYY-MM-DD’，其中YYYY表示年，MM表示月，DD表示日。在给DATE类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合DATE的日期格式即可，如下：
（1）以‘YYYY-MM-DD’或者‘YYYYMMDD’字符串格式表示的日期，取值范围为‘1000-01-01’～‘9999-12-3’。例如，输入‘2012-12-31’或者‘20121231’，插入数据库的日期都为2012-12-31。
（2）以‘YY-MM-DD’或者‘YYMMDD’字符串格式表示的日期，YY表示两位的年值。包含两位年值的日期容易让人混淆，因为不知道世纪。MySQL使用以下规则解释两位年值：00～69范围的年值转换为2000～2069；70～99范围的年值转换为1970～1999。例如，输入‘12-12-31’，插入数据库的日期为2012-12-31；输入‘981231’，插入数据库的日期为1998-12-31。
（3）以YY-MM-DD或者YYMMDD数字格式表示的日期，与前面相似，00～69范围的年值转换为2000～2069；70～99范围的年值转换为1970～1999。例如，输入12-12-31，插入数据库的日期为2012-12-31；输入981231，插入数据库的日期为1998-12-31。
（4）使用CURRENT_DATE或者NOW()，插入当前系统日期。
【例5.9】创建数据表tmp5，定义数据类型为DATE的字段d，向表中插入YYYY-MM-DD和YYYYMMDD字符串格式的日期，SQL语句如下：
首先创建表tmp5：
mysql> CREATE TABLE tmp5(d DATE); Query OK, 0 rows affected (0.02 sec)
向表中插入YYYY-MM-DD和YYYYMMDD格式的日期：
mysql> INSERT INTO tmp5 values('1998-08-08'),('19980808'),('20101010');
查看插入结果：
mysql> SELECT * FROM tmp5; +------------+ | d | +------------+ | 1998-08-08 | | 1998-08-08 | | 2010-10-10 | +------------+
可以看到，不同格式的日期值都正确地插入数据表中。
【例5.10】向tmp5表中插入YY-MM-DD和YYMMDD字符串格式的日期，SQL语句如下：
首先删除表中的数据：
DELETE FROM tmp5;
向表中插入YY-MM-DD和YYMMDD格式的日期：
mysql> INSERT INTO tmp5 values ('99-09-09'),('990909'), ('000101'), ('111111');
查看插入结果：
mysql> SELECT * FROM tmp5; +------------+ | d | +------------+ | 1999-09-09 | | 1999-09-09 | | 2000-01-01 | | 2011-11-11 | +------------+
【例5.11】向tmp5表中插入YY-MM-DD和YYMMDD数字格式的日期，SQL语句如下：
首先删除表中的数据：
DELETE FROM tmp5;
向表中插入YY-MM-DD和YYMMDD数字格式的日期：
mysql> INSERT INTO tmp5 values (99-09-09),(990909), (000101) ,(111111);
查看插入结果：
mysql> SELECT * FROM tmp5; +------------+ | d | +------------+ | 1999-09-09 | | 1999-09-09 | | 2000-01-01 | | 2011-11-11 | +------------+
【例5.12】向tmp5表中插入系统当前日期，SQL语句如下：
首先删除表中的数据：
DELETE FROM tmp5;
向表中插入系统当前日期：
mysql> INSERT INTO tmp5 values( CURRENT_DATE() ),( NOW() );
查看插入结果：
mysql> SELECT * FROM tmp5; +------------+ | d | +------------+ | 2013-03-21 | | 2013-03-21 | +------------+
CURRENT_DATE只返回当前日期值，不包括时间部分；NOW()函数返回日期和时间值，在保存到数据库时，只保留其日期部分。
提示 MySQL允许“不严格”语法：任何标点符号都可以用作日期部分之间的间隔符。例如，‘98-11-31’、‘98.11.31’、‘98/11/31’和‘98@11@31’是等价的，这些值也可以正确地插入数据库中。
4．DATETIME
DATETIME类型的字段可以同时输入日期和时间信息，在存储时需要8个字节。日期格式为‘YYYY-MM-DD HH:MM:SS’，其中YYYY表示年，MM表示月，DD表示日，HH表示小时，MM表示分钟，SS表示秒。在给DATETIME类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合DATETIME的日期格式即可，如下所示：
（1）以‘YYYY-MM-DD HH:MM:SS’或者‘YYYYMMDDHHMMSS’字符串格式表示的值，取值范围为‘1000-01-01 00:00:00’～‘9999-12-3 23:59:59’。例如，输入‘2012-12-31 05: 05: 05’或者‘20121231050505’，插入数据库的DATETIME值都为2012-12-31 05: 05: 05。
（2）以‘YY-MM-DD HH:MM:SS’或者‘YYMMDDHHMMSS’字符串格式表示的日期，在这里YY表示两位的年值。与前面相同，‘00～69’范围的年值转换为‘2000～2069’，‘70～99’范围的年值转换为‘1970～1999’。例如，输入‘12-12-31 05: 05: 05’，插入数据库的DATETIME为2012-12-31 05: 05: 05；输入‘980505050505’，插入数据库的DATETIME为1998-05-05 05: 05: 05。
（3）以YYYYMMDDHHMMSS或者YYMMDDHHMMSS数字格式表示的日期和时间。例如，输入20121231050505，插入数据库的DATETIME为2012-12-31 05:05:05；输入980505050505，插入数据的DATETIME为1998-12-31 05: 05: 05。
【例5.13】创建数据表tmp6，定义数据类型为DATETIME的字段dt，向表中插入YYYY-MM-DD HH:MM:SS和YYYYMMDDHHMMSS字符串格式的日期和时间值，SQL语句如下：
首先创建表tmp6：
CREATE TABLE tmp6( dt DATETIME );
向表中插入YYYY-MM-DD HH:MM:SS和YYYYMMDDHHMMSS格式的日期：
mysql> INSERT INTO tmp6 values('1998-08-08 08:08:08'),('19980808080808'), ('20101010101010');
查看插入结果：
mysql> SELECT * FROM tmp6; +----------------------+ | dt | +----------------------+ | 1998-08-08 08:08:08 | | 1998-08-08 08:08:08 | | 2010-10-10 10:10:10 | +----------------------+ 3 rows in set (0.00 sec)
可以看到，不同类型的日期值都正确地插入数据表中。
【例5.14】向tmp6表中插入YY-MM-DD HH:MM:SS和YYMMDDHHMMSS字符串格式的日期和时间值，SQL语句如下：
首先删除表中的数据：
DELETE FROM tmp6;
向表中插入YY-MM-DD HH:MM:SS和YYMMDDHHMMSS格式的日期：
mysql> INSERT INTO tmp6 values('99-09-09 09:09:09'),('990909090909'), ('101010101010');
查看插入结果：
mysql> SELECT * FROM tmp6; +---------------------+ | dt | +---------------------+ | 1999-09-09 09:09:09 | | 1999-09-09 09:09:09 | | 2010-10-10 10:10:10 | +---------------------+ 3 rows in set (0.00 sec)
【例5.15】向tmp6表中插入YYYYMMDDHHMMSS 和YYMMDDHHMMSS数字格式的日期和时间值，SQL语句如下：
首先删除表中的数据：
DELETE FROM tmp6;
向表中插入YYYYMMDDHHMMSS和YYMMDDHHMMSS数字格式的日期和时间：
mysql> INSERT INTO tmp6 values(19990909090909), (101010101010);
查看插入结果：
mysql> SELECT * FROM tmp6; +-------------------------+ | dt | +-------------------------+ | 1999-09-09 09:09:09 | | 2010-10-10 10:10:10 | +-------------------------+
【例5.16】向tmp6表中插入系统当前日期和时间值，SQL语句如下：
首先删除表中的数据：
DELETE FROM tmp6;
向表中插入系统当前日期：
mysql> INSERT INTO tmp6 values( NOW() );
查看插入结果：
mysql> SELECT * FROM tmp6; +-------------------------+ | dt | +-------------------------+ | 2013-03-21 20:28:06 | +-------------------------+
NOW()函数返回当前系统的日期和时间值，格式为YYYY-MM-DD HH:MM:SS。
提示 MySQL允许“不严格”语法：任何标点符号都可以用作日期部分或时间部分中的间隔符。例如，‘98-12-31 11:30:45’、‘98.12.31 11+30+45’、‘98/12/31 11*30*45’和‘98@12@31 11^30^45’是等价的，这些值都可以正确地插入数据库中。
5．TIMESTAMP
TIMESTAMP的显示格式与DATETIME相同，显示宽度固定为19个字符，日期格式为YYYY-MM-DD HH:MM:SS，在存储时需要4个字节。但是TIMESTAMP列的取值范围小于DATETIME的取值范围，为‘1970-01-01 00:00:01’UTC～‘2038-01-19 03:14:07’UTC。其中，UTC（Coordinated Universal Time）为世界标准时间，因此在插入数据时，要保证在合法的取值范围内。
【例5.17】创建数据表tmp7，定义数据类型为TIMESTAMP的字段ts，向表中插入值‘19950101010101’、‘950505050505’、‘1996-02-02 02:02:02’、‘97@03@03 03@03@03’、121212121212、NOW()，SQL语句如下：
CREATE TABLE tmp7( ts TIMESTAMP);
向表中插入数据：
INSERT INTO tmp7 values ('19950101010101'),
('950505050505'),
('1996-02-02 02:02:02'),
('97@03@03 03@03@03'),
(121212121212),
( NOW() );
查看插入结果：
mysql>SELECT * FROM tmp7; +---------------------+ | ts | +---------------------+ | 1995-01-01 01:01:01 | | 1995-05-05 05:05:05 | | 1996-02-02 02:02:02 | | 1997-03-03 03:03:03 | | 2012-12-12 12:12:12 | | 2013-03-24 09:17:49 | +---------------------+
由结果可以看到，‘19950101010101’被转换为1995-01-01 01:01:01；‘950505050505’被转换为1995-05-05 05:05:05；‘1996-02-02 02:02:02’被转换为1996-02-02 02:02:02；‘97@03@03 03@03@03’被转换为1997-03-03 03:03:03；121212121212被转换为2012-12-12 12:12:12；NOW()被转换为系统当前日期时间2013-03-24 09:17:49。
提示 TIMESTAMP与DATETIME除了存储字节和支持的范围不同外，还有一个最大的区别就是：DATETIME在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；而TIMESTAMP值的存储是以UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。即查询时，根据当前时区的不同，显示的时间值是不同的。
【例5.18】向tmp7表中插入当前日期，查看插入值；更改时区为东10区，再次查看插入值，SQL语句如下：
首先删除表中的数据：
DELETE FROM tmp7;
向表中插入系统当前日期：
mysql> INSERT INTO tmp7 values( NOW() );
查看当前时区下的日期值：
mysql> SELECT * FROM tmp7; +---------------------+ | ts | +---------------------+ | 2013-03-24 10:02:42 | +---------------------+
查询结果为插入时的日期值，如果读者所在时区为东8区，下面修改当前时区为东10区，SQL语句如下：
mysql> set time_zone='+10:00';
再次查看插入时的日期值：
mysql> SELECT * FROM tmp7; +---------------------+ | ts | +---------------------+ | 2013-03-24 12:02:42 | +---------------------+
由结果可以看到，因为东10区的时间比东8区快2个小时，因此查询的结果经过时区转换之后，显示的值增加了2个小时。相同的，如果时区每减小一个值，则查询显示的日期中的小时数减1。
提示 如果为DATETIME或TIMESTAMP对象分配一个DATE值，则结果值的时间部分被设置为‘00:00:00’，因为DATE值不包含时间信息。如果为DATE对象分配一个DATETIME或TIMESTAMP值，则结果值的时间部分被删除，因为DATE值不包含时间信息。
5.1.4 字符串类型
字符串类型的字段除了可以存储字符串数据之外，还可以存储其他数据，比如图片和声音的二进制数据。字符串可以进行区分或者不区分大小写的串比较，以及模式匹配查找。MySQL中的字符串类型有CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。表5-5列出了MySQL中的字符串数据类型。
VARCHAR、BLOB和TEXT类型是变长类型，对于其存储需求取决于列值的实际长度（在表5-5中用L表示），而不是取决于类型的最大可能尺寸。例如，一个VARCHAR(10)列能保存最大长度为10个字符的一个字符串，实际的存储需要是字符串的长度L，加上1个字节以记录字符串的长度。对于字符abcd，L是4而存储要求是5个字节。本节将介绍这些数据类型的作用以及如何在查询中使用这些类型。
表5-5 MySQL中的字符串数据类型
1．CHAR和VARCHAR类型
CHAR(M) 为固定长度字符串，在定义时要指定字符串列长。当保存时在右侧填充空格以达到指定的长度。M表示列长度，M的范围是0～255个字符。例如，CHAR(4)定义了一个固定长度的字符串列，其包含的字符个数最大为4。当检索到CHAR值时，尾部的空格将被删除。
VARCHAR(M) 是长度可变的字符串，M表示最大列长度。M的范围是0～65535。VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定，而其实际占用的空间为字符串的实际长度加1。例如，VARCHAR(50)定义了一个最大长度为50的字符串，如果插入的字符串只有10个字符，则实际存储的字符串为10个字符和一个字符串结束字符。在保存和检索VARCHAR的值时尾部的空格仍保留。
【例5.19】下面将字符串分别保存到CHAR(4)和VARCHAR(4)列，以说明CHAR和VARCHAR之间的差别，如表5-6所示。
表5-6 CHAR(4)与VARCHAR(4)的存储区别
从对比结果可以看到，CHAR(4) 定义了固定长度为4的列，不管存入的数据长度为多少，所占用的空间均为4个字节。VARCHAR(4) 定义的列所占的字节数为实际长度加1。
当查询时，CHAR(4) 和VARCHAR(4) 的值并不一定相同，如例5.20所示。
【例5.20】创建tmp8表，定义字段ch和vch的数据类型分别为CHAR(4)和VARCHAR(4)，向表中插入数据“ab ”，SQL语句如下：
创建表tmp8：
CREATE TABLE tmp8(
ch CHAR(4), vch VARCHAR(4)
);
输入数据：
INSERT INTO tmp8 VALUES('ab ', 'ab ');
查询结果：
mysql> SELECT concat('(', ch, ')'), concat('(',vch,')') FROM tmp8;
+----------------------+---------------------+
| concat('(', ch, ')') | concat('(',vch,')') |
+----------------------+---------------------+
| (ab) | (ab ) |
+----------------------+---------------------+
1 row in set (0.00 sec)
从查询结果可以看到，ch在保存“ab ”时将末尾的两个空格删除了，而vch字段保留了末尾的两个空格。
提示 在表5-6中，最后一行的值只有在使用“不严格”模式时，字符串才会被截断插入；如果MySQL使用“严格”模式，则超过列长度的值不会被保存，并且会出现错误信息“ERROR 1406(22001): Data too long for column”，即字符串长度超过指定长度，无法插入。
2．TEXT类型
TEXT列保存非二进制字符串，如文章内容、评论等。当保存或查询TEXT列的值时，不删除尾部空格。Text类型分为4种：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。不同的TEXT类型的存储空间和数据长度也不同。
● TINYTEXT：最大长度为255(28–1)个字符的TEXT列。
● TEXT：最大长度为65535(216–1)个字符的TEXT列。
● MEDIUMTEXT：最大长度为16777215(224–1)个字符的TEXT列。
● LONGTEXT：最大长度为4294967295或4GB(232–1)个字符的TEXT列。
3．ENUM类型
ENUM是一个字符串对象，其值为创建表时在列规定中枚举的一列值。语法格式如下：
字段名 ENUM('值1','值2',... '值n')
字段名指将要定义的字段，值n指枚举列表中的第n个值。ENUM类型的字段在取值时，只能在指定的枚举列表中取，而且一次只能取一个。如果创建的成员中有空格，则其尾部的空格将自动被删除。ENUM值在内部用整数表示，每个枚举值均有一个索引值：列表值所允许的成员值从1开始编号，MySQL存储的就是这个索引编号。枚举最多可以有65535个元素。
例如，定义ENUM类型的列（NULL, ", first, second, third），该列可以取的值和每个值的索引如表5-7所示。
表5-7 ENUM类型的取值范围
ENUM值依照列索引顺序排列，并且空字符串排在非空字符串之前，NULL值排在其他所有的枚举值之前。这一点也可以从表5-7中看到。
在这里，有一个方法可以查看列成员的索引值，如例5.21所示。
【例5.21】创建表tmp9，定义ENUM类型的列enm('first', 'second', 'third')，查看列成员的索引值，SQL语句如下：
首先，创建tmp9表：
CREATE TABLE tmp9( enm ENUM('first','second','third') );
插入各个列值：
INSERT INTO tmp9 values('first'),('second') ,('third') , (NULL);
查看索引值：
mysql> SELECT enm, enm+0 FROM tmp9; +---------+--------+ | enm | enm+0 | +---------+--------+ | first | 1 | | second | 2 | | third | 3 | | NULL | NULL | +---------+--------+
可以看到，这里的索引值和前面所述的相同。
提示 ENUM列总有一个默认值。如果将ENUM列声明为NULL，NULL值则为该列的一个有效值，并且默认值为NULL。如果ENUM列被声明为NOT NULL，其默认值为允许的值列表的第1个元素。
【例5.22】创建表tmp10，定义INT类型的字段soc，ENUM类型的字段level，其值为（‘excellent’,‘good’,‘bad’），向表tmp10中插入数据‘good’、1、2、3、‘best’，SQL语句如下：
首先，创建数据表：
CREATE TABLE tmp10 (soc INT, level enum('excellent', 'good','bad') );
插入数据：
mysql>INSERT INTO tmp10 values(70,'good'), (90,1),(75,2),(50,3);
再次插入数据：
mysql>INSERT INTO tmp10 values (100,'best'); ERROR 1265 (01000): Data truncated for column 'level' at row 1
这里系统提示错误信息，可以看到，由于字符串值‘best’不在ENUM列表中，所以对数据进行了阻止插入操作，查询结果如下：
mysql> SELECT * FROM tmp10; +------+-----------+ | soc | level | +------+-----------+ | 70 | good | | 90 | excellent | | 75 | good | | 50 | bad | +------+-----------+
由结果可以看到，因为ENUM类型列的值在MySQL中都是以编号序列存储的，因此，插入列表中的值good或者插入其对应序号2的结果是相同的；best不是列表中的值，因此不能插入数据。
4．SET类型
SET是一个字符串对象，可以有零个或多个值，SET列最多可以有64个成员，其值为创建表时规定的一列值。SET的各值之间用逗号（,）分隔。语法格式如下：
SET('值1','值2',… '值n')
与ENUM类型相同，SET的值在内部用整数表示，每一个值都有一个索引编号。当创建表时，SET成员值的尾部空格将自动被删除。但与ENUM类型不同的是，ENUM类型的字段只能从定义的列值中选择一个值插入，而SET类型的列可以从定义的列值中选择多个字符联合插入。
如果插入SET字段中的列值有重复，则MySQL自动删除重复的值；插入SET字段的值的顺序并不重要，MySQL会在存入数据库时，按照定义的顺序显示；如果插入了不正确的值，默认情况下，MySQL将忽视这些值，并给出警告。
【例5.23】创建表tmp11，定义SET类型的字段s，取值列表为（‘a’,‘b’,‘c’,‘d’），插入数据（‘a’）、（‘a,b,a’）、（‘c,a,d’）、（‘a,x,b,y’）。
首先创建表tmp11，SQL语句如下：
CREATE TABLE tmp11 ( s SET('a', 'b', 'c', 'd'));
插入数据：
INSERT INTO tmp11 values('a'),( 'a,b,a'),('c,a,d');
再次插入数据：
INSERT INTO tmp11 values ('a,x,b,y');
ERROR 1265 (01000): Data truncated for column 's' at row 1
由于插入了SET列不支持的值，因此MySQL给出错误提示。
查看结果：
mysql> SELECT * FROM tmp11; +-------+ | s | +-------+ | a | | a,b | | a,c,d | +-------+
从结果可以看到，对于SET来说，如果插入的值为重复的，则只取一个。例如，插入“a,b,a”，则结果为“a,b”。如果插入了不按顺序排列的值，则自动按顺序插入。例如，插入“c,a,d”，结果为“a,c,d”。如果插入了不正确值，该值将被阻止插入，例如，插入值“a,x,b,y”，会给出错误信息。
5.1.5 二进制类型
MySQL支持两类字符型数据：文本字符串和二进制字符串。前面讲解了存储文本的字符串类型，这一节将讲解MySQL中存储二进制数据的数据类型。MySQL中的二进制数据类型有BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB，本节将讲解这些二进制类型的特点和使用方法。表5-8列出了MySQL中的二进制数据类型。
表5-8 MySQL中的二进制数据类型
1．BIT类型
BIT是位字段类型。M表示值的位数，范围为1～64。如果M被省略，默认为1。如果为BIT(M)列分配的值的长度小于M位，在值的左边用0填充。例如，为BIT(6)列分配一个值b‘101’，其效果与分配b‘000101’相同。BIT数据类型用来保存位字段值，例如，以二进制的形式保存数据13，13的二进制形式为1101，在这里需要位数至少为4位的BIT类型，即可以定义列类型为BIT(4)。大于二进制1111的数据是不能插入BIT(4)类型的字段中的。
【例5.24】创建表tmp12，定义BIT(4)类型的字段b，向表中插入数据2、9、15。
首先创建表tmp12，SQL语句如下：
CREATE TABLE tmp12( b BIT(4) );
插入数据：
mysql> INSERT INTO tmp12 VALUES(2), (9), (15); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings:1
查询插入结果：
mysql> SELECT BIN(b+0) FROM tmp12; +----------+ | BIN(b+0) | +----------+ | 10 | | 1001 | | 1111 | +----------+ 4rows in set (0.00 sec)
b+0表示将二进制的结果转换为对应的数字的值，BIN() 函数将数字转换为二进制形式。从结果可以看到，成功地将3个数插入到表中。
提示 默认情况下，MySQL不可以插入超出列允许范围的值，因而插入数据时要确保插入的值在指定的范围内。
2．BINARY和VARBINARY类型
BINARY和VARBINARY类型类似于CHAR和VARCHAR，不同的是它们包含二进制字节字符串。其使用的语法格式如下：
列名称 BINARY(M)或者VARBINARY(M)
BINARY类型的长度是固定的，指定长度之后，不足最大长度的，将在右边填充“\0”补齐以达到指定长度。例如，指定列数据类型为BINARY(3)，当插入“a”时，存储的内容实际为“a\0\0”，当插入“ab”时，实际存储的内容为“ab\0”，不管存储的内容是否达到指定的长度，其存储空间均为指定的值M。
VARBINARY类型的长度是可变的，其长度可以在0到最大值之间。例如，指定列数据类型为VARBINARY(20)，如果插入的值的长度只有10，则实际存储空间为10加1，即其实际占用的空间为字符串的实际长度加1。
【例5.25】创建表tmp13，定义BINARY(3)类型的字段b和VARBINARY(3)类型的字段vb，并向表中插入数据“5”，比较两个字段的存储空间。
首先创建表tmp13，输入SQL语句如下：
CREATE TABLE tmp13( b binary(3), vb varbinary(30) );
插入数据：
INSERT INTO tmp13 VALUES(5,5);
查看两个字段存储数据的长度：
mysql> SELECT length(b), length(vb) FROM tmp13; +-----------+---------------+ | length(b) | length(vb) | +-----------+---------------+ | 3 | 1 | +-----------+---------------+ 1 row in set (0.00 sec)
可以看到，b字段的数据长度为3，而vb字段的数据长度仅为插入的一个字符的长度1。
如果想要进一步确认“5”在两个字段中不同的存储方式，输入如下语句：
mysql> SELECT b,vb,b = '5', b='5\0\0',vb='5',vb = '5\0\0' FROM tmp13; +------+------+---------+-----------+--------+--------------+ | b | vb | b = '5' | b='5\0\0' | vb='5' | vb = '5\0\0' | +------+------+---------+-----------+--------+--------------+ | 5 | 5 | 0 | 1 | 1 | 0 | +------+------+---------+-----------+--------+--------------+ 1 row in set (0.00 sec)
由执行结果可以看出，b字段和vb字段的长度是截然不同的，因为b字段不足的空间填充了“\0”，而vb字段则没有填充。
3．BLOB类型
BLOB是一个二进制大对象，用来存储可变数量的数据。BLOB类型分为4种：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB，它们可容纳值的最大长度不同，如表5-9所示。
表5-9 BLOB类型的存储范围
BLOB列存储的是二进制字符串（字节字符串）；TEXT列存储的是非二进制字符串（字符字符串）。BLOB列没有字符集，并且排序和比较基于列值字节的数值；TEXT列有一个字符集，并且根据字符集对值进行排序和比较。