MySQL 数据类型


# MySQL 数据类型

MySQL 中的数据类型在使用时可以划分为:数值类型、时间类型、字符串类型、枚举与集合类型。

# 数值类型

有整数和浮点数两种,再根据字节来区分:字节一般是 8 位的二进制,如果需要带符号,就会占用第一位来表示符号,用 0 表示整数,1 表示负数。

整数有 5 种:

类型 字节长度 范围(有符号) 范围(无符号) 用途
TINYINT 1 [-128, 127] [0 , 255] 小整数值
SMALLINT 2 [-32768 , 32767] [0 , 65535] 大整数值
MEDIUMINT 3 [-223 , 223 - 1] [0 , 224 - 1] 大整数值
INT 或 INTEGER 4 [-231 , 231 - 1] [0 , 232 - 1] 大整数值
BIGINT 8 [-263 , 263 - 1] [0 , 264 - 1] 极大整数值

在 MySQL 中定义的 BOOL 或 BOOLEAN 类型,最终会转换成 TINYINT(1) 类型,用 1 代表 TRUE,· 代表 FALSE

浮点型有 2 种:

类型 字节长度 范围(有符号) 范围(无符号) 用途
FLOAT 4 (-3.402823466 * 1038 ,
-1.175494351 * 10-38)
0
(1.175494351 * 10-38 ,
3.402823466351 1038)
0
(1.175494351 * 10-38 ,
3.402823466 * 1038)
单精度
浮点数值
DOUBLE 8 (-1.7976931348623157 * 10308 ,
-2.2250738585072014 * 10-308)
0
(2.2250738585072014 * 10-308 ,
1.7976931348623157 * 10308)
0
(2.2250738585072014 * 10-308 ,
1.7976931348623157 * 10308)
双精度
浮点数值
DECIMAL 对 DECIMAL(M, D),
如果 M>D,为 M+2,
否则为 D+2
依赖于 M 和 D 的值 依赖于 M 和 D 的值 小数值

针对于 DECIMAL 这种比较特殊的类型,有如下的规则:

  • M 是表示有效数字数的精度。M 范围为 1 ~ 65
  • D 是表示小数点后的位数。D 的范围是 0 ~ 30
  • MySQL 要求 D <= M

DECIMAL 主要用于一些需要保存定长小数的项。比如,我们存储的值要求保存数位小数。

# 时间类型

常规使用有 3 种,日期(Date),时间(TIME),日期+时间组合(DATETIME)。

较少使用的 2 种:年份(YEAR),时间戳(TIMESTAMP)。

他们各自的区别整理如下:

类型 字节长度 范围 格式 用途
DATE 3 1000-01-01
~
9999-12-31
YYYY-MM-DD 日期值
TIME 3 -838:59:59
~
838:59:59
HH:MM:SS 时间值或持续时间
DATETIME 8 1000-01-01 00:00:00
~
9999-12-31 23:59:59
YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4 1970-01-01 00:00:00
~
2038-1-19 11:14:07
YYYY-MM-DD HH:MM:SS 混合日期和时间值,时间戳
YEAR 1 1901 ~ 2155 YYYY 年份值

若定义一个字段为 TIMESTAMP,这个字段里的时间数据会随其他字段修改的时候自动刷新,所以这个数据类型的字段可以存放这条记录最后被修改的时间。

# 字符串类型

常规的字符串类型有 CHAR,VARCHAR

文本类型有 TINYTEXT,TEXT,MEDIUMTEXT,LONGTEXT

二进制星星的文本类型有 TINYBLOB,BLOB,MEDIUMBLOB,LONGBLOB

他们的区别如下:

类型 字节长度 特性 用途
CHAR 0 ~ 255 固定长度 定长字符串
VARCHAR 0 ~ 65535 固定长度 变长字符串
TINYTEXT 0 ~ 255 可变长度,大小写不敏感 短文本字符串
TEXT 0 ~ 65535 可变长度,大小写不敏感 长文本数据
MEDIUMTEXT 0 ~ 16777215 可变长度,大小写不敏感 中等长度文本数据
LONGTEXT 0 ~ 4294967295 可变长度,大小写不敏感 极大文本数据
TINYBLOB 0 ~ 255 大小写敏感 不超过 255 个字符的二进制字符串
BLOB 0 ~ 65535 大小写敏感 二进制形式的长文本数据
MEDIUMBLOB 0 ~ 16777215 大小写敏感 二进制形式的中等长度文本数据
LONGBLOB 0 ~ 4294967295 大小写敏感 二进制形式的极大文本数据

其中 char 和 varchar 是最常用的类型:

  • 使用 char(n) 声明的字段,数据库就一定会占用这么长字节的位置,即使实际字符串只有寥寥数位。好处是会方便 MySQL 进行数据检索,不会产生碎片空间,所以推荐对可预见长度的字符类型使用 char 来定义,比如身份证、电话、姓名、银行卡、物料编码等等。
  • 使用 varchar(n) 声明的字段,默认情况下不会有空间浪费的情况。比如定义字段可用 10 位,实际数据仅使用 1 位,MySQL 内部也只用 1 位,不会去占用另外的 9 位。好处是对空间的利用率较高,但若数据不断变化调整,MySQL 内部就需要将数据转移到新的空间,因为原本并没有占用预留的空间。如此往复,空间的碎片率就会非常高。
  • 总结起来
    • 定长字符类型建议用 char。
    • 不定长并且不怎么变化的数据类型,建议用 varchar。
    • 如果数据改动频繁,长度又是参差不齐,要么空间换时间用 char,要么时间换空间用 varchar。

在 MySQL 中,一个英文字母占 1 字节,一个中文汉字所占的字节数与编码格式有关:

  • 如果是 GBK 编码,则一个中文汉字占 2 个字节。
  • 如果是 UTF8 编码,则一个中文汉字占 3 个字节。

# 枚举与集合类型

# 枚举

枚举(ENUM)是单选字符串数据类型,适合存储表单界面中的「单选值」。

设定 ENUM 的时候,需要给定「固定的几个选项」,存储的时候就只存储其中的一个值。

设定 ENUM 的格式:

enum("选项1","选项2","选项3",...);
1

实际上,ENUM 的选项都会对应一个数字,依次是 1,2,3,4,5...,最多有 65535 个选项。

使用的时候,可以使用选项的字符串格式,也可以使用对应的数字。

# 集合

集合(SET)是多选字符串数据类型,适合存储表单界面的「多选值」。

设定 SET 的时候,同样需要给定「固定的几个选项」,存储的时候,可以存储其中的若干个值。

设定 SET 的格式:

set("选项1","选项2","选项3",...)
1

同样的,SET 的每个选项值也对应一个数字,依次是 1,2,4,8,16...,最多有 64 个选项。

使用的时候,可以使用 SET 选项的字符串本身(多个选项用逗号分隔),也可以使用多个选项的数字之和(比如:1+2+4=7)。

# 数据类型的属性

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

(完)