侧边栏壁纸
博主头像
张种恩的技术小栈博主等级

绿泡泡：___zze，添加备注来意

  • 累计撰写 748 篇文章
  • 累计创建 65 个标签
  • 累计收到 39 条评论

目 录CONTENT

文章目录
DataBase

《MySQL 5.5从零开始学》(7)之数据类型介绍

zze
zze
2020-01-11 / 0 评论 / 0 点赞 / 669 阅读 / 36805 字

本部分内容参考自《MySQL 5.5从零开始学》。

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 等；

整数类型

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

类型名称说明存储需求
TINYINT很小的整数1 个字节
SMALLINT小的整数2 个字节
MEDIUMINT中等大小的整数3 个字节
INT（INTEGER）普通大小的整数4 个字节
BIGINT大整数8 个字节

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

数据类型有符号无符号
TINYINT-128 ~ 1270 ~ 255
SMALLINT-32768 ~ 327670 ~ 65535
MEDIUMINT-8388608 ~ 83886070 ~ 16777215
INT（INTEGER）-2147483648 ~ 21474836470 ~ 4294967295
BIGINT-9223372036854775808 ~ 92233720368547758070 ~ 18446744073709551615

有如下创建表的语句：

CREATE TABLE tb_tmp1
(
    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 位数字的值。
其他整型数据类型也可以在定义表结构时指定所需要的显示宽度，如果不指定，则系统为每一种类型指定默认的宽度值。
例 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 查看表结构如下：

image.png

可以看到，系统将添加不同的默认显示宽度。这些显示宽度能够保证显示每一种数据类型可以取到取值范围内的所有值。例如 TINYINT 有符号数和无符号的数的取值范围分别为 -128~127 和 0~255，由于负号占了一个数字位，因此 TINYINT 默认的显示宽度为 4。同理，其他整数类型的默认显示宽度与其有符号数的最小值的宽度相同。
不同的整数类型有不同的取值范围，并且需要不同的存储空间，因此，应该根据实际需要选择最合适的类型，这样有利于提高查询的效率和节省存储空间。整数类型是不带小数部分的数值，现实生活中很多地方需要用到带小数的数值，下面将介绍MySQL中支持的小数类型。

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

浮点数类型和定点数类型

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

类型名称说明存储需求
FLOAT单精度浮点数4 个字节
DOUBLE双精度浮点数8 个字节
DECIMAL(M,D), DEC压缩的“严格”定点数M + 2 个字节

DECIMAL 类型不同于 FLOAT 和 DOUBLE，DECIMAL 实际是以串存放的，DECIMAL 可能的最大取值范围与 DOUBLE 一样，但是其有效的取值范围由 M 和 D 的值决定。如果改变 M 而固定 D，则其取值范围将随 M 的变大而变大。从上表可以看到，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.797693 1348623157E+308；

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

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

image.png

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

image.png

FLOAT 和 DOUBLE 在不指定精度时，默认会按照实际的精度（由计算机硬件和操作系统决定），DECIMAL 如不指定精度默认为 (10, 0)。
浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围；它的缺点是会引起精度问题。

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

日期与时间类型

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

类型名称日期格式日期范围存储需求
YEARYYYY1901 ~ 21551 字节
TIMEHH:MM:SS-838:59:59 ~ 838:59:593 字节
DATEYYYY-MM-DD1000-01-01 ~ 9999-12-33 字节
DATETIMEYYYY-MM-DD HH:MM:SS1000-01-01 00:00:00 ~ 9999-12-31 23:59:598 字节
TIMESTAMPYYYY-MM-DD HH:MM:SS1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC4 字节

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：创建数据表 tmp3，定义数据类型为 YEAR 的字段 y，向表中插入值 2010，'2010'，'2166'，SQL 语句如下：
首先创建表 tmp3：

CREATE TABLE tmp3( y YEAR);

向表中插入数据：

mysql> insert into tmp3 values(2010),('2010'),('2166');
ERROR 1264 (22003): Out of range value for column 'y' at row 3

可以看到，由于插入的第 3 个值 2166 超过了YEAR 类型的取值范围，所以 MySQL 拒绝插入。
去除第三个值执行下面操作：

mysql> insert into tmp3 values(2010),('2010');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

查看结果：

image.png

由结果可以看到，当插入值为数值类型的 2010 或者字符串类型的 '2010' 时，都正确的储存到了数据库中。

例 4：向 tmp3 表中 y 字段插入 2 位字符串表示的 YEAR 值，分别为 '0'、'00'、'77' 和 '10'，SQL 语句如下：
首先删除表中的数据：

DELETE FROM tmp3;

向表中插入数据：

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

查看结果：

image.png

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

例 5：向 tmp3 表中 y 字段插入 2 位数字表示的 YEAR 值，分别为 0、78 和 11，SQL 语句如下:
首先删除表中的数据：

DELETE FROM tmp3;

向表中插入数据：

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

查看结果：

image.png

由结果可以看到，0 被转换为 0000；78 被转换为 1978；11 被转换为 2011。

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。

例 6：创建数据表 tmp4，定义数据类型为 TIME 的字段 t，向表中插入值 '10:05:05'、'23:23'、'2 10:10'、'302' 、'10' ，SQL 语句如下：
首先创建表tmp4：

CREATE TABLE tmp4(t TIME);

向表中插入数据：

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

查看结果：

image.png

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

例 7：向表 tmp4 中插入值 '101112' 、111213、0，SQL 语句如下：
首先删除表中的数据：

DELETE FROM tmp4;

向表中插入数据：

INSERT INTO tmp4 VALUES('101112'),(111213),('0');

查看结果：

image.png

由结果可以看到，'101112' 被转换为 10:11:12；111213 被转换为 11:12:13；'0' 被转换为 00:00:00。
也可以使用系统日期函数向 TIME 字段列插入值。

例 8：向 tmp4 表中插入系统当前时间，SQL 语句如下:
首先删除表中的数据：

DELETE FROM tmp4;

向表中插入数据：

INSERT INTO tmp4 VALUES(CURRENT_TIME),(NOW());

查看结果：

image.png

由结果可以看到，获取系统当前的日期时间插入到 TIME 类型列，都是系统当前的日期时间值。

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. 以 YYMMDD 数字格式表示的日期，与前面相似，00-69 范围的年值转换为 2000~2069 ；70~99 范围的年值转换为 1970~1999。例如：输入 981231，插入数据的日期为 1998-12-31。
  4. 使用 CURRENT_ DATE 或者 NOW()，插入当前系统日期。

例 9：创建数据表 tmp5，定义数据类型为 DATE 的字段 d，向表中插入'YYYY-MM-DD' 和 'YYYYMMDD' 字符串格式日期，SQL 语句如下：
首先创建表 tmp5：

CREATE TABLE tmp5(d DATE);

向表中插入 'YYYY-MM-DD' 和 'YYYYMM-DD' 格式的日期：

INSERT INTO tmp5 VALUES('1998-08-08'),('19980808'),('20101010');

查看插入结果：

image.png

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

例 10：向 tmp5 表中插入 'YY-MM-DD' 和 'YYMMDD' 字符串格式日期，SQL 语句如下：
首先删除表中的数据：

DELETE FROM tmp5;

向表中插入 'YY-MM-DD' 和 'YYMMDD' 格式日期：

INSERT INTO tmp5 VALUES('99-09-09'),('990909'),('000101'),('111111');

查看插入结果：

image.png

例 11：向 tmp5 表中 YYMMDD 数字格式日期，SQL 语句如下:
首先删除表中的数据：

DELETE FROM tmp5;

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

INSERT INTO tmp5 VALUES(990909),(000101),(111111);

查看插入结果：

image.png

例 12：向 tmp5 表中插入系统当前日期，SQL 语句如下：
首先删除表中的数据：

DELETE FROM tmp5;

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

INSERT INTO tmp5 VALUES(CURRENT_TIME),(NOW());

查看插入结果：

image.png

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

MySQL 允许“不严格”语法：任何标点符号都可以用作日期部分之间的间隔符。例如，'98-11-31' 、'98.11.31'、'98/11/31' 和 '98@11@31' 是等价的，这些值也可以正确的插入到数据库。

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。
    例 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' 格式日期：

INSERT INTO tmp6 VALUES('1998-08-08 08:08:08'),('19980808080808'),('20101010101010');

查看插入结果：

image.png

可以看到，各个不同类型的日期值都正确的插入到了数据表中。
例 14：向 tmp6 表中插入 'YY-MM-DD HH:MM:SS' 和 'YYMMDDHHMMSS' 字符串格式日期和时间值，SQL 语句如下：
首先删除表中的数据：

DELETE FROM tmp6;

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

INSERT INTO tmp6 VALUES('99-09-09 09:09:09'),('990909090909'),('101010101010');

查看插入结果：

image.png

例 15：向 tmp6 表中插入 YYYYMMDDHHMMSS和 YYMMDDHHMMSS 数字格式日期和时间值，SQL 语句如下：
首先删除表中的数据：

DELETE FROM tmp6;

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

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

例 16：向 tmp6 表中插入系统当前日期和时间值，SQL 语句如下：
首先删除表中的数据：

DELETE FROM tmp6;

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

INSERT INTO tmp6 VALUES(NOW());

查看插入结果：

image.png

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' 是等价的，这些值都可以正确的插入数据库。

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，为世界标准时间)，因此在插入数据时，要保证在合法的取值
范围内。
例 17：创建数据表 tmp7，定义数据类型为 TIMESTAMP 的字段 ts, 向表中插入值 '19950101010101'、'950505050505'、'1996-02-02 02:02:02'、'97@03@0303@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());

