mysql表结构设计--数字类型

1. 数字类型

1.整型类型

在整型类型中，有 signed 和 unsigned 属性，其表示的是整型的取值范围，默认为 signed。在设计时，不建议刻意去用 unsigned 属性，因为在做一些数据分析时，SQL 可能返回的结果并不是想要得到的结果

MySQL 要求 unsigned 数值相减之后依然为 unsigned，否则就会报错
为了避免这个错误，需要对数据库参数 sql_mode 设置为 NO_UNSIGNED_SUBTRACTION，允许相减的结果为 signed，这样才能得到最终想要的结果

SET sql_mode='NO_UNSIGNED_SUBTRACTION';

2.浮点类型和高精度型

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

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

在海量并发的互联网业务中使用，金额字段的设计并不推荐使用 DECIMAL 类型，而更推荐使用 INT 整型类型

2.业务表结构设计实战

整型结合属性 auto_increment，可以实现自增功能，但在表结构设计时用自增做主键，特别要注意以下两点，若不注意，可能会对业务造成灾难性的打击：

• 用 BIGINT 做主键，而不是 INT；
• 自增值并不持久化，可能会有回溯现象（MySQL 8.0 版本前. 删除后自增不连续,重启后连续,自增值不一样）。

当达到 INT 上限后，再次进行自增插入时，会报重复错误，MySQL 数据库并不会自动将其重置为 1

自增值重启后回溯问题要满足两个条件，1. 发生宕机 2. 宕机的时候记录删除了

1. 若要彻底解决自增值重启后回溯问题，有以下 2 种方法：

• 升级 MySQL 版本到 8.0 版本，每张表的自增值会持久化；
• 若无法升级数据库版本，则强烈不推荐在核心业务表中使用自增数据类型做主键。

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

decimal底层是二进制存储,小数与整数位分开存储与计算, 变长, 计算效率远不如整型高效

在海量互联网业务的设计标准中，并不推荐用 DECIMAL 类型，而是更推荐将 DECIMAL 转化为 整型类型。
字段原地更新的性能更好
在数据库设计中，非常强调定长存储，因为定长存储的性能更好

3. 应用

IPv4存储可以采用INT UNSIGNED存储，后续查询使用INET_ATON()、INET_NTOA()函数转换使用。这样可以减少聚簇索引存储空间，如针对此字段有二级索引也会同步减少存储空间
INET6_ATON(expr)，这个函数可以将ipv6转化为固定16个字节

手机号一般设计为VARCHAR类型居多，手机号是静态数据，很少更新，也不会存在性能问题