大家好，我是讯享网，很高兴认识大家。



mysql表设计规范

表设计是每一个后端程序员都无法避开的一块砖，而且这块砖一不小心就很容易烫手，本篇笔记就是为了帮助大家在设计表时能够轻松拿捏。

1.命名规范

数据库表名、字段名、索引名等都需要命名规范。命名可读性要高，尽量使用英文，采用驼峰或者下划线分割的方式，让人见名知意。

反例：这些命名过于简单，缺乏描述性，很难让人理解它的含义。

1.表名：a、b、c

2.字段名：aaa、bbb、ccc 

3.索引名：index1、index2、index3 

正例：这些命名就让人见名知意。

1．表名：customers、orders、products

2．字段名：customer＿id、order＿date、product＿name

3．索引名：idx＿customer＿id、idx＿order＿date

Tips:

表名、字段名必须使用小写字母，禁止使用数字开头，禁止使用拼音，并且一般不使用英文缩写。

主键索引名为pk＿字段名；唯一索引名为uk＿字段名；普通索引名则为idx＿字段名。

2.选择合适的字段类型

设计表时，需要选择合适字段类型，比如说：

根据数据类型选择字段类型：不同的数据类型应该使用不同的字段类型。

考虑数据长度：字段类型应该根据所需存储的数据长度来选择。

注意精度和小数位数：对于需要精确数值计算的字段 （如货币和百分比），应该选择带有精度和小数位数的字段类型 （如DECIMAL）

考虑数据完整性：字段类型也应该考虑到数据完整性，日期型数据应该使用DATE或DATETIME类型，以确保输入的日期格式正确。

3.主键设计要合理

主键的设计在数据库中非常重要，它用于唯一标识表中的每一行数据，并且在数据操作和查询中起到关键作用。 通常主键的设计，不要与业务相关联，因为业务是会发生变化的，应当使用自增的id，并且保持主键的连续性。比如说可以使用优化的雪花id等等。

4.选择合适的字段长度

首先问大家一个问题，数据库字段长度表示字符长度还是字节长度？

在mysql中，varchar和char类型表示字符长度，而其他类型表示的长度都表示字节长度

char(10)表示字符长度是10。

bigint(4) 表示显示长度是4个字节，但是因为bigint实际长度是8个字节，月所以bigint(4) 的实际长度就是8个字节

所以在设计表时需要充分考虑一个字段的长度，比如一个用户名字段 (它的长度5~20个字符)，你觉得应该设置多长呢？ 可以考虑设置为Varchar(32)。需要注意字段长度一般设置为2的n次方

5.优先考虑逻辑删除，而不是物理删除

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

物理删除：把数据从硬盘中删除， 可释放存储空间

逻辑删除：给数据添加一个字段，比如is deleted 以标记该数据已经逻辑删除。

为什么推荐用逻辑删除， 不推荐物理删除呢？

数据恢复困难，物理删除会导致索引树重构

6.每个都需要添加通用字

id: 主键，一个表必须得有主键，必须

create time：创建时间

creator：创建人

updatetime：修改时间，必须，夏更新记录时，需要更新

update_by：修改人

remark：数据记录备注

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

建表的时候一张表的字段不要太多了。尽量不超过20个。超出的话优先考虑拆分，也就是通常的查询表，说详情表。

查询效率：当表中保存的数据数量很大时，查询操作需要检索的数据也会随之增加。 如果表的字段数过多，查询操作就需要读取更多的数据，这会导致查询效率变慢。

存储空间：表的字段数越多，每一行数据占用的存储空间也就越大。对于大型数据库来说，这可能会导致磁盘空间的浪费。

数据库设计复杂性：当表的字段数过多时，数据库的设计和维护变得更加复杂。 这可能涉及到索引和关联表的设计，以确保数据的完整性和一致性

8.定义字段尽可能notnull

如果没有特殊的理由， 一般都建议将字段定义为NOT NULL。为什么呢？

首先， NOT NULL可以防止出现空指针问题

其次，NULL值存储也需要额外的空间的，它也会导致比较运算更为复杂，使优化器难以优化SQL，NULL值有可能会导致索引失效

如果将字段默认设置成一个空字符串或常量值并没有什么不同，且都不会影响到应用逻辑， 那就可以将这个字段设置为NOT NULL。

9.合理添加索引

