MySQL 9从入门到性能优化：视频教学版最新章节_王英英著

3.1　MySQL的数据类型

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

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

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

（3）字符串类型：包括文本字符串类型CHAR、VARCHAR、TEXT、ENUM、SET，以及二进制字符串类型BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB。

3.1.1　整数类型

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

表3.1　MySQL中的整数类型

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

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

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

不同的整数类型有不同的取值范围，并且需要不同的存储空间，因此应该根据实际需要选择合适的数据类型，这样有利于提高查询的效率和节省存储空间。

3.1.2　小数类型

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

表3.3　MySQL中的小数类型

DECIMAL类型不同于FLOAT和DOUBLE，它实际是以串存放的，可能的最大取值范围与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、DOUBLE和DECIMAL(5,1)，SQL语句如下：

    CREATE TABLE tmp2 ( x FLOAT,  y DOUBLE,  z DECIMAL(5,1) );

向表中插入数据5.12、5.15和5.123，SQL语句如下：

    mysql>INSERT INTO tmp2 VALUES(5.12, 5.15, 5.123);

在插入数据时，MySQL给出了一个警告信息，使用SHOW WARNINGS;语句查看警告信息：

可以看到，给出z字段数值被截断的警告。使用SELECT * FROM tmp2语句查看数据输入结果，结果如下：

3.1.3　日期与时间类型

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

表3.4　日期与时间数据类型

1．YEAR类型

YEAR是一个单字节类型，用于表示年，在存储时只需要1字节。可以使用以下3种格式指定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”。

（1）创建表tmp3，SQL语句如下：

    CREATE TABLE tmp3(y YEAR );

（2）向表中插入数据：

    mysql> INSERT INTO tmp3 values(2010),('2010');

（3）再次向表中插入数据：

    mysql> INSERT INTO tmp3 values ('2166');
    ERROR 1264 (22003): Out of range value for column 'y' at row 1

（4）上述语句执行之后，MySQL给出了一条错误提示，使用SHOW查看错误信息：

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

（5）查看插入结果：

    mysql> SELECT * FROM tmp3;
    +------+
    | y    |
    +------+
    | 2010 |
    | 2010 |
    +------+

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

【 例3.4 】向表tmp3中的y字段插入2位字符串表示的YEAR值，分别为“0”“00”“77”和“10”。

（1）删除表中的数据：

    DELETE FROM tmp3;

（2）向表中插入数据：

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

（3）查看插入结果：

    mysql> SELECT * FROM tmp3;
    +------+
    | y    |
    +------+
    | 2000 |
    | 2000 |
    | 1977 |
    | 2010 |
    +------+

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

【 例3.5 】向表tmp3中的y字段插入2位数字表示的YEAR值，分别为0、78和11。

（1）删除表中的数据：

    DELETE FROM tmp3;

（2）向表中插入数据：

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

（3）查看插入结果：

    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~31的值。在插入数据库时，D被转换为小时保存，格式为“D*24 + HH”。

（2）“HHMMSS”格式的、没有间隔符的字符串或者HHMMSS格式的数值，假定是有意义的时间。例如，“101112”被理解为“10:11:12”，而“109712”是不合法的（它有一个没有意义的分钟部分），存储时将变为00:00:00。

提示为TIME列分配简写值时应注意：如果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”。

（1）创建表tmp4：

    CREATE TABLE tmp4( t TIME );

（2）向表中插入数据：

        mysql> INSERT INTO tmp4 values('10:05:05 '), ('23:23'), ('2 10:10'), ('3 02'),
    ('10');

（3）查看插入结果：

    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。

【 例3.7 】向表tmp4中插入值“101112”、111213、“0”和107010。

（1）删除表中的数据：

    DELETE FROM tmp4;

（2）向表中插入数据：

    mysql>INSERT INTO tmp4 values('101112'),(111213),( '0');

（3）再向表中插入数据：

    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（分钟部分是不会超过60的）。

（4）查看插入结果：

    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字段列插入值。

【 例3.8 】向表tmp4中插入系统当前时间。

（1）删除表中的数据：

    DELETE FROM tmp4;

