MySql表结构设计篇

（一）数值类型

（1）数据类型

MySQL 数据库支持 SQL 标准支持的整型类型：INT、SMALLINT。此外，MySQL 数据库也支持诸如 TINYINT、MEDIUMINT 和 BIGINT 整型类型：

除了整型类型，数字类型常用的还有浮点和高精度类型。

MySQL 之前的版本中存在浮点类型 Float 和 Double，但这些类型因为不是高精度，也不是 SQL 标准的类型，所以在真实的生产环境中不推荐使用，否则在计算时，由于精度类型问题，会导致最终的计算结果出错。

更重要的是，从 MySQL 8.0.17 版本开始，当创建表用到类型 Float 或 Double 时，会抛出下面的警告：MySQL 提醒用户不该用上述浮点类型，甚至提醒将在之后版本中废弃浮点类型

（2）业务中金额字段的设计

【1】浮点类型 Float 和 Double，但这些类型因为不是高精度，也不是 SQL 标准的类型，所以在真实的生产环境中不推荐使用，否则在计算时，由于精度类型问题，会导致最终的计算结果出错。

【2】通常在表结构设计中，类型 DECIMAL 可以用来表示用户的工资、账户的余额等精确到小数点后 2 位的业务。

【3】在海量互联网业务的设计标准中，并不推荐用 DECIMAL 类型，而是更推荐将 DECIMAL 转化为 整型类型。也就是说，资金类型更推荐使用用分单位存储，而不是用元单位存储。如1元在数据库中用整型类型 100 存储

【4】金额字段的取值范围如果用 DECIMAL 表示的，如何定义长度呢？因为类型 DECIMAL 是个变长字段，若要定义金额字段，则定义为 DECIMAL(8,2) 是远远不够的。这样只能表示存储最大值为 999999.99，百万级的资金存储。

用户的金额至少要存储百亿的字段，而统计局的 GDP 金额字段则可能达到数十万亿级别。用类型 DECIMAL 定义，不好统一。

另外重要的是，类型 DECIMAL 是通过二进制实现的一种编码方式，计算效率远不如整型来的高效。因此，推荐使用 BIG INT 来存储金额相关的字段。

字段存储时采用分存储，即便这样 BIG INT 也能存储千兆级别的金额。这里，1兆 = 1万亿。

这样的好处是，所有金额相关字段都是定长字段，占用 8 个字节，存储高效。另一点，直接通过整型计算，效率更高。

注意，在数据库设计中，我们非常强调定长存储，因为定长存储的性能更好。

【5】当使用 BIG INT 存储金额字段的时候，如何表示小数点中的数据呢？其实，这部分完全可以交由前端进行处理并展示。作为数据库本身，只要按分进行存储即可。

【6】整型的存储存储对mysql来讲性能更好些而且更方便于程序操作计算

【7】具体的业务场景中，选择INT、BIGINT、DECIMAL都是可以的，上面虽然一直强调BIGINT的好处，但是设计选型需要针对特定的业务。如果业务已经使用的是DECIMAL那么还是选择DECIMAL更好，如果业务确定的金额不会太大，那么也不需要使用BIGINTINT，要注意符合具体的业务场景

【8】Java中BIGINT的使用，对于如果不是无符号类型，BIGINT(20)的取值范围为-9223372036854775808~9223372036854775807。与Java.lang.Long的取值范围完全一致，mybatis会将其映射为Long。而BIGINT(20) UNSIGNED的取值范围是0 ~ 18446744073709551615，其中一半的数据超出了Long的取值范围，Mybatis将其映射为BigInteger。

【9】Java中使用DECIMAL末尾会补0，如果想要去除这个多余的0可以通过BigDecimal的stripTrailingZeros()方法，可以将一个BigDecimal格式化为一个相等的，但去掉了末尾0的BigDecimal：

【10】Java中BigDecimal的比较，要特别注意，使用equals()方法不但要求两个BigDecimal的值相等，还要求它们的scale()相等：必须使用compareTo()方法来比较，它根据两个值的大小分别返回负数、正数和0，分别表示小于、大于和等于。

