数据库设计规范 - MySQL

转载

mb601a5cdeee340 2021-02-10 19:06:28

数据库设计规范

表达是与否概念的字段，必须使用 is_xxx的方式命名，数据类型是 unsigned tinyint（ 1表示是，0表示否）任何字段如果为非负数，必须是 unsigned。表名、字段名必须使用小写字母、数字、下划线组成的的蛇形命名法；表名不使用复数名词，表名应该仅仅表示表里面的实体内容，不应该表示实体数量。表的命名最好是加上“业务名称表的作用”。库名与应用名称尽量一致。禁用保留字。唯一索引名为 uk字段名；普通索引名则为 idx_字段名。小数类型为 decimal，禁止使用 float和 double。（如果存储的数据范围超过 decimal的范围，建议将数据拆成整数和小数分开存储。）如果存储的字符串长度几乎相等，使用 char定长字符串类型。 varchar是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。字段允许适当冗余，以提高性能，但是必须考虑数据同步的情况。冗余字段应遵循： 1）不是频繁修改的字段。 2）不是 varchar超长字段，更不能是 text字段。正例：商品类目名称使用频率高，字段长度短，名称基本一成不变，可在相关联的表中冗余存储类目名称，避免关联查询。单表行数超过 500万行或者单表容量超过 2GB，才推荐进行分库分表。合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

数据库命名规则

1、基本命名原则

字母全部小写原则：所有数据库对象命名字母全部小写，统一大小写有助于在多数据库间转移。
字符范围原则：只能使用英文字母、下划线、数字进行命名，且首位字符必须是英文字母。
分段命名原则：命名中多个单词间采用下划线分割，以便阅读同时方便某些工具对数据库对象的映射。例如：create_at。
不要用保留词：数据库对象命名不能直接使用数据库保留关键字，但分段中可以使用。如 user 不能用于表名、列名等，但是 username 可以用于列名，user_info 也可以用于表名。
同义性原则：对于同一含义尽量使用相同的单词命名，不管使用英文单词、英文缩写还是拼音首字母，以免引起误解。如 telphone 的 A 表中表示固定电话号码，在 B 表中就不应该用于表示移动电话号码。尽量避免同一单词表示多种含义的情况。
命名方式一致原则：在一个系统、一个项目中尽量采用一致的命名方式，都采用英文单词或者拼音首字母。尤其要避免在一个对象命名中同时采用英文单词和拼音首字母。如确实需要在一个项目中采用两种命名方式，考虑系统功能设计相关表（开发）使用英文单词命名，业务相关的表（实施）使用拼音首字母。

字段类型设计

一个汉字3个字节。

时间戳字段的数据类型为 int(10) UNSIGNED
ip 字段数据类型为 varchar(46)。（附注 1）
手机号字段的数据类型为 varchar(11)
user_name字段类型为 varchar(30), title 不超过50个汉字，字段类型为 varchar(150)
省、市、区三个字段的数据类型为 varchar(60)，详细地址字段类型为 varchar(150)
status 字段用 tinyint(1) 而非 enum 数据类型
is_ 布尔值字段使用 tinyint(1) 而非 enum(true,false) 数据类型
图片字段类型为 varchar(100)，网页 SEO 关键词 keywords 不超过32个汉字，字段类型 varchar(96)，description 不超过200个汉字，字段类型 varchar(500)。附注：
IPv4 地址字符串形式最大长度为 16 字节（255.255.255.255，结束符也需要占据 1 个字节），IPv6 地址字符串形式最大长度为 46 字节（ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff，结束符也需要占据 1 个字节）。
经纬度范围是-180~180。经纬度值示例：40.69847032728747。谷歌官方建议存储经纬度的时候，使用 double(10,6) 精确到分米级就可以了。小数点后 7 位，精度就是 1CM。
数据类型 enum 的 3 个缺点：
新增 enum 值或删除 enum 值的时候需要重建整个表，当数据量大的时候可能需要耗费数小时。
enum 值的排序规则是按创建表结构时指定的顺序，而非字面值的大小。
依赖 mysql 对 enum 值的校验并不是非常必要，在默认配置下插入非法值最终会变成空值。
float是单精度浮点数值，double是双精度浮点数值，存储值最大为16位，decimal 是定点型，最大存储值为38位。float 和 double 求 SUM 的结果都是不精确的，只有 decimal 求 SUM 得到的是精准数值。所以，decimal 类型是适合财务和货币计算。
int(M)，M 表示最大显示宽度。以前总是会误以为int(3)存储的数值小于 int(11)存储的数值，这是大错特错的。int(3)和int(11)都表示 2 147 483 647 这个值，它在数据库里面存储的都是4个字节的长度。

1、整数型的数值类型已经限制了取值范围，有符号整型和无符号整型都有，而M值并不代表可以存储的数值字符长度，它代表的是数据在显示时显示的最小长度。 2、当存储的字符长度超过M值时，没有任何的影响，只要不超过数值类型限制的范围。 3、当存储的字符长度小于M值时，只有在设置了zerofill用0来填充，才能够看到效果，换句话就是说，没有zerofill，M值就是无用的。

总结：int(11)，tinyint(1)，bigint(20)，后面的数字，不代表占用空间容量。而代表最小显示位数。这个东西基本没有意义，除非你对字段指定zerofill。所以我们在设计mysql数据库时，建表时，mysql会自动分配长度：int(11)、tinyint(4)、smallint(6)、mediumint(9)、bigint(20)，就用这些默认的显示长度就可以了。不用再去自己填长度，比如搞个int(10)、tinyint(1)之类的，基本没用。而且导致表的字段类型多样化。如果加了unsigned，则最大值翻倍。如tinyint默认范围(-128~127)，tinyint unsigned的值范围翻倍为(0~256)。