（2）向表中插入数据：

    mysql> INSERT INTO tmp4 values (CURRENT_TIME) ,(NOW());

（3）查看插入结果：

    mysql> SELECT * FROM tmp4;
    +----------+
    | t        |
    +----------+
    | 08:43:51 |
    | 08:43:51 |
    +----------+

由结果可以看到，系统当前的日期已插入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”字符串格式的日期。

（1）创建表tmp5：

    MySQL> CREATE TABLE tmp5(d DATE);

（2）向表中插入“YYYY-MM-DD”和“YYYYMMDD”格式的日期：

    MySQL> INSERT INTO tmp5 values('1998-08-08'),('19980808'),('20101010');

（3）查看插入结果：

    MySQL> SELECT * FROM tmp5;
    +------------+
    | d          |
    +------------+
    | 1998-08-08 |
    | 1998-08-08 |
    | 2010-10-10 |
    +------------+

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

【 例3.10 】向表tmp5中插入“YY-MM-DD”和“YYMMDD”字符串格式的日期。

（1）删除表中的数据：

    DELETE FROM tmp5;

（2）向表中插入“YY-MM-DD”和“YYMMDD”格式的日期：

    mysql> INSERT INTO tmp5 values ('99-09-09'),( '990909'), ('000101') ,('111111');

（3）查看插入结果：

    mysql> SELECT * FROM tmp5;
    +-------------+
    | d           |
    +-------------+
    | 1999-09-09  |
    | 1999-09-09  |
    | 2000-01-01  |
    | 2011-11-11  |
    +-------------+

【 例3.11 】向表tmp5中插入YYYYMMDD和YYMMDD数字格式日期。

（1）删除表中的数据：

    DELETE FROM tmp5;

（2）向表中插入YYYYMMDD和YYMMDD格式的日期：

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

（3）查看插入结果：

    mysql> SELECT * FROM tmp5;
    +-------------+
    | d           |
    +-------------+
    | 1999-09-09  |
    | 1999-09-09  |
    | 2000-01-01  |
    | 2011-11-11  |
    +-------------+

【 例3.12 】向表tmp5中插入系统当前日期。

（1）删除表中的数据：

    DELETE FROM tmp5;

（2）向表中插入系统当前日期：

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

（3）查看插入结果：

    mysql> SELECT * FROM tmp5;
    +-------------+
    | d           |
    +-------------+
    | 2024-08-09  |
    | 2024-08-09  |
    +-------------+

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。

【 例3.13 】创建数据表tmp6，定义数据类型为DATETIME的字段dt，向表中插入“YYYY-MM-DD HH:MM:SS”和“YYYYMMDDHHMMSS”字符串格式日期和时间值。

（1）创建表tmp6：

    CREATE TABLE tmp6(  dt DATETIME );

（2）向表中插入“YYYY-MM-DD HH:MM:SS”和“YYYYMMDDHHMMSS”格式的日期：

        mysql> INSERT INTO tmp6 values('1998-08-08 08:08:08'),('19980808080808'),
    ('20101010101010');

（3）查看插入结果：

    mysql> SELECT * FROM tmp6;
    +------------------------+
    | dt                     |
    +------------------------+
    | 1998-08-08 08:08:08    |
    | 1998-08-08 08:08:08    |
    | 2010-10-10 10:10:10    |
    +------------------------+

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

【 例3.14 】向表tmp6中插入“YY-MM-DD HH:MM:SS”和“YYMMDDHHMMSS”字符串格式的日期和时间值。

（1）删除表中的数据：

    DELETE FROM tmp6;

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

         mysql> INSERT INTO tmp6 values('99-09-09 09:09:09'),('990909090909'),
    ('101010101010');

（3）查看插入结果：

    mysql> SELECT * FROM tmp6;
    +-------------------------+
    | dt                      |
    +-------------------------+
    | 1999-09-09 09:09:09     |
    | 1999-09-09 09:09:09     |
    | 2010-10-10 10:10:10     |
    +-------------------------+

【 例3.15 】向表tmp6中插入YYYYMMDDHHMMSS和YYMMDDHHMMSS数字格式的日期和时间值。

（1）删除表中的数据：

    DELETE FROM tmp6;

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

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