【11】Java中BigDecimal尽量使用参数类型为String的构造函数

（3）自增整型主键列和字符串主键列设计

【1】在一般的业务表设计的时候我们一般都会设置一个主键列，整型结合属性 auto_increment，可以实现自增功能，其中注意事项如下
1：对于一般的小型的业务表而言，如果肯定数据量不会到达上亿或者千万级别，那么可以使用int类型作为自增主键列
2：对于业务量大的互联网系统而言，其中流水表、订单表、记录表等很有可能达到上亿的级别，那么对于这样的业务而言，我们就需要提前考虑到，并且坚决不能使用INT类型，而必须使用BIGINT，INT 的范围最大在 42 亿的级别，对于上亿的数据如果等到数据达到这个级别在进行调整那么就非常麻烦了。
【2】如果业务表使用了自增主键列，那么有俩点是我们必须清楚知晓的
1：合理的选择使用INT和BIGINT，注意如果使用了INT作为主键列，那么到达最大值之后就无法插入新数据了，再次进行自增插入时，会报重复错误
2：自增值并不持久化，可能会有回溯现象（MySQL 8.0 版本前）

【3】在海量互联网架构设计过程中，为了之后更好的分布式架构扩展性，不建议使用整型类型做主键，更为推荐的是字符串类型

（二）字符串类型

（1）基础知识

MySQL 数据库的字符串类型有 CHAR、VARCHAR、BINARY、BLOB、TEXT、ENUM、SET。不同的类型在业务设计、数据库性能方面的表现完全不同，其中最常使用的是 CHAR、VARCHAR

CHAR(N) 用来保存固定长度的字符，N 的范围是 0 ~ 255，请牢记，N 表示的是字符，而不是字节。VARCHAR(N) 用来保存变长字符，N 的范围为 0 ~ 65536， N 表示字符。