当设计表时，需要考虑哪些字段需要加索引， 可以遵循以下几个原则：

假设你有一个订单表，包含订单ID、 用户ID、 订单金额、 订单状态等字段。 现在需要根据用户ID和订单状态进行查询，可以考虑为用户ID和订单状态这两个字段建立联合索引，例如：

CREATE TABLE order_tab (

  id int(11） NOT NULL AUTO INCREMENT,

讯享网

 user_id int(11） NOT NULL,

 amount decimal(10，2） NOT NULL,

 status Varchar(20） NOT NULL,

 createtime datetime NOT NULL,

 PRIMARY KEY(id),

 KEY idx_user_status(user_id,status) USING BTREEENGINE=InnoDB) DEFAULT CHARSET=utf8;

10.不需要严格守3NF，通过业务字段余来减少表关联

简单来说就是反范式设计。 常见形式是在第三范式(3NF)的基础上，进一步进行余，从而减少表关联回顾下什么是数据库三范(3NF)?

第一范式：对属性的原子性，要求属性具有原子性， 不可再分解；

第二范式：对记录的唯一性，要求记录有唯一标识， 即实体的唯一性，即不存在部分依赖；

第三方式： 对字段的余性， 要求任何字段不能由其他字段派生出来，它要求字段没有余，即不存在传递依赖；

假设需要设计一个产品订单表，包含以下字段： 订单ID、 用户ID、 订单日期、 产品名称、 产品价格、产品数量以及订单总价。 正常情况下，可能会分别设计订单表和产品表，并使用外键进行关联，例如：

CREATE TABLE order(

id int(11) NOT NULL AUTO INCREMENT,

user_id int(11) NOT NULL,

order_date date NOT NULL,

product_id int(11) NOT NULL,

quantity int(11) NOT NULL,

PRIMARY KEY (id),

FOREIGN KEY (product_id) REFERENCES product(id)

)

ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE product(

 id int(11) NOT NULL AUTO INCREMENT,

 name Varchar(255) NOT NULL,

 price decimal(10,20) NOT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这个设计方式符合范式要求， 但在查询时需要进行表关联操作， 可能会降低查询效率。为了提高查询效率，我们可以使用反范式的设计方式，将订单表中的产品名称、 产品价格和订单总价余存储到订单表中，从而避免关联查询。 例如：

CREATE TABLE order(

id int(11） NOT NULL AUTO INCREMENT,

userid int(11) NOT NULL,

order_date date NOT NULL,

product_name varchar(255) NOT NULL,

product_price decimal(10,2) NOT NULL,

quantity int(11) NOT NULL,

total_price decimal(10,2) NOT NULL,

PRIMARY KEY(id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

通过这种反范式的设计方式， 我们可以避免联操作，提高查询效率。 但同时也带来了一些缺点，例如数据几余、 数据更新困难等。 因此， 在实际应用中需要根据具体情况进行选择。

11.避免使用MySQL保留

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

如果你需要使用这些保留字作为表名、 列名或其他标识符，你可以考虑以下方法来避免冲突

12.不搞处键关联，一般都在代码维护

在数据库设计中，使用外键关联是一种良好的实践，可以确保数据的完整性和一致性。 外键关联可以帮助维护表之间的关系，防止无效或不一致的数据插入、 更新或删除操作。然而，在某些情况下，也存在一些缺点，这可能是导致现在不太推荐使用外键关联的原因之一。 以下是一些这种情况：

因此，在决定是否使用外键关联时， 需要考虑实际业务需求和场景，并进行权衡和决策。 在某些情况下，可以采用其他方法来保证数据的完整性和一致性，例如使用应用程序逻辑或数据库触发器来实现约束检查和数据操作。同时，需要注意数据库设计的基本原则和**实践， 例如避免数据余、 遵循规范化原则和正常化理论等

13.字段注释

设计表时每个字段的含义要注释清楚，包括枚举类型。比如说：

 order_status varchar(2) COLLATE utf8bin NOT NULL COMMENT ‘订单状态 01：待支付 02:已支付’

14.时间的类型选择

时间类型的选择一般都要好好考虑，因为不同的类型存储的格式不同。对于MySQL来说， 主要有date、 datetime、 time、 timestamp和year

推荐优先使用datetime类型来保存日期和时间， 因为存储范围更大， 且跟时区无关

15.SQL编写的一些优化经验