了解并熟悉 MySQL 中的数据类型,对建表和数据库优化都非常重要。 MySQL 实现了 SQL 定义的类型,也相应的增加了 tiny, small, big 的类型。 MySQL 的数据类型主要分成三个部分:

  • Numeric Type 数值型
  • Date and Time Type 日期和时间
  • String Type 字符型

更多的内容可以在官网 查到。

整型

MySQL 数据类型 大小 范围(有符号)
TINYINT(m) 1 字节 范围 (-128~127)
SMALLINT(m) 2 个字节 范围 (-32768~32767)
MEDIUMINT(m) 3 个字节 范围 (-8388608~8388607)
INT(m) 4 个字节 范围 2^31-1(-2147483648~2147483647)
BIGINT(m) 8 个字节 范围 2^63-1(+-9.22*10 的 18 次方)

取值范围如果加了 unsigned (无符号),则最大值翻倍,如 TINYINT unsigned 的取值范围为 (0~256)。 INT(m) 里的 m 是表示 SELECT 查询结果集中的显示宽度,并不影响实际的取值范围,没有影响到显示的宽度,不知道这个 m 有什么用。

浮点型 (float 和 double)

MySQL 数据类型 大小 含义
float(m,d) 4 字节 单精度浮点型    8 位精度 (4 字节)        m 总个数,d 小数位
double(m,d) 8 字节 双精度浮点型    16 位精度 (8 字节)       m 总个数,d 小数位

设一个字段定义为 float(5,3),如果插入一个数 123.45678, 实际数据库里存的是 123.457,但总个数还以实际为准,即 6 位。

定点数

浮点型在数据库中存放的是近似值,而定点类型在数据库中存放的是精确值。 decimal(m,d) 参数 m<65 是总个数,d<30d<m 是小数位。

字符串 (char,varchar,text)

MySQL 数据类型 大小 含义
char(n) 0-255 字节 固定长度,最多 255 个字符
varchar(n) 0-65535 字节 固定长度,最多 65535 个字符
tinytext 0-255 字节 可变长度,最多 255 个字符, 255B
text 0-65535 字节 可变长度,最多 65535 个字符,64 KB
mediumtext 0-16777 215 字节 可变长度,最多 2 的 24 次方 -1 个字符,最大16MB
longtext 0-4294967295 字节 可变长度,最多 2 的 32 次方 -1 个字符,最大 4G

char 和 varchar 比较:

  1. char(n) 若存入字符数小于 n,则以空格补于其后,查询之时再将空格去掉。所以 char 类型存储的字符串末尾不能有空格,varchar 不限于此。
  2. char(n) 固定长度,char(4) 不管是存入几个字符,都将占用 4 个字节,varchar 是存入的实际字符数 +1 个字节(n<=255)或 2 个字节 (n>255),所以 varchar(4), 存入 3 个字符将占用 4 个字节。
  3. char 类型的字符串检索速度要比 varchar 类型的快。

varchar 和 text 比较:

  1. varchar 可指定 n,text 不能指定,内部存储 varchar 是存入的实际字符数 +1 个字节(n<=255)或 2 个字节 (n>255),text 是实际字符数 +2 个字节。
  2. text 类型不能有默认值。
  3. varchar 可直接创建索引,text 创建索引要指定前多少个字符。varchar 查询速度快于 text, 在都创建索引的情况下,text 的索引似乎不起作用。

char(n) 和 varchar (n)中括号中 n 代表字符的个数,并不代表字节个数,所以当使用中文的时候 (UTF8) 意味着可以插入 m 个中文,但是实际会占用 m*3个字节。

同时 char 和 varchar 最大的区别就在于 char 不管实际 value 都会占用 n 个字符的空间,而 varchar 只会占用实际字符应该占用的空间 +1,并且实际空间 +1<=n

  • 超过 char 和 varchar 的 n 设置后,字符串会被截断
  • char 的上限为 255 字节,varchar 的上限 65535 字节,text 的上限为 65535
  • char 在存储的时候会截断尾部的空格,varchar 和 text 不会
  • varchar 会使用 1-3 个字节来存储长度,text 不会