（3）查看插入结果：

    mysql> SELECT * FROM tmp6;
    +--------------------------+
    | dt                       |
    +-------------------------+
    | 1999-09-09 09:09:09     |
    | 2010-10-10 10:10:10     |
    +-------------------------+

【 例3.16 】向表tmp6中插入系统当前日期和时间。

（1）删除表中的数据：

    DELETE FROM tmp6;

（2）向表中插入系统当前日期和时间：

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

（3）查看插入结果：

    mysql> SELECT * FROM tmp6;
    +-------------------------+
    | dt                     |
    +-------------------------+
    | 2024-08-09 17:07:30     |
    +-------------------------+

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）为世界标准时间，因此插入的数据要保证在合法的取值范围内。

【 例3.17 】创建数据表tmp7，定义数据类型为TIMESTAMP的字段ts，向表中插入值“19950101010101”“950505050505”“1996-02-02 02:02:02”“97@03@03 03@03@03”和121212121212、NOW()。

（1）创建数据表tmp7：

    CREATE TABLE tmp7( ts TIMESTAMP);

（2）向表中插入数据：

    INSERT INTO tmp7 values ('19950101010101'),
    ('950505050505'),
    ('1996-02-02 02:02:02'),
    ('97@03@03 03@03@03'),
    (121212121212),
    ( NOW() );

（3）查看插入结果：

    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 |
    | 2024-08-09 17:08:25 |
    +---------------------+

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

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

【 例3.18 】向表tmp7中插入当前日期，查看插入值，更改时区为东十区，再次查看插入值。

（1）删除表中的数据：

    DELETE FROM tmp7;

（2）向表中插入系统当前日期：

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

（3）查看当前时区下的日期值：

    mysql> SELECT * FROM tmp7;
    +-------------------------+
    | ts                     |
    +-------------------------+
    | 2024-08-09 17:12:20     |
    +-------------------------+

查询结果为插入时的日期和时间。

（4）读者所在时区一般为东八区，下面修改当前时区为东十区：

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

（5）再次查看插入时的日期值：

    mysql> SELECT * FROM tmp7;
    +-------------------------+
    | ts                     |
    +-------------------------+
    | 2024-08-09 19:12:20     |
    +-------------------------+

由结果可以看到，因为东十区时间比东八区快2个小时，因此查询的结果经过时区转换之后，显示的值增加了2个小时。对应地，每减少1个时区，则查询显示的日期中的小时数减1。

提示如果为一个DATETIME或TIMESTAMP对象分配一个DATE值，那么结果值的时间部分将被设置为“00:00:00”，因为DATE值未包含时间信息。如果为一个DATE对象分配一个DATETIME或TIMESTAMP值，那么结果值的时间部分将被删除，因为DATE值未包含时间信息。

3.1.4　文本字符串类型

字符串类型用来存储字符串数据，除此之外，还可以存储其他数据，比如图片和声音的二进制数据。MySQL支持两类字符型数据：文本字符串和二进制字符串。本小节主要讲解文本字符串类型。

文本字符串不仅可以进行区分或者不区分大小写的字符串比较，还可以进行模式匹配查找。表3.5列出了MySQL中的文本字符串类型。

表3.5　MySQL中文本字符串类型

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

1．CHAR和VARCHAR类型

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

VARCHAR(M)是长度可变的字符串，M表示最大列长度，其范围是0~65535。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 ”。

（1）创建表tmp8：

    CREATE TABLE tmp8(ch  CHAR(4),  vch  VARCHAR(4));

（2）插入数据：

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

（3）查询插入结果：

    mysql> SELECT concat('(', ch, ')'), concat('(',vch,')') FROM tmp8;
    +----------------------+---------------------+
    | concat('(', ch, ')') | concat('(',vch,')') |
    +----------------------+---------------------+
    | (ab)                | (ab  )              |
    +----------------------+---------------------+
    1 row in set (0.00 sec)

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

提示在表3.6中，最后一行的字符串只有在使用“不严格”模式时，才会被截断插入；如果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的最大长度为65535（2 ¹⁶ –1）个字符。

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

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

3．ENUM类型

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

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

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

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

