3.1 MySQL数据类型介绍_SQL基础教程（视频教学版）-QQ阅读男生玄幻网

书名：SQL基础教程（视频教学版）
作者名：李小威
本章字数：10345字
更新时间：2021-04-04 10:20:31

3.1　MySQL数据类型介绍

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

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

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

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

3.1.1　整数类型

数值类型主要用来存储数字。MySQL提供了多种数值类型，不同的数据类型提供不同的取值范围。可以取值的范围越大，所需要的存储空间也越大。整数类型的属性字段可以添加AUTO_INCREMENT自增约束条件。表3.1列出了MySQL中的整数类型。

表3.1　MySQL中的整数类型

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

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

在第2.4节中，有如下创建表的语句：

id字段的数据类型为INT(11)，后面的数字11表示的是该数据类型指定的显示宽度，指定能够显示的数值中数字的个数。例如，假设声明一个INT类型的字段：

    year INT(4)

该声明指明，在year字段中的数据一般只显示4位数字的宽度。

在这里读者要注意：显示宽度和数据类型的取值范围是无关的。显示宽度只是指明MySQL最大可能显示的数字个数，要显示的数值的位数小于指定的宽度时会由空格填充；如果插入了大于显示宽度的数值，只要该值不超过该类型整数的取值范围，数值依然可以插入，而且能够显示出来。例如，向year字段插入一个数值19999，当使用SELECT查询该列值的时候，MySQL显示的将是完整的带有5位数字的19999，而不是4位数字的值。

其他整型数据类型也可以在定义表结构时指定所需要的显示宽度，如果不指定，系统会为每一种类型指定默认的宽度值，如例3.1所示。

【例3.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。同理，其他整数类型的默认显示宽度与其有符号数的最小值的宽度相同。

提示

显示宽度只用于显示，并不能限制取值范围和所占用的空间。例如：INT(3)会占用4个字节的存储空间，并且允许的最大值也不会是999，而是INT整型所允许的最大值。

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

3.1.2　浮点数类型和定点数类型

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

表3.3　MySQL中表示小数的类型

DECIMAL类型不同于FLOAT和DOUBLE：DECIMAL实际是以字符串形式存放的，可能的最大取值范围与DOUBLE一样，但是其有效的取值范围由M和D的值决定。如果改变M而固定D，则其取值范围将随M的变大而变大。从表3.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。

提示

不论是定点数类型还是浮点数类型，如果用户指定的精度超出精度范围，就会按四舍五入进行处理。

【例3.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;”语句查看警告信息：

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

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

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

提示

在MySQL中，定点数以字符串形式存储，在对精度要求比较高的时候（如货币、科学数据等）使用DECIMAL的类型比较好。另外，两个浮点数进行减法和比较运算时容易出问题，所以在使用浮点数时需要注意，并应尽量避免用浮点数进行比较运算。

3.1.3　日期与时间类型

MySQL中有多种表示日期的数据类型，主要有DATETIME、DATE、TIMESTAMP、TIME和YEAR。例如，当只记录年份信息的时候，可以只使用YEAR类型，而没有必要使用DATE。每一个类型都有合法的取值范围，当指定不合法的值时，系统将“零”值插入到数据库中。本节将介绍MySQL日期和时间类型的使用方法。表3.4列出了MySQL中的日期与时间类型。

表3.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。

【例3.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类型的取值范围，因此没有成功插入。

【例3.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。

【例3.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。

提示

为TIME列（或字段）分配简写值时应注意：如果没有冒号，MySQL解释值时假定最右边的两位表示秒。MySQL解释TIME值为过去的时间而不是当天的时间。例如，读者可能认为'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。

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

查看结果：

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

【例3.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

可以看到，在插入数据107010时，MySQL给出了一个错误提示信息。使用“SHOW WARNINGS;”查看错误信息，如下所示：

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

由结果可以看到，'101112'被转换为10:11:12；111213被转换为11:12:13；'0'被转换为00:00:00；107010因为是不合法的值，因此不能被插入。

