12. MySQL数据类型

MySQL中的数据类型

常见数据类型的属性，如下：

CREATE TABLE temp (
  id INT,
  name VARCHAR(15) CHARACTER SET 'gbk'
) CHARACTER SET 'utf8';

整数类型

类型介绍

可选属性

M：显示宽度

M：表示显示宽度，取值范围是(0，255)。
例如：int(5)：当数据宽度小于5位的时候在数字前面需要用字符填满宽度。该项功能需要配合“ZEROFILL”使用，表示用“0”填满宽度，否则指定显示宽度无效
注意：

• 如果设置了显示宽度，那么当插入的数据宽度超过显示宽度限制时，不会截断或插入失败，还是按照类型的实际宽度进行保存，即显示宽度与类型可以存储的值范围无关。从MySQL8.0.17开始，整数数据类型不推荐使用显示宽度属性
• 整型数据类型可以在定义表结构时指定所需要的显示宽度，如果不指定，则系统为每一种类型指定默认的宽度值

举例：

UNSIGNED

UNSIGNED：无符号类型(非负)，所有的整数类型都有一个可选的属性UNSIGNED，无符号整数类型的最小取值为0。所以，如果需要在MySQL数据库中保存非负整数值时，可以将整数类型设置为无符号类型
说明：int类型默认显示宽度为int(11)，无符号int类型默认显示宽度为int(10)

ZEROFILL

ZEROFILL：0填充（如果某列是ZEROFILL，那么MySQL会自动为当前列添加UNSIGNED属性）。 如果指定了ZEROFILL只是表示不够M位时，用0在左边填充，如果超过M位，只要不超过数据存储范围即可
注意：

• int(M)所占的存储空间与M的取值无任何关系，都是在磁盘上占用4bytes的存储空间

• int(M)必须和UNSIGNED ZEROFILL一起使用才有意义，如果整数值超过M位，就按照实际位数显示，只是无须再用字符0进行填充

  类型选择

• 需要考虑存储空间和可靠性的平衡问题。一方面，用占用字节数少的整数类型可以节省存储空间；另一方面，要是使用的整数类型取值范围太小，一旦遇到超出取值范围的情况，就可能引起系统错误，影响可靠性

• 实际工作中， 系统故障产生的成本远远超过增加几个字段存储空间所产生的成本，因此，首先要保证可靠性，再考虑如何节省存储空间

  浮点类型

  类型介绍

• FLOAT表示单精度浮点数

• DOUBLE表示双精度浮点数
• REAL默认就是DOUBLE。如果你把SQL模式设定为启用REAL_AS_FLOAT，那么MySQL就认为REAL是FLOAT，可以通过以下SQL语句实现：SET sql_mode = "REAL_AS_FLOAT";

说明：

• FLOAT占用字节数少，取值范围小；DOUBLE占用字节数多，取值范围也大

• 浮点数类型的无符号数取值范围，只相当于有符号数取值范围的一半，也就是只相当于有符号数取值范围大于等于零的部分

  数据精度说明

• MySQL允许使用非标准语法：FLOAT(M, D)或DOUBLE(M, D)

  • M称为精度，D称为标度
  • M = 整数位 + 小数位，D = 小数位
  • D <= M <= 255，0 <= D <= 30
  • 举例：FLOAT(5, 2)取值范围是 -999.99~999.99，如果超过这个范围会报错
• FLOAT和DOUBLE类型在不指定(M, D)时，默认按照实际的精度来显示（由实际的硬件和操作系统决定）
• 也可以加UNSIGNED来修饰浮点类型，但是不会改变数据范围
• 不管是否显示设置了精度(M, D)，MySQL的处理方案如下：
  • 如果存储时，整数部分超出了范围，MySQL就会报错，不允许存储这样的值
  • 如果存储时，小数点部分超出了范围：
    • 若四舍五入后，整数部分没有超出范围，则只警告，但能成功操作并四舍五入删除多余的小数位后进行存储。例如在FLOAT(5, 2)列插入999.009，则存储999.01
    • 若四舍五入后，整数部分超出范围，则MySQL报错，拒绝处理。例如在FLOAT(5, 2)列插入999.995
• 从MySQL8.0.17开始，FLOAT(M, D)和DOUBLE(M, D)用法在官方文档中已经明确不推荐使用。关于FLOAT和DOUBLE的UNSIGNED也不推荐使用

  精度误差说明

  举例：
  
  原因：因为FLOAT和DOUBLE都是采用二进制的方式进行存储数据，如果尾数不是0或5，就无法用一个二进制数来精确表达，只能在取值允许的范围内进行四舍五入

  定点数类型

  只有DECIMAL一种类型

