MySQL数据类型解析及设计库表注意点

作者: 程序猿蛋蛋哥 | 来源:发表于2019-04-28 20:40 被阅读44次

很多时候，我们开发应用系统，底层的数据库表结构都需要开发人员亲自设计，设计的合理与否，关乎着整个系统的稳定性和运行速率，系统上线后再频繁地对后端数据库表结构进行修改更是大忌。所以我们需要更多地储备数据库层面的知识和技能，以便最初就能设计好表结构。

本篇基于MySQL数据库，尽可能详细地为大家解析MySQL基本的数据类型和设计库表结构时应注意的点。

整数类型：

MySQL整数类型有5个：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT

整数类型	存储空间(位)	存储值范围	范围计算公式
TINYINT	8（1字节）	-128 ~ 127	$-2^{8-1} 到 2^{8-1}-1$
SMALLINT	16（2字节）	-32768 ~ 32767	$-2^{16-1} 到 2^{16-1}-1$
MEDIUMINT	24（3字节）	-8388608 ~ 8388607	$-2^{24-1} 到 2^{24-1}-1$
INT	32（4字节）	-2147483648 ~ 2147483647	$-2^{32-1} 到 2^{32-1}-1$
BIGINT	64（8字节）	-(9.223372e+18) ~ 9.223372e+18	$-2^{64-1} 到 2^{64-1}-1$

范围计算公式为： $-2^{N-1} 到 2^{N-1}-1$

整数类型有可选的UNSIGNED属性：表示非负值，可使正数的范围扩大一倍，例如：TINYINT的存储范围是：-128 ~ 127，TINYINT UNSIGNED的存储范围是：0 ~ 255。

【疑问】：
定义数据库表字段为整数类型，例如INT，那么INT(1)，INT(11)，INT(20)...等等，有什么意义和区别吗？应该怎么定义？

【解答】：
定义数据库表字段时，为整数类型指定宽度，例如INT(11)，INT(20)，对大多数应用没有意义，不会限制所对应存储值范围，对于存储和计算来说，INT(11)和INT(20)是相同的，没有区别。

INT(1)，INT(11)，INT(20)...存储有符号范围始终都是：-2147483648 ~ 2147483647，无符号范围：0 ~ 4294967295
TINYINT(1)，TINYINT(2)，TINYINT(4)...存储有符号范围始终都是：-128 ~ 127，无符号范围：0 ~ 255

那指定整数类型宽度用来干什么呢？如 ITN(4)

只是一些MySQL客户端工具用来显示字符的个数。

举例：如下goods表结构

desc goods

如果num字段(或type字段)存储的值的字符数超过了所定义的宽度4，那么该值会显示全吗？

字段值字符数超过指定类型宽度

一般用法是：指定整数类型宽度的同时，定义该字段Zero Fill属性（意思是达不到指定宽度时用0填充）

类型宽度+zerofill

那么在设计数据库表字段，并定义为整数类型时，应注意：【重点，敲黑板】

一般不用指定整数类型宽度，如ITN(20)，TINYINT(2)...等，除非设定Zero Fill属性，用来填充字符个数。

字段不会出现负值时，将其设定为unsigned，能扩大正数的存储范围，同时也能免去负值带来的干扰。

整型比字符操作代价更低，因为字符集和校对规则(排序规则)使字符比较比整型比较更复杂。所以定义字段类型时，先考虑整型。

表的标识列最好选择整数类型，因为很快并且可以使用AUTO_INCREMENT（自增）。

阿里巴巴Java开发手册中，建表规约 - 第9条，对设计数据库表强制要求如下：

阿里开发-建表规约-9

实数类型：

实数是带有小数部分的数字。

MySQL的实数类型有3个：FLOAT，DOUBLE，DECIMAL

对比如下：

实数类型	存储空间(字节)	数值计算	使用
FLOAT	4字节	CPU支持原生浮点计算因此使用PC标准的浮点运算进行近似计算	单精度，存小数时存在精度损失
DOUBLE	8字节	CPU支持原生浮点计算因此使用PC标准的浮点运算进行近似计算	双精度(比单的精度更高，范围更大) 存小数时也存在精度损失
DECIMAL	65个数字 (MySQL>=5.0版本)	CPU不支持对DECIMAL直接计算 MySQL(>=5.0版本)服务器自己实现了DECIMAL的高精度计算	对小数进行精确计算时使用，如财务数据

