5.1　MySQL数据类型介绍

MySQL支持多种数据类型，主要有数值类型、日期/时间类型和字符串类型。

（1）数值类型：包括整数类型TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、浮点小数数据类型FLOAT和DOUBLE，定点小数类型DECIMAL。

（2）日期/时间类型：包括YEAR、TIME、DATE、DATETIME和TIMESTAMP。

（3）字符串类型：包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET等。字符串类型又分为文本字符串和二进制字符串。

5.1.1　整数类型

数值型数据类型主要用来存储数字，MySQL提供了多种数值数据类型，不同的数据类型提供不同的取值范围，可以存储的值范围越大，其所需要的存储空间也会越大。MySQL主要提供的整数类型有TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）、BIGINT。整数类型的属性字段可以添加AUTO_INCREMENT自增约束条件。表5.1列出了MySQL中的数值类型。

表5.1　MySQL中的整数型数据类型

从表5.1中可以看到，不同类型整数存储所需的字节数是不同的，占用字节数最小的是TINYINT类型，占用字节最大的是BIGINT类型，相应的占用字节越多的类型所能表示的数值范围越大。根据占用字节数可以求出每一种数据类型的取值范围。例如，TINYINT需要1字节（8 bits）来存储，那么TINYINT无符号数的最大值为2⁸-1（255）、TINYINT有符号数的最大值为2⁷-1（127）。其他类型的整数的取值范围计算方法相同，如表5.2所示。

表5.2　不同整数类型的取值范围

在第4章中，有如下创建表的语句：

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查看表结构，结果如下：

可以看到，系统将添加不同的默认显示宽度。这些显示宽度能够保证显示每一种数据类型可以取到取值范围内的所有值。例如，TINYINT有符号数和无符号数的取值范围分别为-128~127和0~255，由于负号占了一个数字位，因此TINYINT默认的显示宽度为4。同理，其他整数类型的默认显示宽度与其有符号数的最小值的宽度相同。

不同的整数类型有不同的取值范围，并且需要不同的存储空间，因此应该根据实际需要选择最合适的类型，这样有利于提高查询的效率和节省存储空间。整数类型是不带小数部分的数值，现实生活中很多地方需要用到带小数的数值，下面将介绍MySQL中支持的小数类型。

5.1.2　浮点数类型和定点数类型

MySQL中使用浮点数和定点数来表示小数。浮点数类型有两种：单精度浮点类型（FLOAT）和双精度浮点类型（DOUBLE）。定点数类型只有一种：DECIMAL。浮点数类型和定点数类型都可以用（M，N）来表示。其中，M称为精度，表示总共的位数；N称为标度，表示小数的位数。表5.3列出了MySQL中的小数类型和存储需求。

表5.3　MySQL中的小数类型

DECIMAL类型不同于FLOAT和DOUBLE，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;语句查看警告信息：

可以看到，FLOAT和DOUBLE在进行四舍五入时没有给出警告，只给出z字段数值被截断的警告。查看结果：

FLOAT和DOUBLE在不指定精度时，默认会按照实际的精度（由计算机硬件和操作系统决定），DECIMAL若不指定精度则默认为(10,0)。

浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围；它的缺点是会引起精度问题。

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。

【例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查看错误信息：

可以看到，插入的第3个值2166超过了YEAR类型的取值范围，此时不能正常执行插入操作，查看结果：

由结果可以看到，当插入值为数值类型的2010或者字符串类型的‘2010’时，都正确地储存到了数据库中；而当插入值‘2166’时，由于超出了YEAR类型的取值范围，因此，不能插入值。

【例5.4】向tmp3表中y字段插入2位字符串表示的YEAR值，分别为‘0’、‘00’、‘77’和‘10’，SQL语句如下：

①删除表中的数据：

　　DELETE FROM tmp3;

②向表中插入数据：

　　INSERT INTO tmp3 values('0'),('00'),('77'),('10');

③查看结果：

由结果可以看到，字符串‘0’和‘00’的作用相同，分别都转换成了2000年；‘77’转换为1977；‘10’转换为2010。

【例5.5】向tmp3表中y字段插入2位数字表示的YEAR值，分别为0、78和11，SQL语句如下：

①删除表中的数据：

　　DELETE FROM tmp3;

②向表中插入数据：

　　INSERT INTO tmp3 values(0),(78),(11);

③查看结果：

由结果可以看到，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。

【例5.6】创建数据表tmp4，定义数据类型为TIME的字段t，向表中插入值‘10:05:05’，‘23:23’，‘2 10:10’，‘3 02’，‘10’，SQL语句如下：

①创建表tmp4：

　　CREATE TABLE tmp4( t TIME );

②向表中插入数据：

③查看结果：

由结果可以看到，‘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。

【例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;”查看错误信息，如下所示：

