Fork me on GitHub

Mysql数据库设计命名规范

数据库涉及字符规范

采用 26 个英文字母(区分大小写)和 0-9 这十个自然数,加上下划线‘_’组成,共37个字符。不能出现其他字符(注释除外)。

注意事项:

  1. 以上命名都不得超过 30 个字符的系统限制。变量名的长度限制为29(不包括标识字符@)。

  2. 数据对象、变量的命名都采用英文字符,禁止使用中文命名。绝对不要在对象名的字符之间留空格。

  3. 小心保留词,要保证你的字段名没有和保留词、数据库系统或者常用访问方法冲突, 当表名或字段名乃至数据库名和保留字冲突时,在sql语句里可以用撇号(`)括起来。

  4. 保持字段名和类型的一致性,在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数,那在另一个表里也要保证是整型。

数据库命名规范

数据库名使用小写英文以及下划线组成(公司简称+项目名)。比如:

baidu_oa
ailibaba_cloud

备份数据库名使用正式库名加上备份时间组成,如:

dbname_20150206

数据库创建时,显式指定字符集。

数据库表命名规范

数据表名使用小写英文以及下划线组成(项目名+表信息),并且创建数据库时,显式指定默认字符集。

比如:

oa_user
oa_workflow_steps

备份数据表名使用正式表名加上备份时间组成,如:

oa_user_20150206
oa_workflow_step_20150206

字段命名规范

字段名称使用单词组合完成,首字母小写,单词之间用“_”分隔,最好是带表名前缀,自增id最好创建与业务无关.

oa_user表的字段:

user_id
user_name

表与表之间的相关联字段要用统一名称。

索引命名规范

索引名称为idx_列名表名缩写,唯一索引名称为uniq列名_表名缩写,其中表名和关联字段名如果过长,可以取表名、关联字段名的前 5 个字母,如果表名、关联字段为多个单词组合,可以取前一个单词,外加后续其它单词的首字母作为字段名。

如:

普通索引:idx_user_name_oa

唯一索引:uqidx_user_name_oa

注意事项:

  • 单个索引长度不超过64KB
  • 单个表索引不超过8

字段类型规范

规则:用尽量少的存储空间来存数一个字段的数据。

  1. char(n)varchar(n)中括号中n代表字符的个数,并不代表字节个数,所以当使用了中文的时候(UTF8)意味着可以插入m个中文,但是实际会占用m*3个字节。

  2. 同时charvarchar最大的区别就在于char不管实际value都会占用n个字符的空间,而varchar只会占用实际字符应该占用的空间+1,并且实际空间+1<=n。

  3. 超过charvarcharn设置后,字符串会被截断。

  4. char的上限为255字节,varchar的上限65535字节,text的上限为65535

  5. char在存储的时候会截断尾部的空格,varchartext不会。

  6. varchar会使用1-3个字节来存储长度,text不会。

ValueCHAR(4)Storage RequiredVARCHAR(4)Storage Required
‘’‘ ‘4 bytes‘’1 byte
‘ab’‘ab ‘4 bytes‘ab’3 bytes
‘abcd’‘abcd’4 bytes‘abcd’5 bytes
‘abcdefgh’‘abcd’4 bytes‘abcd’5 bytes

总体来说:

  • char,存定长,速度快,存在空间浪费的可能,会处理尾部空格,上限255
  • varchar,存变长,速度慢,不存在空间浪费,不处理尾部空格,上限65535,但是有存储长度实际65532最大可用。
  • text,存变长大数据,速度慢,不存在空间浪费,不处理尾部空格,上限65535,会用额外空间存放数据长度,顾可以全部使用65535

接下来,我们说下这个场景的问题:

varchar(n)后面的n非常大的时候我们是使用varchar好,还是text好呢?

这是个明显的量变引发质变的问题。我们从2个方面考虑,第一是空间,第二是性能。

首先从空间方面:

从官方文档中我们可以得知当varchar大于某些数值的时候,其会自动转换为text,大概规则如下:

  • 大于varchar(255)变为 tinytext
  • 大于varchar(500)变为 text
  • 大于varchar(20000)变为 mediumtext

所以对于过大的内容使用varchartext没有太多区别。

比如能用int的就不用char或者varchar,能用varchar(20)的就不用varchar(255)

时间戳字段尽量用int型。

尽量避免大字段blobtextlongtext,如强制需要大字段,不要集中存放在一个表中,分多表存放。查询语句where条件不要落在大字段上。

相关文档:

操作规范

  • 创建表时需添加表级别和列级别注释,显示指定存储引擎、默认字符集、自增开始值,id列无特殊要求,建议使用int值。

  • 如无备注,则表中的第一个id字段一定是主键且为自动增长;

  • 如无备注,则数值类型的字段请使用UNSIGNED属性;

  • 如无备注,所有字段都设置NOT NULL,并设置默认值;

  • 如无备注,所有的布尔值字段,如is_hotis_deleted,都必须设置一个默认值,并设为0

  • 所有的数字类型字段,都必须设置一个默认值,并设为0

  • 针对varchar类型字段的程序处理,请验证用户输入,不要超出其预设的长度;

  • 建表时将数据字典中的字段中文名和属性备注写入数据表的备注中(“PK、自动增长”不用写);

  • 如无说明,建表时一律采用innodb引擎;

  • 尽量采用批量SQL语句,减少与数据库交互次数;

  • 提交线上建表改表需求,必须详细注明涉及到的所有SQL语句(包括INSERTDELETEUPDATE),便于DBA进行审核和优化。

数据库用户创建规范

采用26个英文字母(区分大小写)和0-9这十个自然数,加上下划线‘_’组成,共37个字符。不能出现其他字符(注释除外)。

注意事项:

  • 以上命名都不得超过16个字符的系统限制。
  • 数据库用户一律采用小写,由以下几个部分组成,使用人员项目名连接途径_用户权限组成;

    例如:管理平台账号,d_mg_web_dmld_mg_pc_web_dml

其他设计技巧

  1. 避免使用存储过程、触发器、视图、自定义函数等,这些高级特性有性能问题,以及未知BUG较多。业务逻辑放到数据库会造成数据库的DDLSCALE OUTSHARDING等变得更加困难。

  2. 分区表对分区键有严格要求;分区表在表变大后,执行DDLSHARDING、单表恢复等都变得更加困难。因此禁止使用分区表,并建议业务端手动SHARDING

  3. 使用常用英语(或者其他任何语言)而不要使用拼音首字母缩写。

  4. 将大字段、访问频率低的字段拆分到单独的表中存储,分离冷热数据, 有利于有效利用缓存,防止读入无用的冷数据,较少磁盘IO,同时保证热数据常驻内存提高缓存。

  5. 禁止有super权限的应用程序账号存在, 安全第一。super权限会导致read only失效,导致较多诡异问题而且很难追踪。

  6. 不要在MySQL数据库中存放业务逻辑, 数据库是有状态的服务,变更复杂而且速度慢,如果把业务逻辑放到数据库中,将会限制业务的快速发展。建议把业务逻辑提前,放到前端或中间逻辑层,而把数据库作为存储层,实现逻辑与存储的分离。

  7. 不建议使用enum,set,blob,text字段,如非用blobtext字段,请将blobtext内容存放到文件系统中,数据库中只存放文件系统的url地址,或将该类字段单独存放到一张小表中(首推第一种方法)。

附:建表示例

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `manager_test` (
  `id` int(20) unsigned NOT NULL AUTO_INCREMENT,
  `staff_id` int(11) NOT NULL COMMENT '操作人员id',
  `staff_name` varchar(50) NOT NULL COMMENT '人员名称',
  `url` varchar(200) NOT NULL COMMENT '操作路径',
  `method` varchar(10) NOT NULL COMMENT '操作方式',
  `params` varchar(10)NOT NULL COMMENT '操作参数',
  `ip` varchar(20) NOT NULL COMMENT 'ip',
  `time` int(11) NOT NULL COMMENT '操作时间',
  PRIMARY KEY (`id`),
  KEY `idx_staff_id_ct` (`staff_id `)
) ENGINE=InnoDB AUTO_INCREMENT=1DEFAULT CHARSET=utf8

数据库用户命名规范

用户命名规则,分为四段,每段规则如下

示例:d_zq_pc_dml

  • 第一段:环境名(d:develop,t:test,p:product)

  • 第二段:项目名首字母

  • 第三段:连接源(pc:桌面终端,web:程序)

  • 第四段:权限(sel:select,dml:select,insert,update,delete)

注意事项:

禁止程序具有filesuper权限。




-------------文章结束啦 ~\(≧▽≦)/~ 感谢您的阅读-------------

SakuraTears wechat
扫一扫关注我的公众号
您的支持就是我创作的动力!
0%