说明：DECIMAL比DOUBLE，FLOAT存储空间大，存储值范围大，精度高，但是他俩比DECIMAL计算更快（FLOAT/DOUBLE使用CPU原生浮点运算，DECIMAL使用MySQL(>=5.0版本)自身实现的DECIMAL高精度计算）。

那么在设计数据库表字段，并定义为实数类型时，应注意：【重点，敲黑板】

尽量只在只在对小数进行精确计算时，使用DECIMAL类型，例如财务数据。

只指定数据类型，不指定精度（MySQL中FLOAT/DOUBLE/DECIMAL都可以指定精度，但会影响列的空间消耗）。

使用DECIMAL存储，但数据量很大(上千万条或更多)，可以考虑使用BIGINT代替DECIMAL，把小数乘以相应倍数存在BIGINT中，这样可以同时避免：浮点存储计算不精确和DECIMAL精确计算代价高的问题。

使用DECIMAL存储，但存储的数据范围超过了DECIMAL的范围，建议将数据拆成整数和小数分开存储。

阿里Java开发手册，建表规约 - 第6条，对设计数据库表强制要求如下：

阿里开发-建表规约-6

字符串类型

一、VARCHAR 类型

关于VARCHAR类型我会从以下三个方面详细解析：

存储空间
性能
库表设计注意点

【存储空间】

VARCHAR类型是最常见的字符串数据类型，用于存储可变长字符串，一般比定长类型更节省空间，原因：VARCHAR存储时仅使用必要的空间，越短的字符串使用越少的空间。

定义库表的字段类型时，VARCHAR(100)表示什么？

VARCHAR(100)表示：能存储100个字符，注意字符数是100个，不管你存储的是汉字，符号，字母等等，它的总个数不能超过100个。

VARCHAR(100)中的 100 不表示存储空间的大小，那么存储空间大小怎么计算呢？

<1> 首先VARCHAR需要使用 1 或 2 个额外字节记录字符串的长度，这里的字符串长度是指占用的空间大小，单位是字节。
    如果存储的数据最大长度(空间大小) <= 255字节，则只额外使用 1 个字节记录长度(1个字节8位最大表示的无符号数就是255),
    否则使用 2 个字节记录。
    
<2> 存储的数据是多大字节？这就跟我们具体存什么东西以及编码相关了，比如：
    utf-8编码(小于5.5.3版本)：
        存汉字：一个汉字 = 3个字节
        存英文：一个字母 = 1个字节
    gbk编码：
        存汉字：一个汉字 = 2个字节
        存英文：一个字母 = 1个字节
    utf8bm4字符集（大于5.5.3版本，注意是字符集，后面详细讨论）：
        存普通常用汉字：一个汉字 = 3个字节  (普通汉字：包含在Basic Multiling Plane(BMP)字符中的汉字)
        不常用汉字：一个汉字 = 4个字节
        常用的表情字符emoji：一个表情字符emoji = 4个字节
        存英文：一个字母 = 1个字节

关于MySQL的utf8和utf8bm4之间的爱恨情仇，我会用一遍文章为大家尽可能详细的解析，请关注，这里只简单介绍：

MySQL在5.5.3版本之前使用uft8字符集，MySQL的 'utf8' 实际上不是真正的UTF-8。(可认为是MySQL的一个BUG)

标准的 UTF-8 字符集编码可以用 1 ~ 4 个字节去编码 21 位字符，几乎包含了世界上所能看到的语言了，但是MySQL的'utf8'支持的字符最大长度是 3 个字节，而标准的 UTF-8 >支持的字符最大长度是 4 个字节。

也就是说：MySQL的'utf8'只支持到了标准的 UTF-8 字符集中的部分，即基本多文本平面(Basic Multiling Plane(BMP))，包含了控制符，拉丁文，中，日，韩等绝大多数国际字符，但并不是所有，不支持：手机端常用的表情字符emoji和一些不常用的汉字，这些需要4个字节才能编码出来。

在2010年，MySQL在5.5.3版本后增加了utf8mb4 字符编码，mb4：most bytes 4(最多4字节)，用来兼容之前MySQL-'utf8'不支持的四字节字符，是它的超集。

现在MySQL版本都到8以上了，所以一般在设计数据库表时，默认都使用utf8mb4字符集

实战举例：【实战很重要，敲黑板~】

<1> 基于命令行（黑窗口）的MySQL客户端

先查数据库版本：看数据库版本号基本就知道设计库表时，默认使用的字符集了

查数据库版本