可以看到，第二次在插入记录的时候，数据超出了范围，原因是107010的分钟部分超过了60（分钟部分是不会超过60的）。查看结果：

由结果可以看到，‘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());

③查看结果：

由结果可以看到，获取系统当前的日期时间插入到TIME类型的列t，因为读者输入语句的时间不确定，因此获取的值可能与这里的不同，但都是系统当前的日期时间值。

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');

③查看插入结果：

可以看到，各个不同类型的日期值都正确地插入到了数据表中。

【例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');

③查看插入结果：

【例5.11】向tmp5表中插入YYYYMMDD和YYMMDD数字格式日期，SQL语句如下：

①删除表中的数据：

　　DELETE FROM tmp5;

②向表中插入YYYYMMDD和YYMMDD数字格式日期：

　　mysql> INSERT INTO tmp5 values (19990909),(990909), ( 000101) ,( 111111);

③查看插入结果：

【例5.12】向tmp5表中插入系统当前日期，SQL语句如下：

①删除表中的数据：

　　DELETE FROM tmp5;

②向表中插入系统当前日期：

　　mysql> INSERT INTO tmp5 values( CURRENT_DATE() ),( NOW() );

③查看插入结果：

CURRENT_DATE只返回当前日期值，不包括时间部分；NOW()函数返回日期和时间值，在保存到数据库时，只保留了其日期部分。

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；输入981231050505，插入数据的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”格式日期：

③查看插入结果：

可以看到，各个不同类型的日期值都正确地插入到了数据表中。

【例5.14】向tmp6表中插入“YY-MM-DD HH:MM:SS”和“YYMMDDHHMMSS”字符串格式日期和时间值，SQL语句如下：

①删除表中的数据：

　　DELETE FROM tmp6;

②向表中插入“YY-MM-DD HH:MM:SS”和“YYMMDDHHMMSS”格式日期：

③查看插入结果：

【例5.15】向tmp6表中插入YYYYMMDDHHMMSS和YYMMDDHHMMSS数字格式日期和时间值，SQL语句如下：

①删除表中的数据：

　　DELETE FROM tmp6;

②向表中插入YYYYMMDDHHMMSS和YYMMDDHHMMSS数字格式日期和时间：

　　mysql> INSERT INTO tmp6 values(19990909090909), (101010101010);

③查看插入结果：

【例5.16】向tmp6表中插入系统当前日期和时间值，SQL语句如下：

①删除表中的数据：

　　DELETE FROM tmp6;

②向表中插入系统当前日期：

　　mysql> INSERT INTO tmp6 values( NOW() );

③查看插入结果：

NOW()函数返回当前系统的日期和时间值，格式为“YYYY-MM-DD HH:MM:SS”。

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语句如下：

①创建数据表tmp7：

　　CREATE TABLE tmp7( ts TIMESTAMP);

②向表中插入数据：

③查看插入结果：

由结果可以看到，‘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()被转换为系统当前日期时间2018-11-09 17:08:25。

【例5.18】向tmp7表中插入当前日期，查看插入值，更改时区为东10区，再次查看插入值，SQL语句如下：

①删除表中的数据：

　　DELETE FROM tmp7;

②向表中插入系统当前日期：

　　mysql> INSERT INTO tmp7 values( NOW() );

③查看当前时区下的日期值：

④查询结果为插入时的日期值。读者所在时区一般为东8区，下面修改当前时区为东10区，SQL语句如下：

　　mysql> set time_zone='+10:00';

⑤再次查看插入时的日期值：

由结果可以看到，因为东10区时间比东8区快2个小时，因此查询的结果经过时区转换之后，显示的值增加了2个小时。相同的，如果时区每减小一个值，则查询显示的日期中的小时数减1。

5.1.4　文本字符串类型

字符串类型用来存储字符串数据，除了可以存储字符串数据之外，还可以存储其他数据，比如图片和声音的二进制数据。MySQL支持两类字符型数据：文本字符串和二进制字符串。本小节主要讲解文本字符串类型。文本字符串可以进行区分或者不区分大小写的串比较，还可以进行模式匹配查找。在MySQL中，文本字符串类型是指CHAR、VARCHAR、TEXT、ENUM和SET。表5.5列出了MySQL中的文本字符串数据类型。

表5.5　MySQL中文本字符串数据类型

VARCHAR和TEXT类型与下一小节讲到的BLOB都是变长类型，其存储需求取决于列值的实际长度（在前面的表格中用L表示），而不是取决于类型的最大可能尺寸。例如，一个VARCHAR(10)列能保存最大长度为10个字符的字符串，实际的存储需要是字符串的长度L加上1字节（记录字符串的长度）。对于字符“abcd”，L是4而存储要求是5字节。本节将介绍这些数据类型的作用以及如何在查询中使用这些类型。

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：

②输入数据：

