MySQL 支持很多 SQL 数据类型,常用的有:数值类型,字符串类型,日期类型和 JSON 类型等等。

整数类型

类型 占用空间 有符号范围 无符号范围
TINYINT 1 -128 ~ 127 0 ~ 255
SMALLINT 2 -32768 ~ 32767 0 ~ 65535
MEDIUMINT 3 -8388608 ~ 8388607 0 ~ 16777215
INT 4 -2147483648 ~ 2147483647 0 ~ 4294967295
BIGINT 8 -263 ~ 263 - 1 0 ~ 264 - 1

整数类型的 3 个属性:

  1. SIGNED / UNSIGNED
    是否有符号。
    无符号的数值相减可能会存在溢出的问题,比如 SELECT col_1 - col_2 FROM tb;
    其中,col_1col_2 都是整数类型,col_1 小于 col_2
  2. ZEROFILL
    显示属性,值不做任何修改。
    INT(8) ZEROFILL 这条定义语句中:

    • 8 是修饰符,表示字段的显示宽度,而不是长度限制
    • ZEROFILL 是修饰符,表示以 0 补齐宽度

    如果该字段存储数值 1024,则显示为 00001024
    没有 ZEROFILL 这个属性,括号内的数字无意义。

  3. AUTO_INCREMENT
    自增,每张表只能有一个这样的列,且必须是索引的一部分。

尽量不要使用 UNSIGNED
自增主键建议使用 BIGINT

浮点类型

类型 占用空间 精确性
FLOAT(p) 4 bytes if 0 ≤ p ≤ 24, 8 bytes if 25 ≤ p ≤ 53
DOUBLE 8 bytes 高于 FLOAT
DECIMAL(M, D) 变长 非常高

M 表示存储的有效位数,D 表示小数点后存储的位数。

财务、金融系统必须使用 DECIMAL 类型

字符类型

类型 占用空间 最大长度
CHAR(N) N * w 255
VARCHAR(N) N * w + 1 , when N * w ≤ 255; N * w + 2 , when N * w > 255 65535(实际可用 65533)

N 表示字符数,w 由字符集决定:

  • 当字符集是 gbk 时,w=2
  • 当字符集是 utf8 时,w=3
  • 当字符集是 utf8mb4 时,w=4

VARCHAR 需要额外 1~2 个字节存储 length prefix
列中长度大于等于 768 字节的内容,会存储在 off-page
VARCHAR 字段实际长度超过 255 字节时,和 TEXT 存储机制一样

文本类型

类型 占用空间
TINYTEXT L + 1 bytes, when L < 28
TEXT L + 2 bytes, when L < 216
MEDIUMTEXT L + 3 bytes, when L < 224
LONGTEXT L + 4 bytes, when L < 232

L = N * w。

尽量不用,无法避免时使用独立子表存放
TEXT 列不能有默认值
在 TEXT 列上创建索引时,必须指定索引前缀的长度
对 TEXT 列排序时只使用该列的前 max_sort_length 个字节

集合类型

类型 占用空间 最大长度
ENUM(‘value1‘,’value2‘,…) 1 or 2 bytes 65535 个成员
SET(‘value1‘,’value2‘,…) 1, 2, 3, 4 or 8 bytes 64 个成员

利用集合类型和 sql_mode 参数,可以做简单的约束检查。

日期类型

类型 占用空间 范围
YEAR 1 byte 1970 ~ 2070, 1901 ~ 2155
DATE 3 bytes 1000-01-01 ~ 9999-12-31
TIME(fsp) 3 + [0, 1, 2, 3] bytes -838:59:59 ~ 838:59:59
DATETIME(fsp) 5 + [0, 1, 2, 3] bytes 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
TIMESTAMP(fsp) 4 + [0, 1, 2, 3] bytes 1970-01-01 00:00:00 ~ 2038-01-01 03:14:07 (UTC)

MySQL 从 5.6.4 版本开始支持微秒,其中 fsp ( fractional seconds precision ) 即是微秒的精度。

MySQL 中有如下几个日期函数:

函数名 说明
NOW() 返回 SQL 执行时的时间
CURRENT_TIMESTAMP() 与 NOW 函数相同
SYSDATE() 返回执行函数时的时间
DATE_ADD(date, INTERVAL expr unit) 增加时间
DATE_SUB(date, INTERVAL expr unit) 减少时间
DATE_FORMAT() 格式化时间显示

NOW() 与 SYSDATE() 的差别,可以使用 SELECT NOW(6),SYSDATE(6); 进行观察。

关于日期函数的应用,下面有一个简单的例子:

1
2
3
4
5
6
7
CREATE TABLE `tb_name` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
`column_name` varchar(32) NOT NULL COMMENT 'column name',
`create_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT 'create time',
`modify_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT 'modify time',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='table name';

对于这张表,前端应用只需关注 column_name 列即可,create_timemodify_time 在新记录插入时会自动填充为当前时间,当记录发生更新时,modify_time 列也会自动更新。

JSON类型

JSON ( JavaScript Object Notation ) 是一种交换文本信息的语法和非结构化数据的存储方式。

结构化数据就是关系型数据,而关系就是一张二维表。

MySQL 5.7.8 开始支持原生的 JSON 数据类型,用于替换 BLOB 类型,它有如下优点:

  • JSON 类型能进行数据有效性检查,BLOB 类型无法在数据库层做约束
  • 提升了查询性能,不再需要遍历所有字符串才能找到数据
  • 支持部分属性索引,通过虚拟列对 JSON 中的部分数据进行索引