查一下使用数据库的各个字符集设置：可以看出客户端来源数据和查询结果的字符集都为gbk，即字符长度：一个汉字 = 2字节，一个英文字母 = 1字节

查使用数据库字符集设置
验证一下：

select LENGTH('XXX'); -- 返回字符串'XXX'的存储长度，以字节为单位。
select CHAR_LENGTH('XXX'); -- 返回字符串'XXX'的字符数，即'XXX'含了多少个汉字，字母，符号等等，加起来共多少个字符。
select CHARACTER_LENGTH('XXX'); -- 返回字符串'XXX'的字符数。

查字符串存储长度

查字符串字符数

以school表为例：表的默认字符集和各个列的字符集都是utf8bm4，headmaster(校长)列数据类型为varchar(20)，最多存储字符数是20个，存储占空间长度知道一下就行。

查school表DDL

school表各个列字符集

school表数据

<2> MySQL Workbench客户端

数据库字符集

字符串存储空间长度

【性能】

VARCHAR节省了存储空间，对性能有帮助。

update操作时可能使行变得比原来更长，因为行是变长的，这就导致需要做额外工作，则影响性能。

如果一个行占用的空间增长，且在页内没有更多的空间可以存储：
InnoDB：需要分裂页来使行可以放进页内
MyISAM：将行拆成不同的片段存储

【库表设计注意点】

一般情况下，数据库的表/字段，请使用utf8bm4编码的字符集。

字符串列的最大长度比平均长度大很多，列更新很少，适合用VARCHAR类型

阿里Java开发手册，建表规约 - 第8条，对设计数据库表强制要求如下：

阿里开发-建表规约-8

二、CHAR 类型

我们也从以下三个方面解析：

存储空间
性能
库表设计注意点

【存储空间】

记住两点即可：

<1> CHAR类型是定长的，CHAR(10)表示可以存储10个字符，MySQL根据定义的字符数分配足够的存储空间。

对比一下CHAR(1) 与 VARCHAR(1) 存储空间: 
存储单字节字符(如一个英文字母)：
CHAR(1) : 1个字节
VARCHAR(1) : 1个字节 + 额外记录长度(占用空间大小)的1字节 = 2字节

<2> 存储CHAR值时，MySQL会删除所有的末尾空格，不会删除前面和中间的空格；存储VARCHAR值时(MySQL>=5.0版本)，则会保留末尾空格。

举例：定义school表的code（学校代号）字段为CHAR(10)

desc school

为“西安电子科技大学”和“明尼苏达大学双城分校”分别设定code值：

update school set code = '  985_0011' where name = '西安电子科技大学'; （code前面有两个空格）
update school set code = '010_0039    ' where name = '明尼苏达大学双城分校'; （code末尾有四个空格）

char末尾空格被删掉

对比看一下VARCHAR(10)：（注意：MySQL>=5.0版本时VARCHAR会保留末尾空格，之前版本也会删除末尾空格）

create table v_test(v_col varchar(10));
insert into v_test(v_col) values('string1'), ('  string2'), ('string3  '); （string2前面有两个空格，string3末尾有两个空格）

varchar保留末尾空格

【性能】

非常短的列：CHAR 比 VARCHAR 在存储空间上更有效率。

经常变更的列：CHAR 不易产生碎片，比 VARCHAR 更好。

【库表设计注意点】

很短的字符串，或者所有字符串都接近同一个长度（如密码的MD5值是定长），适合定义为CHAR类型。

阿里Java开发手册，建表规约 - 第7条，对设计数据库表强制要求如下：

阿里开发-建表规约-7

三、BLOB 和 TEXT 类型

BLOB 和 TEXT 都是用来存储很大的数据，BLOB采用二进制存储，TEXT采用字符方式存储。

MySQL把每个 BLOB 和 TEXT值当作一个独立的对象处理，存储引擎在存储时通常会做特殊处理。

怎么个特殊处理呢？
当BLOB和TEXT值太大时，InnoDB会使用专门的“外部”存储区域来进行存储：每个值在表行内用1 ~ 4字节存储一个指针，指向外部存储区域存储的实际值。

对比：

BLOB: 存二进制数据，没有排序规则或字符集
TEXT：存字符串，有排序规则和字符集，只对每列的最前max_sort_length字节而不是整个字符串做排序。
若只需要排序前面一小部分字符，可以减小max_sort_length的配置，或使用ORDER BY SUSTRING(column, length)。

max_sort_length

四、枚举

MySQL中使用ENUM('xxx1','xxx2','xxx3')定义字段为枚举列。

我们从以下四个方面解析：

