CODING 修炼小册

MySQL 数据类型

# MySQL 数据类型

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

# 数值类型

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

很显然，使用的字节数越多，意味着能表示的数值范围就越大，但是也就越耗费存储空间。

# 整数类型

整数有 5 种：

类型 占用存储空间 无符号数取值范围 有符号数取值范围 用途
TINYINT 1 字节 0 ~ 2⁸-1 -2⁷ ~ 2⁷-1 非常小的整数
SMALLINT 2 字节 0 ~ 2¹⁶-1 -2¹⁵ ~ 2¹⁵-1 小的整数
MEDIUMINT 3 字节 0 ~ 2²⁴-1 -2²³ ~ 2²³-1 中等大小的整数
INT 或 INTEGER 4 字节 0 ~ 2³²-1 -2³¹ ~ 2³¹-1 标准的整数
BIGINT 8 字节 0 ~ 2⁶⁴-1 -2⁶³ ~ 2⁶³-1 大整数

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

# 浮点数类型

浮点数有 2 种：

类型 占用存储空间 无符号数取值范围 有符号数取值范围 用途
FLOAT 4 字节 0
(1.175494351E-38,
3.402823466E+38)		 (-3.402823466E+38,
-1.175494351E-38)
0
(1.175494351E-38,
3.402823466E+38)		 单精度浮点数
DOUBLE 8 字节 0
(2.2250738585072014E-308,
1.7976931348623157E+308)		 (-1.7976931348623157E+308,
-2.2250738585072014E-308)
0
(2.2250738585072014E-308,
1.7976931348623157E+308)		 双精度浮点数

# 浮点数的不精确性

在计算机中十进制最终是被转成二进制来存储的，比如十进制小数 9.875 转换成二进制小数的话就是：1001.111。

但虽然有的十进制小数，比如 1.875 可以被很容易的转换成二进制数 1.111，但是更多的小数是无法直接转换成二进制的，比如说 0.3，它转换成的二进制小数就是一个无限小数，但是我们现在只能用 4 个字节或者 8 个字节来表示这个小数，所以只能进行一些舍入来近似的表示，所以我们说计算机的浮点数表示有时是不精确的。

# 设置最大位数和小数位数

在定义浮点数类型时，还可以在 FLOAT 或者 DOUBLE 后边跟上两个参数，就像这样：

FLOAT(M, D)
DOUBLE(M, D)
1
2

对于我们用户而言，使用的都是十进制小数。如果我们事先知道表中的某个列要存储的小数在一定范围内，我们可以使用 FLOAT(M, D) 或者 DOUBLE(M, D) 来限制可以存储到本列中的小数范围。其中：

  • M 表示该小数最多需要的十进制有效数字个数。
    • 注意是有效数字个数，对于小数 -2.3 来说有效数字个数就是 2，对于小数 0.9 来说有效数字个数就是 1。
  • D 表示该小数的小数点后的十进制数字个数。
    • 小数点后有几个十进制数字，D 的值就是什么。

例如下面这样设置了 M 和 D 的单精度浮点数的取值范围：

类型 取值范围
FLOAT(4, 1) -999.9 ~ 999.9
FLOAT(5, 1) -9999.9 ~ 9999.9
FLOAT(6, 1) -99999.9 ~ 99999.9
FLOAT(4, 0) -9999 ~ 9999
FLOAT(4, 1) -999.9 ~ 999.9
FLOAT(4, 2) -99.99 ~ 99.99

当然，M 和 D 的取值也不是无限大的：

  • M 的取值范围是 1 ~ 255
  • D 的取值范围是 0 ~ 30
  • MySQL 要求 D <= M。

M 和 D 都是可选的，如果我们省略了它们，那它们的值按照机器支持的最大值来存储。

# 定点数类型

因为用浮点数表示小数可能会有不精确的情况，在一些情况下我们必须保证小数是精确的，所以有了定点数类型，它也是存储小数的一种方式：

类型 占用存储空间 取值范围 用途
DECIMAL(M, D) 取决于 M 和 D 取决于 M 和 D 一些需要保存定长小数的项，比如存储的值要求保存数位小数

其中 M 和 D 的含义与浮点数中的含义一样，但取值范围略有不一样：

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

# 为什么能保证小数精确

定点数类型的 M 和 D 的含义与浮点数中的含义一样，但是占用存储空间不一样：

  • 单精度浮点数类型 FLOAT(M, D) 占用的字节数一直都是 4 字节
  • 双精度浮点数类型 DOUBLE(M, D) 占用的字节数一直都是 8 字节
  • 定点数类型 DECIMAL(M, D) 占用的字节数随着 M 和 D 的值的变动而变动