也可以使用系统日期函数向TIME字段插入值。

【例3.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()插入系统的当前日期。

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

查看插入后的结果：

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

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

查看插入的结果：

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

删除表中的数据：

    DELETE FROM tmp5;

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

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

查看插入的结果：

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

删除表中的数据：

    DELETE FROM tmp5;

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

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

查看插入的结果：

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；输入981231050505，插入到数据库中类型为DATETIME的字段，其值转换为1998-12-31 05: 05: 05。

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

查看插入结果：

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

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

查看插入的结果：

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

删除表中的数据：

    DELETE FROM tmp6;

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

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

查看插入的结果：

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

删除表中的数据：

    DELETE FROM tmp6;

向表中插入系统当前的日期和时间值：

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

查看插入的结果：

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字段的取值范围，为'970-01-01 00:00:01' UTC～'2038-01-19 03:14:07' UTC。其中，UTC（Coordinated Universal Time）为世界标准时间。因此，在插入数据时，要保证在合法的取值范围内。

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

向表中插入数据：

查看插入的结果：

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

提示

TIMESTAMP与DATETIME除了存储占用的字节数和支持的范围不同之外，还有一个最大的区别就是：DATETIME在存储日期和时间的数据时，按实际输入的格式来存储，即输入什么就存储什么，与时区无关；而TIMESTAMP值的存储是以UTC（世界标准时间）格式来保存的，存储时对当前时区进行转换，检索时再转换回当前时区。即查询时，根据当前时区的不同，显示的时间值是不同的。

【例3.18】向tmp7表中插入当前日期1. BIT类型和时间，查看插入值，更改时区为东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。

提示

如果为一个DATETIME或TIMESTAMP对象分配一个DATE值，结果值的时间部分被设置为'00:00:00'，因为DATE值未包含时间部分的信息。如果为一个DATE对象分配一个DATETIME或TIMESTAMP值，结果值的时间部分被删除，因为DATE值不存储时间部分的值。

3.1.4　文本字符串类型

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

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

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

1. CHAR和VARCHAR类型

CHAR(M)为固定长度字符串，在定义时指定字符串字段的长度（注：这里的字段对应数据库中的列）。当保存时在右侧填充空格以达到指定的长度。M表示字段长度，M的范围是0~255个字符。例如，CHAR(4)定义了一个固定长度的字符串字段，其包含的字符个数最大为4。当检索到CHAR值时，尾部的空格将被删除。

VARCHAR(M)是长度可变的字符串。其中，M表示列中最大字段的长度，范围是0~65 535。VARCHAR的最大实际长度由最长的行的大小和使用的对应字符集来确定，其实际占用的空间为字符串的实际长度加1。例如，VARCHAR(50)定义了一个最大长度为50的字符串，如果插入的字符串只有10个字符，则实际存储的字符串为10个字符和一个字符串结束字符。VARCHAR在值保存和检索时其尾部的空格仍保留。

【例3.19】下面将不同字符串保存到CHAR(4)和VARCHAR(4)字段，以说明CHAR和VARCHAR之间的差别（表3.6）。

表3.6　CHAR(4)与VARCHAR(4)存储区别

对比结果可以看到，CHAR(4)定义了固定长度为4的字段，不管存入的数据长度为多少，所占用的空间均为4个字节。VARCHAR(4)定义的字段所占的字节数为实际长度加1。

当查询时CHAR(4)和VARCHAR(4)的值并不一定相同，如例3.20所示。

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

查询结果：

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

提示

在表3.7中，最后一行的值只有在使用“不严格”模式时，字符串才会被截断插入。如果MySQL运行在“严格”模式，超过字段长度的值不会被保存，并且会出现错误提示信息“ERROR 1406(22001): Data too long for column”，即字符串长度超过指定的长度，无法插入。

2. TEXT类型

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

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

（2）TEXT最大长度为65 535（2¹⁶–1）个字符。

（3）MEDIUMTEXT最大长度为16 777 215（2²⁴–1）个字符。

（4）LONGTEXT最大长度为4 294 967 295（2³²–1）个字符（即4GB的字符）。