存储结构
存储空间
排序
添加/删除/更改枚举字符串

【存储结构】

枚举列是把不重复的字符串存储成一个预定义的集合，如ENUM('fish','dog','pig')，在MySQL内部会将每个值在列表中的位置保存为整数，并在表的.frm文件中保存 “数字 - 字符串” 映射关系的 “查找表”。

举例：

新建表：create table enum_test(e_col ENUM('fish','dog','pig') NOT NULL); （e_col列为枚举）
插入数据：insert into enum_test(e_col) values('fish'),('dog'),('pig'); （只能插枚举列表中的值，否则报错）

ENUM存储结构

【存储空间】

MySQL在存储枚举时非常紧凑，根据列表值的数量（注意是数量）压缩到一个或两个字节中。

【排序】

枚举字段按照内部存储的整数而不是定义的字符串进行排序。

枚举排序
如果需要按照定义的字符串排序，怎么做呢？ 使用FIELD()函数

使用field函数指定排序
所以，建议在定义枚举列表值时就按照字母的顺序。

【添加/删除/更改枚举字符串】

必须使用ALTER TABLE命令：

alter table enum_test change column e_col e_col ENUM('fish', 'dog') NOT NULL;
alter table enum_test change column e_col e_col ENUM('fish', 'dog', 'monkey') NOT NULL;

添加/删除/更改枚举字符串

日期和时间类型

MySQL提供了两种相似的日期类型：DATETIME 和 TIMESTAMP

我们从以下三个方面对比这两种日期类型：

时间范围
存储空间
显示格式

【时间范围】

DATETIME：能保存大范围的值，从1001年到9999年，精度为秒。

TIMESTAMP：保存了从1970年1月1日零点(格林尼治标准时间)以来的秒数，它和UNIX时间戳相同，只能表示的范围是：1970年 ~ 2038年。

【存储空间】

DATETIME：把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关，使用8个字节的存储空间。

TIMESTAMP：使用4字节的存储空间。

【显示格式】

DATETIME：2008-01-16 22:37:08(MySQL默认情况下)

TIMESTAMP：2008-01-16 22:37:08(MySQL>=4.1版本，按照DATETIME方式格式化)，显示的值依赖时区。

怎么个依赖时区呢？
举例：TIMESTAMP类型字段存储值为0，则在美国东部时区显示为：1969-12-31 19:00:00(不是1970-01-01 00:00:00)，因为与格林尼治时间差了5个小时。

如果在多个时区存储或访问数据，TIMESTAMP提供的值与时区有关系，DATETIME则保留文本表示的日期和时间，跟时区无关，所以它们很不一样。

那么在设计数据库表字段，并定义为日期和时间类型时，应注意：【重点，敲黑板~】

除特殊之外，一般情况尽量使用TIMESTAMP，因为比DATETIME空间效率更高。

一般不要把Unix时间戳存储为整数值，不方便处理。

MySQL能存储的最小时间粒度为秒，如果要存储比秒更小粒度的日期和时间，可以使用BIGINT类型存储微秒级别的时间戳，或者使用DOUBLE存储秒之后的小数部分。

创建表时一般增加create_time(创建时间)字段，update_time(更新时间)字段，根据操作自动填入当前时间，如下：
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

位数据类型

MySQL有少数几种存储类型使用紧凑的位存储数据，这些位类型，不管从底层存储格式和处理方式上，都是字符串类型。

我们以 BIT 和 SET 为例，来解析位数据类型。

【存储结构】

BIT

BIT(1)：存储1个位，BIT(2)：存储2个位，BIT列最大长度是：64个位。

存储二进制值 b'00111001'（前面加b表示二进制，必须要有）
查询返回值是：ASCII码为该二进制数值所对应的字符'9'（'00111001'表示数是57，在ASCII码中，编码为57的字符'9'，即查到的就是字符'9'）
数字上下文场景中查询返回值是：数字57，即ASCII码的编码数字
存储空间：InnoDB引擎使用能够存储下的最小整数类型来存放，如：8位bit的话，用TINYTINT就能存放下，所以存储空间为1字节。

BIT存储

SET

SET：MySQL内部是以一系列打包的位的集合来表示，每个位(bit)或者SET元素代表一个值。

举例：
创建一个权限访问控制表acl：create table acl(perms SET('READ', 'WRITE', 'DELETE') NOT NULL);
其中把权限'READ', 'WRITE', 'DELETE'打包成一个位的集合SET，每个SET元素代表一个值：
'READ'   -- 00000001(二进制位表示) -- 1
'WRITE'  -- 00000010(二进制位表示) -- 2
'DELETE' -- 00000100(二进制位表示) -- 4