【测试长度是字符还是字节】

 CREATE TABLE `test_char` (
  `a` char(5) CHARACTER SET utf8,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

在超出 65536 个字符的情况下，可以考虑使用更大的字符类型 TEXT 或 BLOB，两者最大存储长度为 4G，其区别是 BLOB 没有字符集属性，纯属二进制存储。

MySQL 数据库的 VARCHAR 字符类型，最大能够存储 65536 个字符，所以在 MySQL 数据库下，绝大部分场景使用类型 VARCHAR 就足够了。

在表结构设计中，除了将列定义为 CHAR 和 VARCHAR 用以存储字符以外，还需要额外定义字符对应的字符集，因为每种字符在不同字符集编码下，对应着不同的二进制值。常见的字符集有 GBK、UTF8，通常推荐把默认字符集设置为 UTF8。

而且随着移动互联网的飞速发展，推荐把 MySQL 的默认字符集设置为 UTF8MB4，否则，某些 emoji 表情字符无法在 UTF8 字符集下存储，比如 emoji 笑脸表情，对应的字符编码为 0xF09F988E：

包括 MySQL 8.0 版本在内，字符集默认设置成 UTF8MB4，8.0 版本之前默认的字符集为 Latin1。因为不同版本默认字符集的不同，你要显式地在配置文件中进行相关参数的配置：

[mysqld]
character-set-server = utf8mb4
...

鉴于目前默认字符集推荐设置为 UTF8MB4，所以在表结构设计时，可以把 CHAR 全部用 VARCHAR 替换，底层存储的本质实现一模一样。UTF8MB4 字符集 1 个字符最大存储 4 个字节。从底层存储内核看，在多字节字符集下，CHAR 和 VARCHAR 底层的实现完全相同，都是变长存储！

排序规则（Collation）是比较和排序字符串的一种规则，每个字符集都会有默认的排序规则。排序规则以 _ci 结尾，表示不区分大小写（Case Insentive），_cs 表示大小写敏感，_bin 表示通过存储字符的二进制进行比较。需要注意的是，比较 MySQL 字符串，默认采用不区分大小的排序规则：

CHAR 和 VARCHAR 虽然分别用于存储定长和变长字符，但对于变长字符集（如 GBK、UTF8MB4），其本质是一样的，都是变长，设计时完全可以用 VARCHAR 替代 CHAR；

（2）场景应用

【1】对于特定的枚举值字段，比如性别，状态等值，在 MySQL 8.0 版本之前，可以使用 ENUM 字符串枚举类型，只允许有限的定义值插入。如果将参数 SQL_MODE 设置为严格模式，插入非定义数据就会报错：MySQL 8.0.16 版本开始，数据库原生提供 CHECK 约束功能，可以方便地进行有限状态列类型的设计：

说明：对于是否严格要求使用枚举类型保存特定范围数据的字段，并非是绝对的，如果涉及的表状态会变更，或者现有系统已有已经在使用类似tinyint保存，那么也是可以的

【2】对于账户密码的保存设计，对于类似银行账号、密码、身份证、手机号码等根据合规要求都需要做一定搞的安全处理

账号、身份证、手机号可以通过一定的对称加密或者非对称加密算法进行加密后存储，然后查询时解密，并且在前端展示的时候需要脱敏展示

对于密码的保存，禁止使用单一的摘要算法，比如MD5这种，这类是可以被暴力破解的，建议：动态盐 + 非固定加密算法

【3】附件、图片、头像等数据的保存，对于这些数据不建议保存BLOB类型，一般而言都是讲该部分数据上传至文件服务器，然后将保存后的URL地址保存到数据表中，数据字段的设计一般VARCHAR(255)即可

（三）日期和时间类型

【1】MySQL 数据库中常见的日期类型有 YEAR、DATE、TIME、DATETIME、TIMESTAMEP。因为业务绝大部分场景都需要将日期精确到秒，所以在表结构设计中，常见使用的日期类型为DATETIME 和 TIMESTAMP

【2】DATETIME 初始化值设置为当前时间，并设置自动更新当前时间的属性。在很多的业务表设计的时候都会设置create_time和update_time，那么为了简化在程序级别的设置，就可以通过在列定义时设置自动更新

CREATE TABLE USER (
    ID BIGINT NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(255) NOT NULL,
    CREATE_TIME DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    UPDATE_TIME DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    PRIMARY KEY(ID)
);

其中CREATE_TIME默认情况下会在输入第一次插入时赋值为当前时间，其中CREATE_TIME会在第一次插入时以及每次表记录变更时自动更新当前时间

注意：是否需要设置自动更新需要根据具体的业务选择，在我使用的一次经历中，其中每天晚上会进行一次批处理定时任务，而这个会更新表状态，但是这个是非业务操作的，仅仅是做数据的清洗检查，在该场景下我不希望每次更新状态会更新时间，因为大部分场景下，都是根据UPDATE_TIME倒序排列的，不能因为这样的系统操作影响了业务层面的数据顺序

【3】除了 DATETIME，日期类型中还有一种 TIMESTAMP 的时间戳类型，其实际存储的内容为‘1970-01-01 00:00:00’到现在的毫秒数。在 MySQL 中，由于类型 TIMESTAMP 占用 4 个字节，因此其存储的时间上限只能到‘2038-01-19 03:14:07’。

同类型 DATETIME 一样，从 MySQL 5.6 版本开始，类型 TIMESTAMP 也能支持毫秒。与 DATETIME 不同的是，若带有毫秒时，类型 TIMESTAMP 占用 7 个字节，而 DATETIME 无论是否存储毫秒信息，都占用 8 个字节。

【4】在日期的设计中，有的时候使用整型存储，其设计为直接存储 ‘1970-01-01 00:00:00’ 到现在的毫秒数，本质和 TIMESTAMP 一样。对于该方式而言，性能上有些许的提升，但其实影响很小，而且使用该类型对于通过SQL进行数据分析和运维检查非常的不友好，所以不建议使用整型来存储日期字段

【5】在实际的应用中，表结构中日期类型的选择推荐使用DATETIME

最后

以上就是感动羊为你收集整理的MySql表结构设计篇的全部内容，希望文章能够帮你解决MySql表结构设计篇所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。