3. ENUM类型

ENUM类型用于定义一种字符串对象，其中的值是表创建时为字段的取值规定的一组枚举值。语法格式如下：

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

字段名是指将要定义的字段，值n是指枚举列表中的第n个值。ENUM类型的字段在取值时，只能从指定的枚举列表中取值，而且一次只能取一个。如果枚举列表的成员中有空格时，则其尾部的空格将自动被删除。ENUM值在内部用整数表示，每个枚举值均有一个索引值：枚举列表中的成员从1开始编号，MySQL存储的就是这个索引编号。枚举列表最多可以包含65 535个成员（或元素）。

例如，为字段定义ENUM类型的枚举列表('first'，'second'，'third')，该列的字段可以取的值和每个值的索引如表3.7所示。

表3.7　ENUM类型取值范围的示例

ENUM值依照列表中的索引顺序排列，并且空字符串排在非空字符串之前，NULL值排在其他所有的枚举值之前。

查看枚举列表成员的索引值，方法如例3.21所示。

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

查看索引值：

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

提示

ENUM类型的字段总有一个默认值。如果将ENUM类型的字段声明为NULL，NULL值则为该字段的一个有效值，并且默认值为NULL。如果ENUM类型的字段被声明为NOT NULL，则其默认值为指定的枚举值列表的第1个成员（或元素）。

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

插入数据：

    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('值1','值2',...,'值n')

与ENUM类型相同，SET值在内部用整数表示，SET列表中的每一个值都有一个索引编号。当创建表时，SET成员值的尾部空格将自动被删除。与ENUM类型不同的是，ENUM类型的字段只能从定义的枚举列表值中选择一个值插入，而SET类型的字段可从定义的SET列表值中选择多个字符的联合值来插入。

如果SET列表成员的值内有重复，在SET类型的字段中插入这类值时，MySQL会自动删除其中重复的值。另外，对于插入到SET类型的字段的值，值内的顺序并不重要，MySQL会在存入数据库时，按照值定义的顺序来显示；如果插入了不正确的值，在默认情况下，MySQL将忽视这些值，并给出警告信息。

【例3.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'；如果插入了不按顺序排列的值，则MySQL会自动按顺序插入，例如'c,a,d'，结果为'a,c,d'；如果插入了不正确的值，则该值将被阻止插入，例如插入值'a,x,b,y'。

3.1.5　二进制字符串类型

前面讲解了存储文本的字符串类型，这一小节将讲解MySQL中存储二进制数据的字符串类型。MySQL中的二进制字符串类型有BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB，见表3.8。

表3.8　MySQL中的二进制字符串类型

1. BIT类型

BIT类型是位字段类型。M表示每个值的位数，范围为1~64。如果M被省略，则默认M为1。如果为BIT(M)字段分配的值的长度小于M位，则在值的左边用0填充。例如，为BIT(6)字段分配一个值b'101'，其效果与分配b'000101'相同。BIT数据类型用来保存位字段值，例如以二进制数的形式保存数据13（13以二进制数表示的形式为1101），需要位数至少为4位的BIT类型，即可把字段的类型定义为BIT(4)。大于二进制数1111的数据是不能插入定义为BIT(4)类型的字段中的。

【例3.24】创建表tmp12，定义BIT(4)类型的字段b，向表中插入数据2、9、15。

创建表tmp12，SQL语句如下：

    CREATE TABLE tmp12( b BIT(4) );

插入数据：

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

查询插入结果：

b+0表示将二进制数的结果转换为对应的数字的值，BIN()函数将一个数字转换为该数字对应的二进制数。从结果可以看到，上述SQL语句已经成功地将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。

【例3.25】创建表tmp13，定义BINARY(3)类型的字段b和VARBINARY(3)类型的字段vb，并向表中插入数据'5'，比较两个字段的存储空间。

首先创建表tmp13，输入SQL语句如下：

    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，它们可容纳值的最大长度不同，如表3.9所示。

表3.9　BLOB类型的存储容量

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