这是因为定点数作为一种精确的小数，为了达到精确的目的就不能把它转换成二进制小数之后再存储（因为有很多十进制小数转为二进制小数后需要进行舍入操作，导致二进制小数表示的数值是不精确的）。

那么它是怎么存储的呢？大致的思路就是把小数点左右的两个十进制整数给存储起来，比如对于十进制小数 2.38 来说，可以把这个小数的小数点左右的两个整数，也就是 2 和 38 分别保存起来。当然事情并没有这么简单，具体的实现原理这里就不展开研究了。

# 无符号数值类型的表示

对于数值类型，有些情况下我们只需要用到无符号数（就是非负数），定义时就是在原数值类型后加一个单词 UNSIGNED：

数值类型 UNSIGNED
1

例如：

INT UNSIGNED      # 表示无符号整数
FLOAT UNSIGNED    # 表示无符号浮点数
DECIMAL UNSIGNED  # 表示无符号定点数
1
2
3

小贴士

在使用的存储空间大小相同的情况下，无符号整数可以表示的正整数范围比有符号整数能表示的正整数范围大一倍。不过受浮点数和定点数具体的存储格式影响，无符号浮点数和定点数并不能提升正数的表示范围。

# 日期和时间类型

MySQL 提供了多种关于时间和日期的类型，各种类型能表示的范围如下：

类型 占用存储空间 范围 格式 用途
YEAR 1 字节 1901 ~ 2155 YYYY 年份值
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 时间戳

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

# 字符串类型

# 字符编码

在具体分析 MySQL 中各个字符串类型之前，一定要先搞明白字符和字节的区别。字符是面向人的概念，字节是面向计算机的概念。如果你想在计算机中表示字符，那就需要将该字符与一个特定的字节序列对应起来，这个映射过程称之为编码。不幸的是，这种映射关系并不是唯一的，不同的人制作了不同的编码方案，根据表示一个字符使用的字节数量是不是固定的，编码方案可以分为下边两种：

  • 固定长度的编码方案
    表示不同的字符所需要的字节数量是相同的。比如 ASCII 编码方案采用 1 个字节来编码一个字符，ucs2 采用 2 个字节来编码一个字符。
  • 变长的编码方案
    表示不同的字符所需要的字节数量是不同的。比比如 utf8 编码方案采用 1~3 个字节来编码一个字符，gb2312 采用 1~2 个字节来编码一个字符。

对于不同的字符编码方案来说，同一个字符可能被编码成不同的字节序列。比如同样一个字符：我，在 utf8 和 gb2312 这两种编码方案下被映射成如下的字节序列：

# utf8 编码方案，将字符 '我' 编码成：
111001101000100010010001
# 共占用 3 个字节，用十六进制表示就是：0xE68891

# gb2312 编码方案，将字符 '我' 编码成：
1100111011010010
# 共占用 2 个字节，用十六进制表示就是：0xCED2
1
2
3
4
5
6
7

另外，设计 MySQL 的人最初对编码方案和字符集这两个概念并没做明确区分，也就是说我们之后所讲的 utf8 字符集指的就是 utf8 编码方案，gb2312 字符集指的也就是 gb2312 编码方案。

小贴士

正宗的 utf8 字符集是使用 1~4 个字节来编码一个字符的，不过 MySQL 中对 utf8 字符集做了阉割，编码一个字符最多使用 3 个字节。

如果我们之后有存储使用 4 个字节来编码的字符的情景，可以使用一种称之为 utf8mb4 的字符集，它才是正宗的 utf8 字符集（现在一般都是用这种）。

# 常用类型

以下是 MySQL 中提供的各种字符串类型：

类型 最大长度 占用存储空间 特性 用途
CHAR(M) M 个字符 M×W 个字节 固定长度 固定长度的字符串
VARCHAR(M) M 个字符 L+1 或 L+2 个字节 固定长度 可变长度的字符串
TINYTEXT 2⁸-1 个字节 L+1 个字节 可变长度，大小写不敏感 非常小型的字符串
TEXT 2¹⁶-1 个字节 L+2 个字节 可变长度，大小写不敏感 小型的字符串
MEDIUMTEXT 2²⁴-1 个字节 L+3 个字节 可变长度，大小写不敏感 中等大小的字符串
LONGTEXT 2³²-1 个字节 L+4 个字节 可变长度，大小写不敏感 大型的字符串