　　INSERT INTO tmp8 VALUES('ab　', 'ab　');

③查询结果：

从查询结果可以看到，ch在保存“ab　”时将末尾的两个空格删除了，而vch字段保留了末尾的两个空格。

2．TEXT类型

TEXT列保存非二进制字符串，如文章内容、评论等。当保存或查询TEXT列的值时，不删除尾部空格。Text类型分为4种：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。不同的TEXT类型的存储空间和数据长度不同。

（1）TINYTEXT最大长度为255（2⁸–1）字符的TEXT列。

（2）TEXT最大长度为65535（2¹⁶–1）字符的TEXT列。

（3）MEDIUMTEXT最大长度为16777215（2²⁴–1）字符的TEXT列。

（4）LONGTEXT最大长度为4294967295（2³²–1）或4GB字符的TEXT列。

3．ENUM类型

ENUM是一个字符串对象，其值为表创建时在列规定中枚举的一列值。语法格式如下：

　　字段名 ENUM('值1','值2',...,'值n')

其中，“字段名”指将要定义的字段，“值n”指枚举列表中的第n个值。ENUM类型的字段在取值时，只能在指定的枚举列表中取，而且一次只能取一个。创建的成员中有空格时，其尾部的空格将自动被删除。ENUM值在内部用整数表示，并且每个枚举值均有一个索引值：列表值所允许的成员值从1开始编号，MySQL存储的就是这个索引编号。枚举最多可以有65535个元素。

例如，定义ENUM类型的列('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);

③查看索引值：

可以看到，这里的索引值和前面所述的相同。

【例5.22】创建表tmp10，定义INT类型的soc字段，ENUM类型的字段level，并且列表值为('excellent','good', 'bad')，向表tmp10中插入数据(70,'good')、(90,1)、(75,2)、(50,3)、(100,'best')，SQL语句如下：

①创建数据表：

　　CREATE TABLE tmp10 (soc INT, level enum('excellent', 'good','bad') );

②插入数据：

　　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列表中，所以对数据进行了阻止插入操作，查询结果如下：

由结果可以看到，因为ENUM列表中的值在MySQL中都是以编号序列存储的，所以插入列表中的值“good”或者插入其对应序号‘2’的结果是相同的。“best”不是列表中的值，因此不能插入数据。

4．SET类型

SET是一个字符串对象，可以有零或多个值。SET列最多可以有64个成员，其值为表创建时规定的一列值。指定包括多个SET成员的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')，SQL语句如下：

①创建表tmp11：

　　CREATE TABLE tmp11 ( s SET('a', 'b', 'c', 'd'));

②插入数据：

　　INSERT INTO tmp11 values('a'),( 'a,b,a'),('c,a,d');

③再次插入数据：

　　mysql>INSERT INTO tmp11 values ('a,x,b,y');
　　ERROR 1265 (01000): Data truncated for column 's' at row 1

由于插入了SET列不支持的值，因此MySQL给出错误提示。

④查看结果：

从结果可以看到，对于SET来说，如果插入的值为重复的，则只取一个，例如插入“a,b,a”，则结果为“a,b”；如果插入了不按顺序排列的值，则自动按顺序插入，例如插入“c,a,d”，结果为“a,c,d”；如果插入了不正确的值，那么该值将被阻止插入，例如插入值“a,x,b,y”。

5.1.5　二进制字符串类型

前面讲解了存储文本的字符串类型，这一小节将讲解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：

　　CREATE TABLE tmp12( b BIT(4) );

②插入数据：

　　mysql> INSERT INTO tmp12 VALUES(2), (9), (15);

③查询插入结果：

b+0表示将二进制的结果转换为对应的数字的值，BIN()函数将数字转换为二进制。从结果可以看到，成功地将3个数插入表中。

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：

　　CREATE TABLE tmp13(
　　b binary(3),　vb varbinary(3));

②插入数据：

　　INSERT INTO tmp13 VALUES(5,5);

③查看两个字段存储数据的长度：

可以看到，b字段的值数据长度为3，而vb字段的数据长度仅为插入的一个字符的长度1。

如果想要进一步确认‘5’在两个字段中不同的存储方式，输入如下语句：

由执行结果可以看出，b字段和vb字段的长度是截然不同的，因为b字段不足的空间填充了‘\0’，而vb字段则没有填充。

3．BLOB类型

BLOB是一个二进制大对象，用来存储可变数量的数据。BLOB类型分为4种：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB，它们可容纳值的最大长度不同，如表5.9所示。

表5.9　BLOB类型的存储范围

BLOB列存储的是二进制字符串（字节字符串），TEXT列存储的是非二进制字符串（字符字符串）。BLOB列没有字符集，并且排序和比较基于列值字节的数值；TEXT列有一个字符集，并且根据字符集对值进行排序和比较。