查看插入结果：

image.png

由结果可以看到，'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() 被转换为系统当前日期时间 2011-07-22 09:17:49。

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

例 18：向 tmp7 表中插入当前日期，查看插入值，更改时区为东 10 区，再次查看插入值, SQL 语句如下:
首先删除表中的数据：

DELETE FROM tmp7;

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

INSERT INTO tmp7 values( NOW());

查看当前时区下日期值：

image.png

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

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

再次查看之前插入的日期值：

image.png

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

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

字符串类型

字符串类型用来存储字符串数据，除了可以存储字符串数据之外，还可以存储其他数据，比如图片和声音的二进制数据。字符串可以进行区分或者不区分大小写的串比较，另外，还可以进行模式匹配查找。MySQL 中字符串类型指 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET。下表列出了 MySQL 中的字符串数据类型。

类型名称说明存储需求
CHAR(M)固定长度非二进制字符串M 字节，1 <= M <= 255
VARCHAR(M)变长非二进制字符串L+1 字节，在此 L <= M 和 1 <= M <= 65535
TINYTEXT非常小的非二进制字符串L+1 字节，在此 L < 2^8
TEXT小的非二进制字符串L+2 字节，在此 L < 2^16
MEDIUMTEXT中等大小的非二进制字符串L+3 字节，在此 L < 2^24
LONGTEXT大的非二进制字符串L+4 字节，在此 L < 2^32
ENUM枚举类型，只能有一个枚举字符串值1 或 2 个字节，取决于枚举值的数目（最大值 65535）
SET一个设置，字符串对象可以有零个或多个 SET 成员1、2、3、4 或 8 个字节，取决于集合成员的数量（最多 64 个成员）