说明：

• 使用DECIMAL(M, D)的方式表示高精度小数
  • M称为精度，D称为标度
  • 0 <= M <= 65，0 <= D <= 30，D < M
  • 取值范围和DOUBLE类型一样
• 有效的数据范围是由M和D决定的，存储空间并不是固定的，由精度值M决定，所占的存储空间为M+2字节。因此，比起占用同样字节长度的定点数，浮点数表示的数值范围更大
• 定点数在MySQL内部是以字符串的形式进行存储，所以它一定是精准的
• 当DECIMAL类型不指定精度和标度时，其默认为DECIMAL(10, 0)。当数据的精度超出了定点数类型的精度范围时，同样会进行四舍五入处理

  位类型：BIT

  BIT类型中存储的是二进制值

说明：

• M表示二进制的位数，1 <= M <=64，如果没有指定，默认是1位
• 在向BIT类型的字段中插入数据时，一定要确保插入的数据在BIT类型支持的范围内
• 使用SELECT命令查询位字段时，
  • SELECT *表示按照十六进制显示数据
  • SELECT BIN(字段名)表示按照二进制显示数据
  • SELECT HEX(字段名)表示按照十六进制显示数据
  • SELECT 字段名+0表示按照十进制显示数据

    日期与时间类型

    类型介绍

    | 类型 | 名称 | 字节 | 日期格式 | 最小值 | 最大值 | | —- | —- | —- | —- | —- | —- | | YEAR | 年 | 1 | YYYY或YY | 1901 | 2155 | | DATE | 日期 | 3 | YYYY-MM-DD | 1000-01-01 | 9999-12-03 | | TIME | 时间 | 3 | HH:MM:SS | -835:59:59 | 838:59:59 | | DATETIME | 日期时间 | 8 | YYYY-MM-DD
    HH:MM:SS | 1000-01-01
    00:00:00 | 9999-12-31
    23:59:59 | | TIMESTAMP | 日期时间 | 4 | YYYY-MM-DD
    HH:MM:SS | 1970-01-01
    00:00:00 UTC | 2038-01-19
    03:14:07 UTC |

说明：对于TIME类型，不光表示一天之内的时间，还可以表示一个时间间隔，这个时间间隔可以超过24个小时

YEAR类型

• 在MySQL中，有以下几种存储格式：
  • 以4位字符串或数字格式表示YEAR类型，格式为YYYY，最小值为1901，最大值为2155
  • 以2位字符串或数字格式表示YEAR类型，最小值为00，最大值为99
    • 当取值为01到69时，表示2001到2069
    • 当取值为70到99时，表示1970到1999
    • 当取值为整数的0或00时，表示0000年
    • 当取值为字符串的 ‘0’ 时，表示2000年

• 从MySQL5.5.27开始，2位格式的YEAR已经不推荐使用。YEAR默认格式就是”YYYY”，没必要写成YEAR(4)

  DATE类型

• DATE类型表示日期，不包含时间部分，格式为YYYY-MM-DD

• 以YYYY-MM-DD格式或者YYYYMMDD格式表示的字符串日期，最小取值为1000-01-01，最大取值为9999-12-03。YYYYMMDD格式会被转化为YYYY-MM-DD格式
• 以YY-MM-DD格式或者YYMMDD格式表示的字符串日期
  • 当年份取值为00到69时，会被转化为2000到2069
  • 当年份取值为70到99时，会被转化为1970到1999

• 使用CURRENT_DATE()或者NOW()函数，会插入当前系统的日期

  TIME类型

• TIME类型表示时间，不包含日期部分，格式为HH:MM:SS

• 可以使用带有冒号的字符串，如'D HH:MM:SS'、'HH:MM:SS'、'HH:MM'、'D HH:MM'、'D HH'或'SS'格式，都能被正确插入到TIME类型的字段中
  • 如果使用带有冒号并且带有D的字符串插入TIME类型的字段时，D会被转化为小时，计算方式为D*24+HH
  • 如果使用带有冒号并且不带有D的字符串表示时间时，表示当天的时间，比如12:10表示12:10:00，而不是00:12:10
• 可以使用不带有冒号的字符串或者数字，格式为'HHMMSS'或者HHMMSS
  • 如果插入一个不合法的字符串或数字，会将其自动转化为00:00:00进行存储
  • 比如1210表示00:12:10，而不是12:10:00