（注：其中 M 代表该数据类型最多能存储的字符数量，L 代表实际存储的字符串在特定字符集下所占的字节数，W 代表在该特定字符集下，编码一个字符最多需要的字节数）

小贴士

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

  • 如果是 gbk 编码，则一个中文汉字占 2 个字节。
  • 如果是 utf8 编码，则一个中文汉字占 3 个字节。

# CHAR(M)

CHAR(M) 中的 M 代表该类型最多可以存储的字符数量，它的取值范围是 0~255。

  • 如果省略 M，它的默认值就是 1。
  • CHAR(0) 是一种特别的类型，它只能存储空字符串 '' 或者 NULL 值。

因为 CHAR 是固定长度的，所以如果实际存储的字符串在特定字符集编码下占用的字节数不足 M×W，那么剩余的那些存储空间用空格字符（' '）补齐。

这也就是说：一旦确定了 CHAR(M) 类型的 M 的值，如果 M 的值很大，而实际存储的字符串占用字节数又很少，会造成存储空间的浪费。

# VARCHAR(M)

如果表中某个列需要存储字符串类型的数据，而且这些字符串长短不一，使用 CHAR(M) 可能会浪费很多存储空间，VARCHAR(M) 会是一个更好的选择。

VARCHAR(M) 中的 M 也是代表该类型最多可以存储的字符数量，理论上的取值范围是 1~65535。但是 MySQL 中还有一个规定，表中某一行包含的所有列中存储的数据大小总共不得超过 65535 个字节（注意是字节），也就是说 VARCHAR(M) 类型实际能够容纳的字符数量是小于 65535 的。

# 各种 TEXT 类型

如果 VARCHAR(M) 的长度开始不够用，那么可以考虑另外几种 TEXT 类型，它们也都是变长类型：

  • TINYTEXT 最多可以存储 2⁸-1 个字节。
  • TEXT 最多可以存储 2¹⁶-1 个字节。
  • MEDIUMTEXT 最多可以存储 2²⁴-1 个字节。
  • LONGTEXT 最多可以存储 2³²-1 个字节。

前面提到某一行包含的所有列中存储的数据大小总和不得超过 65535 个字节么，这个规定对这些 TEXT 类型是不起作用的，它们并不在这个规定的限制范围之内。一个表中如果有的属性需要存储特别长的文本的话，就可以考虑使用这几个类型了。

# ENUM 类型和 SET 类型

ENUM 和 SET 类型都是一种特殊的字符串类型，在从字符串列表中单选或多选元素的时候会用得到它们。

# 枚举

枚举（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）。

# 二进制类型

# BIT 类型

有时候我们有存储单个或者多个比特位的需求，此时就可以用到这种类型：

类型 字节数 用途
BIT(M) 近似为(M+7)/8 存储 M 个比特位的值

其中 M 的取值范围为 1~64，而且 M 可以省略，它的默认值为 1，也就是说 BIT(1) 和 BIT 的意思是一样的。

MySQL 是以字节为单位存储数据的，一个字节拥有 8 个比特位。如果我们想存储的比特位个数不足整数个字节，那么 MySQL 会偷偷的填充满，比如：

  • BIT(1) 类型仅仅需要存储 1 个比特位的数据，但是 MySQL 会为其申请 (1+7)/8 = 1 个字节。
  • BIT(5) 类型仅仅需要存储 5 个比特位的数据，但是 MySQL 会为其申请 (5+7)/8 = 1 个字节。
  • BIT(9) 类型仅仅需要存储 9 个比特位的数据，但是 MySQL 会为其申请 (9+7)/8 = 2 个字节。

# BINARY(M) 与 VARBINARY(M)

BINARY(M) 和 VARBINARY(M) 对应于前面提到的 CHAR(M) 和 VARCHAR(M)，都是前者是固定长度的类型，后者是可变长度的类型，只不过：

  • BINARY(M) 和VARBINARY(M) 是用来存放字节的，其中的 M 代表该类型最多能存放的字节数量。
  • CHAR(M) 和 VARCHAR(M) 是用来存储字符的，其中的 M 代表该类型最多能存放的字符数量。

# 其他的二进制类型

TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB 是针对数据量很大的二进制数据提出的，比如图片、音频、压缩文件啥的。它们很像 TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT，不过各种 BLOB 类型是用来存储字节的，而各种 TEXT 类型是用来存储字符的而已。

小贴士

对于比较大的二进制数据，比如图片、音频、压缩文件等，通常情况下都不直接存储到数据库管理系统中，而是将它们保存到文件系统中，然后在数据库中之存放一个文件路径即可。

# 数据类型的属性

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

（完）

MySQL 常用命令