MySQL在列定义里存储位到值的映射关系，即："bit位值 - 元素字符串"，与枚举ENUM相似。

我们acl表插入权限值：insert into acl(perms) values('READ'),('WRITE'),('DELETE'),('READ,DELETE'); 看一下查询acl表数据：
（注意：可以在一列中存储多个SET元素）

SET存储

【库表设计注意点】

谨慎使用BIT类型，最好避免使用。

SET类型虽有效利用了存储空间，但改变列定义需要ALTER TABLE，代价较高，可以考虑使用一个整数包装一系列的位，如：TINYINT包装8个位。

总结

对库表设计注意点，做个整体汇总20条：

表名不使用复数名词（表名应该仅仅表示表里的实体内容，不应表示实体数量）(阿里规范-建表规约-3) 。

表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字(阿里-建表规约-2)。
正例：aliyun_admin，rdc_config，level3_name
反例：AliyunAdmin，rdcConfig，level_3_name
尽量避免 NULL，因为可为 NULL 的列会使用更多的存储空间，而且索引，索引统计和值比较都更复杂。

尽量使用可以正确存储数据的最小数据类型，因为占用更少的磁盘、内存和CPU缓存，所以更快。

整型比字符串操作代价更低，因为字符集和校对规则(排序规则)使字符比较比整型比较更复杂。
IPV4地址实际上是32位无符号整数，不是字符串，所以应该用无符号整数(INT UNSIGNED)存储IP地址
MySQL提供函数：INET_ATON(), INET_NTOA(), 在字符串表示和整数表示之间转换。
说明：用小数点将地址分为4段的表示法只是让人们阅读容易。
一个表中不要有太多的列，因为InnoDB在MySQL服务器层和存储引擎层之间会将列转换成行数据结构，依赖列的数量。

不要关联太多表，单个查询最好在12个表以内做关联。（MySQL限制了每个关联操作最多只能有61张表）

一般不要指定整数类型宽度，如ITN(20)，TINYINT(2)等，除非设定Zero Fill属性，用来填充字符个数。

整型字段不会出现负值时，将其设定为UNSIGNED，能扩大正数存储范围，同时也能免去负值的干扰。

尽量只在对小数进行精确计算时，使用DECIMAL类型，如财务数据。（float和double在存储时，存在精度损失问题）

使用DECIMAL存储，数据量大时(上千万条或更多)，考虑把小数乘以相应倍数，使用BIGINT存储；存储数据超范围时，考虑把数据拆成整数和小数分开存储。

一般情况下，数据库的表/字段，请使用utf8bm4编码的字符集。

VARCHAR是可变字符串，不预先分配存储空间，长度不要超过5000，如果超过，定义字段类型为TEXT，独立出一张表，用主键对应关联。（阿里-建表规约-8）

很短的字符串，或者所有字符串都接近同一个长度，如密码的MD5值是定长，考虑使用CHAR类型。

不要把Unix时间戳存储为整数值，不方便处理。

一般情况尽量使用TIMESTAMP，因为比DATETIME空间效率高，

创建表时一般增加create_time(创建时间)字段，update_time(更新时间)字段，根据操作自动填入当前时间，如下：
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
MySQL能存储的最小时间粒度为秒，如果要存储比秒更小粒度的日期和时间，可以使用BIGINT类型存储微秒级别的时间戳，或者使用DOUBLE存储秒之后的小数部分。

谨慎使用BIT类型，最好避免使用。

选择表的列标识符时，整数类型是最好的选择，整数类型 > 字符串类型 > ENUM和SET类型(一般避免)

MySQL数据类型解析及设计库表注意点

整数类型：

实数类型：

字符串类型

一、VARCHAR 类型

【存储空间】

【性能】

【库表设计注意点】

二、CHAR 类型

【存储空间】

【性能】

【库表设计注意点】

三、BLOB 和 TEXT 类型

四、枚举

【存储结构】

【存储空间】

【排序】

【添加/删除/更改枚举字符串】

日期和时间类型

【时间范围】

【存储空间】

【显示格式】

那么在设计数据库表字段，并定义为日期和时间类型时，应注意：【重点，敲黑板~】

位数据类型

【存储结构】

【库表设计注意点】

总结

对库表设计注意点，做个整体汇总20条：

后续持续完善...

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

Java技术

spring boot