表3.7　ENUM类型的取值范围

ENUM值依照列索引顺序排列，并且空字符串排在非空字符串前，NULL排在其他所有的枚举值前。这一点也可以从表3.7中看到。

在这里，有一个方法可以查看列成员的索引值，如【例3.21】所示。

【 例3.21 】创建表tmp9，定义ENUM类型的列enm('first'，'second'，'third')，查看列成员的索引值。

（1）创建表tmp9：

    CREATE TABLE tmp9( enm ENUM('first','second','third') );

（2）插入各个列值：

    INSERT INTO tmp9 values('first'),('second') ,('third') , (NULL);

（3）查看索引值：

可以看到，这里的索引值和表3.7中的相同。

提示 ENUM列总有一个默认值：如果将ENUM列声明为NULL，则NULL为该列的一个有效值，并且默认值为NULL；如果ENUM列被声明为NOT NULL，其默认值为允许的值列表中的第以个元素。

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

（1）创建数据表：

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

（2）插入数据：

    INSERT INTO tmp10 values(70,'good'), (90,1),(75,2),(50,3);

（3）再次插入数据：

    mysql>INSERT INTO tmp10 values (100,'best');
    ERROR 1265 (01000): Data truncated for column 'level' at row 1

这里系统提示错误信息，可以看到，由于字符串值'best'不在ENUM列表中，因此阻止将它插入表中。

（4）查询插入结果：

由结果可以看到，因为ENUM列表中的值在MySQL中都是以编号序列存储的，所以插入列表中的值“good”与插入其对应序号“2”的结果是相同的，而“best”不是列表中的值，所以不能进行插入操作。

4．SET类型

SET是一个字符串对象，可以有0个或多个值。SET列最多可以有64个成员，其值为表创建时规定的一个列值。指定包括多个SET成员的SET列值时，各成员之间用逗号（,）隔开。语法格式如下：

    SET('值1','值2',...,'值n')

与ENUM类型相同，SET值在内部用整数表示，列表中每一个值都有一个索引编号。当创建表时，SET成员值的尾部空格将自动被删除。与ENUM类型不同的是，ENUM类型的字段只能从定义的多个列值中选择一个值插入，而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')。

（1）创建表tmp11：

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

（2）插入数据：

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

（3）再次插入数据：

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

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

（4）查看插入结果：

    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”，则提示错误并阻止插入。

3.1.5　二进制字符串类型

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

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

1．BIT类型

BIT(M)是位字段类型。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)类型的字段中的。

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

（1）创建表tmp12：

    CREATE TABLE tmp12( b BIT(4) );

（2）插入数据：

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

（3）查询插入结果：

    mysql> SELECT BIN(b+0) FROM tmp12;
    +------------+
    | BIN(b+0)  |
    +------------+
    | 10         |
    | 1001      |
    | 1111      |
    +------------+

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。

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

（1）创建表tmp13：

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

（2）插入数据：

    INSERT INTO tmp13 VALUES(5,5);

（3）查看两个字段存储数据的长度：

    mysql> SELECT length(b), length(vb) FROM tmp13;
    +-----------+---------------+
    | length(b) | length(vb)    |
    +-----------+---------------+
    |       3   |         1     |
    +-----------+---------------+

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

3．BLOB类型

BLOB是一个二进制大对象，用来存储可变数量的数据。BLOB类型分为4种：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB，如表3.9所示。

表3.9　BLOB类型的存储范围

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

3.1 MySQL的数据类型

3.1.1 整数类型

3.1.2 小数类型

3.1.3 日期与时间类型

1．YEAR类型

2．TIME类型

3．DATE类型

4．DATETIME类型

5．TIMESTAMP类型

3.1.4 文本字符串类型

1．CHAR和VARCHAR类型

2．TEXT类型

3．ENUM类型

4．SET类型

3.1.5 二进制字符串类型

1．BIT类型

2．BINARY和VARBINARY类型

3．BLOB类型

3.1　MySQL的数据类型

3.1.1　整数类型

3.1.2　小数类型

3.1.3　日期与时间类型

3.1.4　文本字符串类型

3.1.5　二进制字符串类型