• 使用CURRENT_DATE()或者NOW()函数，会插入当前系统的日期

  DATETIME类型

• 格式上为DATE类型和TIME类型的组合，表示为YYYY-MM-DD HH:MM:SS

• 以YYYY-MM-DD HH:MM:SS格式或者YYYYMMDDHHMMSS格式的字符串插入DATETIME类型的字段时，两位数的年份规则符合YEAR类型的规则，即00到69表示2000到2069，70到99表示1970到19999

• 使用CURRENT_DATE()或者NOW()函数，会插入当前系统的日期和时间

  TIMESTAMP类型

• 也是表示日期时间，格式与DATETIME类型相同，但是存储的时间范围比DATETIME要小很多，只能存储”1970-01-01 00:00:00 UTC”到”2038-01-19 03:14:07 UTC”之间的时间，其中，UTC表示世界标准时间

• 存储数据的时候需要对当前时间所在的时区进行转换，查询数据的时候再将时间转换回当前的时区。因此，使用TIMESTAMP存储的同一个时间值，在不同的时区查询时会显示不同的时间

• TIMESTAMP和DATETIME的区别：

  • TIMESTAMP存储空间比较小，表示的日期时间范围也比较小
  • 底层存储方式不同，TIMESTAMP底层存储的是毫秒值，距离1970-01-01 00:00:00 0毫秒的毫秒值
  • 两个日期比较大小或日期计算时，TIMESTAMP更方便、更快
  • TIMESTAMP会根据用户的不同时区，显示不同的结果。而DATETIME只能反映出插入时当地的时区

    开发中经验

• 在实际项目中尽量使用DATETIME，因为这个数据类型包括了完整的日期和时间信息，取值范围也最大，使用起来比较方便

• 此外，一般存储注册时间、商品发布时间时，建议使用TIMESTAMP，因为方便计算

  文本字符串类型

  | 文本字符串类型 | 值的长度 | 长度范围 | 占用的存储空间 | | —- | —- | —- | —- | | CHAR(M) | M | 0 <= M <= 255 | M个字节 | | VARCHAR(M) | M | 0 <= M <= 65535 | M+1个字节 | | TINYTEXT | L | 0 <= L <= 255 | L+2个字节 | | TEXT | L | 0 <= L <= 65535 | L+2个字节 | | MEDIUMTEXT | L | 0 <= L <= 16777215 | L+3个字节 | | LONGTEXT | L | 0 <= L <= 4294967295 | L+4个字节 | | ENUM | L | 1 <= L <= 65535 | 1或2个字节 | | SET | L | 0 <= L <= 64 | 1,2,3,4或8个字节 |

CHAR与VARCHAR类型

CHAR类型：

• CHAR(M)类型一般需要预先定义字符串类型，如果不指定(M)，则长度默认是1个字符
• 定义CHAR类型字段时，声明的字段长度即为CHAR类型数据所占的存储空间的字节数
• 如果数据的实际长度比CHAR类型声明的长度小，则会在右侧补充空格以达到指定的长度。但是，当MySQL检索CHAR类型的数据时，会去除尾部的空格

VARCHAR类型：

• VARCHAR(M)定义时，必须指定M，否则报错
• MySQL5.0以上版本，VARCHAR(20)指的是20个字符，所存储的数据不能超过20个字符
• 当MySQL检索VARCHAR类型的数据时，会保留数据尾部的空格

• VARCHAR类型的数据所占用的存储空间为字符串实际长度加1个字节
  

  TEXT类型

  待续

  ENUM类型

  待续

  SET类型

  待续

  二进制字符串类型

  待续

  JSON类型

  待续

  空间类型

  待续

  小结及选择建议

• 在定义数据类型时：

  • 如果确定是整数，就用INT
  • 如果是小数，一定要用定点数类型DECIMAL(M, D)
  • 如果是日期和时间，就用DATETIME
• 阿里巴巴《Java开发手册》说明：
  • 任何字段如果为非负数，必须是UNSIGNED
  • 小数类型为DECIMAL，禁止使用FLOAT和DOUBLE
    • 如果存储的数据范围超过DECIMAL的范围，建议将数据拆成整数和小数并分开存储
  • 如果存储的字符串长度几乎相等，使用CHAR定长字符串类型
  • VARCHAR是可变长字符串，不预先分配存储空间，长度不要超过 5000。如果存储长度大于此值，定义字段类型为TEXT，独立出来一张表，用主键来对应，避免影响其它字段索引效率