Value CHAR(4) Storage Required VARCHAR(4) Storage Required
’’ ’ ‘ 4 bytes ’’ 1 byte
‘ab’ ‘ab ‘ 4 bytes ‘ab’ 3 bytes
‘abcd’ ‘abcd’ 4 bytes ‘abcd’ 5 bytes
‘abcdefg’ ‘abcd’ 4 bytes ‘abcd’ 5 bytes

在使用 MySQL 存储字符串时经常会疑惑选择哪一种数据类型。

大字符串的选择逻辑

首先从空间方面,当 varchar 大于某些值时,会自动转换成 text,大概为

  • 大于 varchar(255)变为 tinytext
  • 大于 varchar(500)变为 text
  • 大于 varchar(20000)变为 mediumtext

所以对于大内容 varchar 和 text 并没有太多区别。

其次从性能方面,索引是影响性能最关键的因素,对于 text 来说,只能添加前缀索引,并且索引最大只能 1000 字节。而 varchar 即使超过 1000 字节长,也会被截断。

二进制数据 (Blob)

  1. BLOB 和 TEXT 存储方式不同,TEXT 以文本方式存储,英文存储区分大小写,而 Blob 是以二进制方式存储,不分大小写。
  2. BLOB 存储的数据只能整体读出。
  3. TEXT 可以指定字符集,BLOB 不用指定字符集。
数据类型 大小 用途
TINYBLOB 0~255 字节 不超过 255 个字符二进制字符串
BLOB 0~65535 字节 二进制
MEDIUMBLOB 0-16 777 215 字节 二进制形式的中等长度文本数据
LONGBLOB 0-4 294 967 295 字节 二进制形式的极大文本数据

日期和时间类型

MySQL 数据类型 大小 范围 含义
date 3 字节 1000-01-01/9999-12-31 日期 ‘2008-12-2’
time 3 字节 ‘-838:59:59’/’838:59:59’ 时间 ‘12:25:36’
datetime 8 字节 1000-01-01 00:00:00/9999-12-31 23:59:59 日期时间 ‘2008-12-2 22:06:44’
timestamp 4 字节 1970-01-01 00:00:00/2037 年某时 自动存储记录修改时间

每个时间类型有一个有效值范围和一个”零”值,当指定不合法的 MySQL 不能表示的值时使用”零”值。若定义一个字段为 timestamp,这个字段里的时间数据会随其他字段修改的时候自动刷新,所以这个数据类型的字段可以存放这条记录最后被修改的时间。

Datetime 和 timestamp 的区别

区别 datetime timestamp
空间 8 字节 4 字节
是否允许空值 允许 允许
是否可以自定义值 可以 不可以
支持时间范围 1000-01-01 00:00:00/9999-12-31 23:59:59 不能早于 1970 或者晚于 2037 年
是否与时区相关 无关 值以 UTC 格式保存,存储 milliseconds,需要存储或者取出时手动转换时区
默认值 可以在指定 datetime 字段的值的时候使用 now() 变量来自动插入系统的当前时间 默认值为 CURRENT_TIMESTAMP() ,当前系统时间
结论 类型适合用来记录数据的原始创建时间,无论如何更改记录中其他字段, datetime 都不会改变,除非手动改变 数据库会自动修改其值,任何修改记录都会被更新,如果需要不设置自动更新,通过设置 DEFAULT CURRENT_TIMESTAMP 可实现。timestamp 类型适合用来记录数据最后修改时间。

数据类型的属性

MySQL 关键字 含义
NULL 数据列可包含 NULL 值
NOT NULL 数据列不允许包含 NULL 值
DEFAULT 默认值
PRIMARY KEY 主键
AUTO_INCREMENT 自动递增,适用于整数类型
UNSIGNED 无符号
CHARACTER SET name 指定一个字符集