文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

后端程序员必备:15个MySQL表设计的经验准则

2024-11-29 18:37

关注

昨天星球粉丝,咨询了一个并发的问题~ 我提供了一个乐观锁兜底的方案,然后发现他们的表,都没有加version字段的,我想到,这不是表设计通用字段嘛。因此,本文跟大家聊聊,设计表的时候,有哪些经验准则。

1. 设计表时,尽量都有这几个通用字段

表必备一般来说,或具备这几个字段:

2. 每个字段都要有注释,尤其涉及枚举这些时

我们在设计表的时候,每个字段,都要写上注释哈,尤其涉及到一个枚举字段的时候,更要把每个枚举值写出来,后面如果有变更,也要维护到这里来~

反例:

CREATE TABLE order_tab (  
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNIQUE, 
    user_id BIGINT NOT NULL,  
    total_amount DECIMAL(10, 2) NOT NULL, 
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',   
    payment_status VARCHAR(20) DEFAULT 'not_paid',  
    version INT DEFAULT 0,
    created_time DATETIME,
    updated_time DATETIME,
    creator VARCHAR(255),
    modifier VARCHAR(255)  
);

正例:

CREATE TABLE order_tab (  
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '订单项的唯一标识符,自增主键',  
    order_id BIGINT UNIQUE COMMENT '订单的唯一标识符,在整个系统中唯一',  
    user_id BIGINT NOT NULL COMMENT '用户的唯一标识符,关联到用户表',  
    total_amount DECIMAL(10, 2) NOT NULL COMMENT '订单的总金额,精确到小数点后两位',  
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT '订单的状态,例如:PENDING(待处理)、COMPLETED(已完成)等',  
    payment_status VARCHAR(20) DEFAULT 'not_paid' COMMENT '订单的支付状态,如:not_paid(未支付)、paid(已支付)等',  
    version INT DEFAULT 0 COMMENT '乐观锁版本号,用于并发控制',  
    created_time DATETIME COMMENT '订单的创建时间',  
    updated_time DATETIME COMMENT '订单的最后一次更新时间',  
    creator VARCHAR(255) COMMENT '订单的创建者,通常记录创建订单的用户或系统的用户名',  
    modifier VARCHAR(255) COMMENT '订单的修改者,通常记录最后修改订单的用户或系统的用户名'  
);

3. 命名规范

数据库表名、字段名、索引名等都需要命名规范,可读性高(一般要求用英文),让别人一看命名,就知道这个字段表示什么意思。

比如一个表的账号字段,反例如下:

acc_no,1_acc_no,zhanghao

正例:

account_no,account_number

4. 选择合适的字段类型

设计表时,我们需要选择合适的字段类型,比如:

5. 主键设计要合理

主键设计的话,最好不要与业务逻辑有所关联。有些业务上的字段,比如身份证,虽然是唯一的,一些开发者喜欢用它来做主键,但是不是很建议哈。主键最好是毫无意义的一串独立不重复的数字,比如UUID,又或者Auto_increment自增的主键,或者是雪花算法生成的主键等等;

6.选择合适的字段长度

先问大家一个问题,大家知道数据库字段长度表示字符长度还是字节长度嘛?

其实在mysql中,varchar和char类型表示字符长度,而其他类型表示的长度都表示字节长度。比如char(10)表示字符长度是10,而bigint(4)表示显示长度是4个字节,但是因为bigint实际长度是8个字节,所以bigint(4)的实际长度就是8个字节。

我们在设计表的时候,需要充分考虑一个字段的长度,比如一个用户名字段(它的长度5~20个字符),你觉得应该设置多长呢?可以考虑设置为 username varchar(32)。字段长度一般设置为2的幂哈(也就是2的n次方)。

7. 优先考虑逻辑删除,而不是物理删除

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

物理删除就是执行delete语句,如删除account_no =‘666’的账户信息SQL如下:

delete from account_info_tab whereaccount_no ='666';

逻辑删除呢,就是这样:

update account_info_tab set is_deleted = 1 where account_no ='666';

为什么推荐用逻辑删除,不推荐物理删除呢?

8. 一张表的字段不宜过多

我们建表的时候,要牢记,一张表的字段不宜过多哈,一般尽量不要超过20个字段哈。笔者记得上个公司,有伙伴设计开户表,加了五十多个字段。。。

如果一张表的字段过多,表中保存的数据可能就会很大,查询效率就会很低。因此,一张表不要设计太多字段哈,如果业务需求,实在需要很多字段,可以把一张大的表,拆成多张小的表,它们的主键相同即可。

当表的字段数非常多时,可以将表分成两张表,一张作为条件查询表,一张作为详细内容表 (主要是为了性能考虑)。

9. 尽可能使用not null定义字段

如果没有特殊的理由, 一般都建议将字段定义为 NOT NULL 。

为什么呢?

10. 设计表时,评估哪些字段需要加索引

首先,评估你的表数据量。如果你的表数据量只有一百几十行,就没有必要加索引。否则设计表的时候,如果有查询条件的字段,一般就需要建立索引。但是索引也不能滥用:

假设你新建一张用户表,如下:

CREATE TABLE user_info_tab (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `create_time` datetime NOT NULL,
  `modifed_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

对于这张表,很可能会有根据user_id或者name查询用户信息,并且,user_id是唯一的。因此,你是可以给user_id加上唯一索引,name加上普通索引。

CREATE TABLE user_info_tab (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `create_time` datetime NOT NULL,
  `modifed_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE,
  UNIQUE KEY un_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

11. 避免使用MySQL保留字

如果库名、表名、字段名等属性含有保留字时,SQL语句必须用反引号来引用属性名称,这将使得SQL语句书写、SHELL脚本中变量的转义等变得非常复杂。

因此,我们一般避免使用MySQL保留字,如select、interval、desc等等

12. 一般都选择INNODB存储引擎

建表是需要选择存储引擎的,我们一般都选择INNODB存储引擎,除非读写比率小于1%, 才考虑使用MyISAM 。

有些小伙伴可能会有疑惑,不是还有MEMORY等其他存储引擎吗?什么时候使用它呢?其实其他存储引擎一般除了都建议在DBA的指导下使用。

我们来复习一下这MySQL这三种存储引擎的对比区别吧:

13. 选择合适统一的字符集

数据库库、表、开发程序等都需要统一字符集,通常中英文环境用utf8。

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

14. 时间的类型选择

我们设计表的时候,一般都需要加通用时间的字段,如create_time、modified_time等等。那对于时间的类型,我们该如何选择呢?

对于MySQL来说,主要有date、datetime、time、timestamp 和 year。

15. 安全性考虑

来源:捡田螺的小男孩内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-后端开发
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