mysql表结构设计技巧-字段类型选择、字符集选择

本文转载自：fangcaicoding.cn/course/14/8…

Hello 我是方才，8人后端研发leader、6年后端经验、3年团队管理&架构经验。

专注于分享成体系的编程知识、职场经验、个人成长历程等！

文末，方才送你一份优质的技术资料，记得领取哟！

步骤3：表结构设计

有了确定的技术方案后，就进入到了完整的表结构设计阶段。

主要思路是参考数据库范式&反范式设计，结合阿里巴巴规约，以及历史经验的总结，完成从表名、字段名、字段类型的定义。

先看完整的DDL

先简单看下完整的表结构，然后我们再完整讲解表结构设计一些技巧。

CREATE TABLE `kjkm_fse` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `zt_id` bigint(20) UNSIGNED NOT NULL COMMENT '账套id',
  `kjkm_id` bigint(20) UNSIGNED NOT NULL COMMENT '会计科目id',
  `kjkm_bm` varchar(64) NOT NULL COMMENT '会计科目编码-冗余字段，便于查询',
  `kjqj_id` bigint(20) UNSIGNED NOT NULL COMMENT '会计期间id',
  `ljjf_je` decimal(22,2) NOT NULL DEFAULT '0.00' COMMENT '本期借方发生额',
  `ljdf_je` decimal(22,2) NOT NULL DEFAULT '0.00' COMMENT '本期贷方发生额',
  `bqs_je` decimal(22,2) NOT NULL DEFAULT '0.00' COMMENT '本期数-金额（借-贷，结合科目方向计算得到）',
  `bqs_sl` decimal(22,2) NOT NULL DEFAULT '0.00' COMMENT '本期数-数量',
  `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_user_id` varchar(32) NOT NULL COMMENT '创建人用户id',
  `update_user_id` varchar(32) NOT NULL COMMENT '更新人用户id',
  PRIMARY KEY (`id`,`zt_id`),
  UNIQUE KEY `uk_zt_kjqj_kjkm_id` (`zt_id`,`kjqj_id`,`kjkm_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1 COMMENT='会计科目-发生额'
PARTITION BY HASH (`zt_id`) PARTITIONS 3;

数据库范式设计

1. 三范式
   • 第一范式（1NF）：确保表中的每一列都是不可再分的原子数据项。例如，ljjf_je（本期借方发生额）和ljdf_je（本期贷方发生额）分别独立存储，每一列都是不可分割的原子数据项。
   • 第二范式（2NF）：在满足1NF的基础上，确保表中的非主属性完全依赖于主键（这个主键大家可以理解为业务主键，而非ID）。通过将zt_id（账套ID）、kjqj_id（会计期间ID）和kjkm_id（会计科目ID）组合成唯一键，确保了表中的数据与这些关键字段的强关联性。
   • 第三范式（3NF）：在满足2NF的基础上，确保表中的非主属性之间不存在传递依赖关系。在上面的表里，冗余了 kjkm_bm 字段，不符合该范式。
2. 反范式设计
   • 反范式设计（Denormalization）是数据库设计中一种有意引入数据冗余的技术，旨在提高查询性能。
   • 注意点：反范式设计会增加数据存储和数据的一致性维护成本。
   • 适用于：冗余存储的字段需要用于查询，且更新频率较低或是通过预计算得到的扩展字段等情况。
   • 例如，本表中同时存储了 kjkm_id（会计科目Id） 和 kjkm_bm（会计科目编码）。这种设计虽然增加了存储空间，但减少了查询时的连表操作或计算成本，能提高查询性能。
   • 例如，本表的 bqs_je= ljjf_je - ljdf_je 是通过预计算得到的扩展字段，也是为了满足查询需求。

命名规范

包括表名和字段名，参考以下几点：

1. 必须使用小写字母或数字，使用下划线分割；
2. 尽可能顾明思议，表字段的注释要及时更新，特别是枚举字段；
3. 禁用保留字，如 desc、range、match、delayed 等；
4. 索引命名规范，名称前缀 uk_/ idx_：唯一索引名为 uk_字段名；普通索引名则为 idx_字段名；

命名规范还是很好理解的，大家日常应该是使用英语单词更多点，财务这块名词太长，所以方才使用的是中文的首字母缩写（只要整个库保持一个风格，可读性也是很高的）。

必备字段

参考阿里规约，建议表的必备字段有3个：id, create_time, update_time。

说明：其中 id 必为主键，类型为 bigint unsigned、单表时自增、步长为 1。create_time, update_time 的类型均为 datetime 类型，前者现在时表示主动式创建，后者过去分词表示被动式更新。

这个可以根据实际情况，自己去约定，比如方才建的表就有5个必备的字段：

  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_user_id` varchar(32) NOT NULL COMMENT '创建人用户id',
  `update_user_id` varchar(32) NOT NULL COMMENT '更新人用户id',

注意：

• updated_time 字段要有 ON UPDATE CURRENT_TIMESTAMP属性，该属性用于指定当表中的记录被更新时，该字段的值会自动更新为当前的时间戳（CURRENT_TIMESTAMP），这个特性通常用于记录数据最后被修改的时间。
• create_user_id 是varchar(32)类型，在此处是为了兼容历史数据，一般情况也应该是 bigint(20) UNSIGNED。

字段选型

字段类型要尽量和实际类型保持一致，然后优先选择最小的存储长度类型即可。

整数类型

参考MySQL官网：dev.mysql.com/doc/refman/… 。

不同整数类型的存储大小和取值范围：

Type	Storage (Bytes)	Minimum Value Signed	Minimum Value Unsigned	Maximum Value Signed	Maximum Value Unsigned
TINYINT	1	-128	0	127	255
SMALLINT	2	-32768	0	32767	65535
MEDIUMINT	3	-8388608	0	8388607	16777215
INT	4	-2147483648	0	2147483647	4294967295
BIGINT	8	-2^63	0	2^63-1	2^64-1

按最小存储长度原则：

• 枚举类型的字段一般使用 TINYINT；
• 注意字段可能的上下限，避免溢出，比如国民级软件的点赞数这种，就建议使用BIGINT（上次抖音出现点赞数为负数，就是因为 int溢出了）；
• 表的主键，明确数据量会持续增加，且无上限，就建议使用BIGINT；若是字典维护表这种内部使用的，主键就可以用 INT。

补充：关于整数类型在DDL中定义时括号中的数字的含义是显示宽度：比如 int(11) 中的 (11) 是一个 显示宽度（主要用于 ZEROFILL 选项时，指定数字显示时前面填充的零的数量），而不是数据类型的实际存储长度或精度，整数类型的存储长度是固定了的，int类型的储空间始终是 4 字节。

小数类型（比如金额字段）

对于小数类型，建议遵循阿里巴巴规约，使用decimal类型（如decimal(22,2)），禁止使用float和double，以确保数据的精确性。

注意： DECIMAL（5,2） 表示能存储任何具有 5 位数字和 2 位小数的值，值范围为 -999.99 到 999.99。

当然对于金额字段的存储，一般有两种方案，一种是使用 decimal类型 保留2位小数，单位一般为元及以上；另一种就是使用BIGINT类型，将单位转为分，进行存储。

字符串类型

MySQL中字符串类型，包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 以及 SET。这里方才就只讲几个重点了：

1. CHAR(M) 是定长字符串。CHAR 列的长度固定为创建表时声明的长度。M 表示列长度（是字符的个数，不是字节的个数），当存储字符长度不足M时，会用空格右填充到指定长度。适用于存储枚举code等场景。

2. VARCHAR 是变长字符串。M 表示最大列长度（字符的最大个数）。VARCHAR 的空间占用大小不得超过 65535 字节。在选择 VARCHAR 长度时，应当根据最长的行的大小和使用的字符集确定。是最常用的字符串类型。

   • 下表是单个字符占用的字节数，以及 VARCHAR 列长度的取值范围（关于字符集后续会讲）：

字符集	单个字符字节数	VARCHAR 最大列长度的取值范围
ascii	1	(0, 65535]
latin1	1	(0, 65535]
binary	1	(0, 65535]
utf8	3	(0, 21845]
utf8mb4	4	(0, 16383]

也就是说字符集为utf8mb4的 varchar类型的最大长度只能是 16383，若超过，你建表时会报错：

3. 如果你需要存储更大字节的内容，就可以使用``LONGTEXT 类型，最大列长度为 4,294,967,295 字节；或者 使用二进制大文件LONGBLOB` 类型，最大列长度为 4,294,967,295 字节。
4. 注意：对于字符串字段，若长度超过5000，就建议使用text类型，且独立出来一张表，用主键来对应，避免影响其它字段索引效率（后续讲解了聚簇索引就可以理解为什么了）。

在我们当前这个示例中，使用varchar即可：

  `kjkm_bm` varchar(64) NOT NULL COMMENT '会计科目编码-冗余字段，便于查询',

日期和时间类型

MySQL 的日期和时间类型，包括 DATE、TIME、DATETIME、TIMESTAMP 以及 YEAR。

• DATE 类型只包含日期部分，不包含时间部分。DATE 类型的格式为 YYYY-MM-DD，支持的范围是 0000-01-01 到 9999-12-31。
• TIME 类型的格式为 HH:MM:SS[.fraction]，支持的范围是 -838:59:59.000000 到 838:59:59.000000。
• DATETIME 类型是日期和时间的组合，格式为 YYYY-MM-DD HH:MM:SS[.fraction]。支持的范围是 0000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999。
• TIMESTAMP 类型是日期和时间的组合，支持的范围是 UTC 时间从 1970-01-01 00:00:01.000000 到 2038-01-19 03:14:07.999999。注意：
  • 范围上限问题：TIMESTAMP 数据类型受 2038 年问题的影响。如果存储的值大于 2038，需使用 DATETIME 类型。
  • 时区的问题：当存储 TIMESTAMP 时，MySQL 会将当前时区的 TIMESTAMP 值转换为 UTC 时区。当读取 TIMESTAMP 时，MySQL 将存储的 TIMESTAMP 值从 UTC 时区转换为当前时区（DATETIME` 不会这样处理）。
• YEAR 类型的格式为 YYYY，支持的值范围是 1901 到 2155，也支持零值 0000。

所以，方才建议，日期和时间类型字段，优先选用DATETIME，同时要合理利用其自动初始化或更新为当前时间的特性，比如说创建时间和更新时间：

  `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

逻辑删除与物理删除

什么是物理删除？什么是逻辑删除？

• 物理删除：把数据从硬盘中删除，可释放存储空间
• 逻辑删除：给数据添加一个字段，比如is_deleted，以标记该数据已经逻辑删除。

根据方才的经验，建议是优先采用逻辑删除。更方便去最终一些问题或者手动回滚数据等。

但若满足以下场景，是更适合使用物理删除的：

• 目标表的数据量较高，比如超过500万；
• 且删除操作频繁，导致被删除的数据占比较高，比如超过 1/10；
• 建议：对于核心业务数据，且无法通过其他数据派生而来，可以将删除的数据插入到额外的表中，用做备份。

这个场景下，无用的数据太多，会影响到查询和更新的效率了。

而刚好，会计科目余额表就符合这个场景，数据量超10亿，凭证更新会导致数据的频繁覆盖写入，同时本身数据就是可以通过期初数据+凭证数据计算得来，所以可以看到kjkm_fse这个表是没有is_deleted字段的。

字段个数

个人建议表中字段尽量不超过20个，最多不超过50个。

理由是：因为MySQL的聚簇索引特征，过多的字段会导致回表操作成本过高，影响查询性能。

字符集的选择

字符集影响的是字符串类型的存储，包括能否存储以及一个字符对应的字节长度。

MySQL 支持的字符集有utf8、utf8mb4、GBK、latin1等。

• latin1：MySQL 默认字符集，1 个字节长度，所以容易出现乱码问题；
• GBK ：支持中文，但是不支持国际通用字符集，2 个字节长度；
• utf8：支持中英文混合场景，国际通过，3 个字节长度；
• utf8mb4: 完全兼容 utf8，4 个字节长度，可存储更多的字符；

方才推荐字符集优先选择utf8mb4，支持更广泛的字符集范围，通过建表语句 CHARSET=utf8mb4可以指定。

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1 COMMENT='会计科目-发生额'
PARTITION BY HASH (`zt_id`) PARTITIONS 3;

排序规则的选择

排序规则会影响对字符类型的排序以及查询，是非常重要的，一不小心就容易出现bug，且很难定位。

一个字符集可以有多种排序规则。排序规则的命名格式为 <character_set>_<collation_properties>。例如，utf8mb4 字符集有一个名为 utf8mb4_bin 的排序规则，它是 utf8mb4 字符集的二进制排序规则。下表是常见字符集和排序规则的后缀和含义：

后缀	含义
_bin	二进制排序规则，区分大小写
_ci	不区分大小写
_ai_ci	不区分重音和大小写
_0900_bin	Unicode UCA 9.0.0，二进制排序规则
_unicode_ci	（较旧的）Unicode UCA 排序规则，不区分大小写
_general_ci	较宽松的 Unicode 排序规则，不区分大小写

简单看两个示例就会理解深刻了。

下面这个示例，通过COLLATE=utf8mb4_general_ci 和COLLATE=utf8mb4_bin分别设置不同的排序规则：

CREATE TABLE `fc_test` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `mc` varchar(64) NOT NULL COMMENT '名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='不区分大小的测试';

INSERT INTO fc_test( mc) VALUES('fangcaicoding'),('Fangcaicoding'),('cdoing');


CREATE TABLE `fc_test2` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `mc` varchar(64) NOT NULL COMMENT '名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='区分大小的测试';

INSERT INTO fc_test2
( mc)
VALUES('fangcaicoding'),('Fangcaicoding'),('cdoing');

查询和排序下：

-- 不区分大小写
select * from fc_test where mc = "Fangcaicoding" order by mc;
select * from fc_test order by mc desc;
-- 区分大小写
select * from fc_test2 where mc = "Fangcaicoding" order by mc;
select * from fc_test order by mc desc;

运行截图如下：

通过这个示例，我想大家都一定理解了字符集的排序规则的影响点了。

一般情况，方才推荐排序规则优先选择utf8mb4_bin，通过COLLATE=utf8mb4_bin语句设置，区分大小写，确保数据的准确性和一致性。

小结

我相信通过这个示例，大家对如何进行表结构设计，都有了自己的理解，也欢迎在评论区交流哟。

原创不易，如果对你有所帮助，记得帮方才点个赞、点个爱心，来个评论，这对方才很重要。

ps：本文的思维导图xmind源文件，都在资料库了，可以直接扫码领取。

---

交流群

茫茫人海，方才刚好写了这篇博客，而优秀的你又刚好看到且点了个赞。

相遇即是缘分，方才送你一份优质的资料，也可备注加群，方才拉你进入优质的技术交流群。