【MySQL】MySQL建表与常见类型设计陷阱（MySQL专栏启动）_mysql建表语句long类型
| 阿里云国内75折 回扣 微信号：monov8 |
| 阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6 |
📫作者简介小明java问道之路专注于研究 Java/ Liunx内核/ C++及汇编/计算机底层原理/源码就职于大型金融公司后端高级工程师擅长交易领域的高安全/可用/并发/性能的架构设计与演进、系统优化与稳定性建设。
📫 热衷分享喜欢原创~ 关注我会给你带来一些不一样的认知和成长。
🏆 InfoQ签约作者、CSDN专家博主/后端领域优质创作者/内容合伙人、阿里云专家/签约博主、51CTO专家 🏆
🔥如果此文还不错的话还请👍关注、点赞、收藏三连支持👍一下博主~
本文目录
本文导读
本文作为MySQL系列第三篇文章详细讲解了MySQL的建表语句、以及表结构的设计规范和陷阱对网络上常见的资料给出的设计方案做了博主自己的理解和反驳。
一、MySQL建表语句
MySQL建表语句很简单CREATE TABLE `表名` ()在其中设置表的列属性即可。
CREATE TABLE `表名` (
// 定义属性
// 定义索引
) // 设置表属性;二、MySQL建表字符串类型设计
MySQL 数据库的字符串类型有 CHAR、VARCHAR、BINARY、BLOB、TEXT、ENUM、SET其中最常使用的是 CHAR、VARCHAR。
1、CHAR
CHAR(N) 用来保存固定长度的字符在Unicode字符集Utf-8、Utf-16、Utf-32是这样的N 的范围是 0 ~ 255请牢记N 表示的是字符而不是字节。
在表结构设计中还需要额外定义建表对应的字符集。多字节字符集 (MBCS)通常指的是ANSI、中文编码以及Shift-jisjiseuc-jpeuc-kr等。Unicode字符集Unicode字符集即平常说的宽字节包含Utf-8、Utf-16、Utf-32。
常见的字符集有 GBK、UTF8通常推荐把默认字符集设置为 UTF8。
2、VARCHAR
VARCHAR(N) 用来保存变长字符N 的范围为 0 ~ 65536 N 表示字符。在超出 65536 个字符的情况下可以考虑使用更大的字符类型 TEXT 或 BLOB两者最大存储长度为 4G其区别是 BLOB 没有字符集属性纯属二进制存储。
随着移动互联网的飞速发展推荐把 MySQL 的默认字符集设置为 UTF8MB4否则某些 emoji 表情字符无法在 UTF8 字符集下存储。
MySQL 8.0 版本字符集默认设置成 UTF8MB4UTF8MB4 字符集 1 个字符最大存储 4 个字节8.0 版本之前默认的字符集为Latin1。
鉴于目前默认字符集推荐设置为 UTF8MB4所以在表结构设计时可以把 CHAR 全部用 VARCHAR 替换底层存储的本质实现一模一样。
3、枚举类型设计实战
枚举类型设计
设计表结构时你会遇到一些固定选项值的字段。例如状态字段***_state有效的值为有限状态例如01订单初始状态、02下单成功、03支付中……。
很多学习资料和博客推荐在 MySQL 8.0 版本之前可以使用 ENUM 字符串枚举类型只允许有限的定义值插入。如果将参数 SQL_MODE 设置为严格模式插入非定义数据就会报错。
这里博主要跟这些资料唱个反调我们在工程中的状态基本都是我们手动set的这里博主认为如果使用了 ENUM 字符串枚举类型恰恰不利于互联网的高速扩展的设计原则。
在这里我推荐在工程中维护一个 ENUM 枚举类我们对数据库操作的的时候状态或者相关枚举类型的字段从枚举类中获取这样方便维护并且利于扩展。
`TXN_TYPE` varchar(8) CHARACTER NOT NULL COMMENT '交易类型|消费:SQT,退货:SQRT',三、MySQL建表ID和金额的设计与实战
1、ID自增的设计
进行实战设计之前我们需要了解整型类型
MySQL 数据库支持 SQL 标准支持的整型类型INT、SMALLINT、TINYINT、MEDIUMINT 和 BIGINT 整型类型。INT占用4字节取值范围是-2147483648 ~ 21474836472^31BIGINT占用8字节-9223372036854775808 ~92233720368547758072^63
除了整型类型数字类型还有浮点和高精度类型。MySQL 之前的版本中存在浮点类型 Float 和 Double在真实的生产环境中不推荐使用在计算时由于精度类型问题会导致最终的计算结果出错。
ID一般我们会设置为自增结合 auto_increment可以实现自增功能但在表结构设计时用自增做主键一般只会使用 BIGINT 类型做主键。
`ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',原因有几点一是为了扩展性int 的取值范围不一定适用互联网场景的增速这里面需要注意MySQL 8.0 版本前自增不持久化自增值可能会存在回溯问题例如 1/2/3/4我把4删点再次插入的时候主键ID还是 1/2/3/4这就是回溯问题解决办法就是在使用的时候评估这个方案会不会有影响或者直接升级MySQL。
2、互联网企业金额字段设计原理
我们常常在其他博客看到这样一种说法“在海量互联网业务的设计标准中并不推荐用 DECIMAL 类型而是更推荐将 DECIMAL 转化为 整型 BIGINT类型。”他给出的理由是所有金额相关字段都是定长字段占用 8 个字节存储高效。第二直接通过整型计算效率更高。
而事实上真的是这样吗？
金额字段的取值范围如果用 DECIMAL 表示的则定义为 DECIMAL(16,2) 这样满足的万亿以上的场景了。
`TRANS_AMT` decimal(16, 0) NULL DEFAULT NULL COMMENT '子单交易金额',
`CASH_AMT` decimal(16, 0) NULL DEFAULT NULL COMMENT '子单现金金额',
`POINT_AMT` decimal(16, 0) NULL DEFAULT NULL COMMENT '子单积分金额',为什么我推荐使用DECIMAL而不是BIGINT我们在存储金额的时候一般是分为单位例如100,.00就是 1 元当我们下单金额例如100元我们的库里就会落 10000.00但是这比订单购买了1个item商品3件sku这100元就要分摊给这3件sku商品这时候对于分摊的计算在代码中int、long类型没有BigDecimal 计算的精准。
四、MySQL建表时间类型设计与实战
MySQL 数据库中常见的日期类型有 YEAR、DATE、TIME、DATETIME、TIMESTAMEP。
因为业务绝大部分场景都需要将日期精确到秒所以在表结构设计中常见使用的日期类型为DATETIME 和 TIMESTAMP。
这里面TIMESTAMP有一个大坑TIMESTAMP 其实际存储的内容为‘1970-01-01 00:00:00’到现在的毫秒数。在 MySQL 中由于类型 TIMESTAMP 占用 4 个字节因此其存储的时间上限只能到‘2038-01-19 03:14:07’。
我们工程中生产环境等等一般使用的是DATETIME DATETIME 最终展现的形式为YYYY-MM-DD HHMMSS固定占用 8 个字节。
从 MySQL 5.6 版本开始DATETIME 类型支持毫秒DATETIME(N) 中的 N 表示毫秒的精度。例如DATETIME(6) 表示可以存储 6 位的毫秒值。同时DATETIME 不存在时区转化问题。一般是在国际化项目中服务器端或者前端进行转换这样查询或者变更效率更高。
每个表都要有一个时间字段在做表结构设计规范时强烈建议你每张业务核心表都增加一个 DATETIME 类型的 last_modify_date 字段并设置修改自动更新机制 即便标识每条记录最后修改的时间。开发人员可以知道每次操作记录更新的时间以便做后续的处理。
`CREATE_TIME` datetime(0) NOT NULL COMMENT '创建时间',
`CREATE_BY` varchar(32) NOT NULL COMMENT ' 创建人',
`UPDATE_TIME` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',
`UPDATE_BY` varchar(32) CHARACTER NOT NULL COMMENT '更新人',比如在电商的订单表中可以方便对支付超时的订单做处理；在金融业务中可以根据用户资金最后的修改时间做相应的资金轧差等。
五、MySQL高扩展JSON设计与实战
关系型的结构化存储存在一定的弊端因为它需要预先定义好所有的列以及列对应的类型。但是业务在发展过程中或许需要扩展单个列的描述功能。
这时如果能用好 JSON 数据类型那就能打通关系型和非关系型数据的存储之间的界限为业务提供更好的架构选择。JSON 类型的另一个好处是无须预定义字段字段可以无限扩展。
`ITEM_INFO` JSON COMMENT '商品信息',但是这里博主并不推荐大家这么做因为JSON类型及其难维护并且写sql的时候很麻烦
我举个例子我想插入一条信息我需要
SET @item_info = '{
"item_id" : "12345",
"item_amt" : "1024.00"
}';
INSERT INTO 表名 VALUES ( , @item_info);一般在生产中我们这样处理在定义时定义一个超大的字符串类型在代码中使用JSON转换成一个JSON对象的字符串保存。
`ITEM_INFO` varchar(1000) CHARACTER DEFAULT NULL COMMENT '商品信息',总结
本文作为MySQL系列第三篇文章详细讲解了MySQL的建表语句、以及表结构的设计规范和陷阱对网络上常见的资料给出的设计方案做了博主自己的理解和反驳。