常见 status 字段的值范围英文关键词： normal=正常,hidden=禁用,unaudited=待审核,auditing=审核中,rejected=审核失败,audited=已审核 unpay=未付款,paid=待发货,unreceipted=待签收,finish=交易完成,closed=关闭用户状态: 0=正常,0=禁用,unaudited=待审核,rejected=审核失败产品状态: normal=正常,hidden=下架,unaudited=待审核,rejected=审核失败进销存状态: replenishment=补货,exchange=兑换,sell=销售订单状态: unpay=未付款,paid=待发货,unreceipted=待签收,finish=交易完成,closed=关闭任务状态: notstarted=未开始,underway=进行中,finish=已完成,abort=已放弃提现状态: unaudited=未审核,finish=提现成功,cancel=取消提现,rejected=审核失败,closed=关闭充值状态: unpaid=待付款,finish=充值成功,cancel=取消充值,closed=关闭认证状态: unaudited=待审核,auditing=审核中,rejected=未通过,audited=已认证标志状态: hot=热卖,best=精品,new=新品商品类型: appoint=预约商品,presell=预售商品

2、对象命名前缀规范

以下对象命名采用固定前缀进行命名，前缀表示数据库对象的类型，前缀代码规范如下：

类型	前缀规范	说明
索引	idx_	INDEX缩写，不区分索引类型
主键约束	pk_	PRIMARY KEY
外键约束	fk_	FOREIGN KEY
唯一约束	uk_	UNIQUE KEY
序列	seq_	SEQUENCE
函数	f_	FUNCTION
过程	sp_	STORE PROCEDURE
触发器	trg_	TRIGGER

3、表和列命名规范

3.1 数据表命名规范

表名采用 “蛇形命名法” 多段式命名，各单词间用下划线分隔，例如：t_sys_user。
表名只允许用英文字母、下划线、数字进行命名，不允许用中文或者其他符号；
表名全部字母小写；

根据历史习惯各系统常用表类前缀作如下约定：

表分类	前缀	示例
系统类	t_sys_	t_sys_config
用户	t_user_	t_user_info
权限类	t_auth_	t_auth_rule, t_auth_group
日志类	t_log_	t_log_user_login, t_log_user_operate
字典类	t_md_	t_md_biz
临时表	t_tmp_	t_tmp_import

| 框架表 | t_xstd_ | t_std_auth_admin

建议：表名也用于相关索引、分区、分区表空间、约束、主键等命名，因此为了避免相关对象命名长度超过限制，建议表名长度不要超过20。

3.2 数据列命名规范

建议：列名采用多段式命名，只允许用英文字母、下划线、数字进行命名，不允许用中文或者其他符号。

列名字母全部小写。
列名采用 “蛇形命名法”多段式命名时，各单词间用下划线分隔；
列名不能直接使用数据库保留字；
日期类型字段推荐以 date 结尾的名字命名，时间类型的字段推荐以 time 结尾的名字命名。
约定特殊含义字段，如：pid表示分类表的父级ID，外键字段采用 fk_xxx_id 命名法，如 fk_category_id。
自动时间戳字段推荐名称：created_at, updated_at, deleted_at（Laravel、CodeIgniter推荐）

常用字段设计规范

序号	字段名称	数据类型	默认值	备注
1	created_at	timestamp	CURRENT_TIMESTAMP	创建时间
2	updated_at	timestamp	CURRENT_TIMESTAMP	更新时间
3	status	enum('normal','hidden')	normal=可用,hidden=不可用	状态值
4	pid	INT(11)	0	父级ID

状态标识字段采用 ENUM 数据类型，字段值尽量用单词描述，以下是常见的业务状态值定义

用户状态:normal=正常,hidden=禁用,unaudit=待审核,failure=审核失败
产品状态:normal=正常,hidden=下架,unaudit=待审核,failure=审核失败
进销存状态:replenishment=补货,exchange=兑换,sell=销售订单状态:unpay=未付款,paid=待发货,unreceipted=待签收,finish=交易完成,closed=关闭
任务状态:notstarted=未开始,underway=进行中,finish=已完成,abort=已放弃
提现状态:unaudit=未审核,finish=提现成功,cancel=取消提现,failure=审核失败,closed=关闭
充值状态:unpaid=待付款,finish=充值成功,cancel=取消充值,closed=关闭

4、物理表设计示例

4.1 用户信息表结构设计

序号	字段名称	数据类型	允许NULL	默认值	备注
1	id	INT(11)	NOT NULL		主键
2	user_name	VARCHAR(30)	NOT NULL		用户名
3	mobile	VARCHAR(11)	NULL	''	手机号
4	password	VARCHAR(32)	NOT NULL		密码
5	status	ENUM('normal','hidden)	NOT NULL	'normal'	状态
6	created_at	TIMESTAMP	NOT NULL	CURRENT_TIMESTAMP	创建时间

4.2 MySQL脚本示例

CREATE TABLE IF NOT EXISTS t_sys_user (  `id` INT (11) NOT NULL AUTO_INCREMENT,  user_name VARCHAR (30) NOT NULL COMMENT '用户名',  mobile VARCHAR (11) NOT NULL DEFAULT '' COMMENT '手机号码',  `password` VARCHAR (30) NOT NULL COMMENT '密码',  `status` ENUM ('normal', 'hidden') NOT NULL DEFAULT 'normal' COMMENT '状态(normal=可用,hidden=不可用)',  create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',  update_time DATETIME NULL COMMENT '更新时间',  PRIMARY KEY (`id`),  UNIQUE (`user_name`)) ENGINE = INNODB CHARSET = utf8 COMMENT '用户信息表';`create_at` INT UNSIGNED NOT NULL COMMENT '创建时间',

建议：MySQL系统关键词一律大写。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。