数据库设计规范

数据库设计规范

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

数据库命名规则

1、基本命名原则

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

推荐的命名规范

1. 数据表名称使用全小写带下划线的 “蛇形命名法”，如：tb_user_task, tb_user_log，tb 表示业务名称 Taobao 的简写代号，作为表前缀。
2. 数据表字段使用全小写带下划线的 “蛇形命名法”，如：user_name, first_name, real_name, nick_name, login_ip, create_time。
3. 数据表特殊含义字段命名约定，如：pid 表示分类表的父级 ID，category_id 表示分类外键字段名称，外键字段使用下划线命名法，如 category_id。主键 ID 命名为 id 而非 "表名_id" 的格式。

amount 、quantity、 number 三者之间的区别
amount 与不可数（uncountable）、无生命（inanimate）的名词连用，如：
The amount of work I did yesterday was twice the amount I did today.
She spent a very large amount of money yesterday.
quantity 与可数（countable）或不可数（uncountable）、无生命（inanimate）的名词连用，如：
There are large quantities of apples on the market.
A large quantity of fuel has been used.
number 与可数（countable）、有生命（animate）或无生命（inanimate）的名词连用，如：
A small number of people have known the fact.
He has a large number of toys.
注意：amount, number 和 quantity 的前面经常使用 vast, large, small 等形容词来修饰，而这些形容词前面有时还会使用 very 等程度副词来修饰。

count 总数；数数；量的计数；

字段类型设计

一个汉字占用 3 个字节。

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

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

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

表分类	前缀	示例
系统类	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 数据列命名规范

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

1. 列名字母全部小写。
2. 列名采用 “蛇形命名法”多段式命名时，各单词间用下划线分隔；
3. 列名不能直接使用数据库保留字；
4. 日期类型字段推荐以 date 结尾的名字命名，时间类型的字段推荐以 time 结尾的名字命名。
5. 约定特殊含义字段，如：pid 表示分类表的父级 ID，外键字段采用 fk_xxx_id 命名法，如 fk_category_id。
6. 自动时间戳字段推荐名称：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 系统关键词一律大写。