VARCHAR、BLOB 和 TEXT 类型是变长类型，对于其存储需求取决于列值的实际长度（在前面的表格中用 L 表示），而不是取决于类型的最大可能尺寸。例如，一个 VARCHAR(10) 列能保存最大长度为 10 个字符的一个字符串，实际的存储需要是字符串的长度 L，加上 1 个字节以记录字符串的长度。对于字符 'abcd'，L 是 4 而存储要求是 5 个字节。

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 在值保存和检索时尾部的空格仍保留。

例 19：下面将不同字符串保存到 CHAR(4) 和 VARCHAR(4) 列，说明 CHAR 和 VARCHAR 之间的差别，如下表所示：

插入值CHAR(4)存储需求VARCHAR(4)存储需求
''' '4 字节''1 字节
'ab''ab '4 字节'ab'3 字节
'abc''abc '4 字节'abc'4 字节
'abcd''abcd'4 字节'abcd'5 字节
'abcdef''abcd'4 字节abcd5 字节

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

对比结果可以看到，CHAR(4) 定义了固定长度为 4 的列，不管存入的数据长度为多少，所占用的空间均为 4 个字节。VARCHAR(4) 定义的列所占的字节数为实际长度加 1。
当查询时 CHAR(4) 和 VARCHAR(4) 的值并不一定相同。
例 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  ');

查询结果：

image.png

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

TEXT

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

  1. TINYTEXT 最大长度为 255(28-1) 字符的 TEXT 列。
  2. TEXT 最大长度为 65535(216-1 )字符的 TEXT 列。
  3. MEDIUMTEXT 最大长度为 16777215(224-1) 字符的 TEXT 列。
  4. LONGTEXT 最大长度为 4294967295 或 4GB(232-1) 字符的 TEXT 列。

ENUM

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

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

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

值索引
NULLNULL
''0
first1
second2
third3

ENUM 值依照列索引顺序排列，并且空字符串排在非空字符串前，NULL 值排在其他所有的枚举值前。
在这里，有一个方法可以查看列成员的索引值，如下面【例 21】所示。
例 21：创建表 tmp9，定义 ENUM 类型的列 enm('first', 'second', 'third')， 查看列成员的索引值，SQL 语句如下：
首先，创建 tmp9 表：

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

插入各个列值：

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

查看索引值：

image.png

上面插入的 '0' 将会作为 enm 的索引引用 enm 真正的值，由上述可以看到实际插入的值是空字符串 ''。
ENUM 列总有一个默认值。如果将 ENUM 列声明为 NULL, NULL 值则为该列的一个有效值，并且默认值为 NULL。如果ENUM 列被声明为 NOT NULL，其默认值为允许的值列表的第 1 个元素。

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

由结果可以看到，因为 ENUM 列表中的值在 MySQL 中都是以编号序列存储的，因此，插入列表中的值 'good' 或者插入其对应序号 2 的结果是相同的。

SET类型

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

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

与 ENUM 类型相同，SET 值在内部用整数表示，列表中每一个值都有一个索引编号。当创建表时，SET 成员值的尾部空格将自动被删除。但与 ENUM 类型不同的是，ENUM 类型的字段只能从定义的列值中选择一个值插入，而 SET 类型的列可从定义的列值中选择多个字符的联合。
如果插入 SET 字段中列值有重复，则 MySQL 自动删除重复的值；插入 SET 字段的值的顺序并不重要，MySQL 会在存入数据库时，按照定义的顺序显示；如果插入了不正确的值，默认情况下，MySQL 将忽视这些值，并给出警告。
例 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,b');

查看结果：

image.png

从结果可以看到，对于 SET 来说如果插入的值为重复的，则只取一个，例如 a,b,a，则结果为 a,b；如果插入了不按顺序排列值，则自动按顺序插入，例如 c,a,d，结果为 a,e,d。

二进制类型

MySQL 支持两类字符型数据:文本字符串和二进制字符串。MySQL 中的二进制数据类型有: BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB，下表列出了 MySQL 中的二进制数据类型。

类型名称说明存储需求
BIT(M)位字段类型大约 (M+7)/8 个字节
BINARY(M)固定长度二进制字符串M 个字节
VARBINARY(M)可变长度二进制字符串M+1 个字节
TINYBLOB(M)非常小的 BLOBL+1 字节，在此 L<2^8
BLOB(M)小 BLOBL+2 字节，在此 L<2^16
MEDIUMBLOB(M)中等大小的 BLOBL+3 字节，在此 L<2^24
LONGBLOB(M)非常大的 BLOBL+4 字节，在此 L<2^32

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) 类型的字段中的。
例 24、创建表 tmp12，定义 BIT(4) 类型的字段 b，向表中插入数据 2、9、15、16。
首先创建表 tmp12，SQL 语句如下：

CREATE TABLE tmp12(b BIT(4));

插入数据：

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

查询插入结果：

image.png

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

如插入值 16，它的二进制值为 10000，不可插入，所以要确保插入的值在指定的范围内。

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。

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

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

image.png

可以看到，b 字段的值数据长度为 3，而 vb 字段的数据长度仅为插入的一个字符的长度 1。如果想要进一步确认 '5' 在两个字段中不同的存储方式，输入如下语句：

image.png

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

BLOB

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

数据类型存储范围
TINYBLOB最大长度为 255(28-1) 字节
BLOB最大长度为 65535(216-1) 字节
MEDIUMBLOB最大长度为 16777215(224-1) 字节
LONGBLOB最大长度为 4294967295 或 4GB(232-1) 字节

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

0
《MySQL 5.5从零开始学》
版权归属： zze
本文链接： https://www.zze.xyz/archives/mysql55-7
许可协议： 本文使用《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》协议授权

评论区