MySQL数据库设计命名规范-实用小技巧

一、数据库涉及字符规范

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

注意事项 :

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

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

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

起来。

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

二、数据库命名规范

数据库名使用小写英文以及下划线组成 (credithc+ 项目名 ). 比如 :

credithc_oa

credithc_hyr

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

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_oa_user_name

唯一索引： uidx_oa_user_name

注意事项：

单个索引长度不超过 64KB

单个表索引不超过 8 个

六、字段类型规范

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

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

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

3 、超过 char 和 varchar 的 n 设置后，字符串会被截断。

4 、 char 的上限为 255 字节， varchar 的上限 65535 字节， text 的上限为 65535 。

5 、 char 在存储的时候会截断尾部的空格， varchar 和 text 不会。

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

总体来说：

1 、 char ，存定长，速度快，存在空间浪费的可能，会处理尾部空格，上限 255 。

2 、 varchar ，存变长，速度慢，不存在空间浪费，不处理尾部空格，上限 65535 ，但是有存储长度实际 65532 最大可用。

3 、 text ，存变长大数据，速度慢，不存在空间浪费，不处理尾部空格，上限 65535 ，会用额外空间存放数据长度，顾可以全部使用 65535 。

接下来，我们说下这个场景的问题：

当 varchar （ n ）后面的 n 非常大的时候我们是使用 varchar 好，还是 text 好呢？这是个明显的量变引发质变的问题。我们从 2 个方面考虑，第一是空间，第二是性能。

首先从空间方面：

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

○ 大于 varchar （ 255 ）变为 tinytext

○ 大于 varchar （ 500 ）变为 text

○ 大于 varchar （ 20000 ）变为 mediumtext

所以对于过大的内容使用 varchar 和 text 没有太多区别

1 ，能用 int 的就不用 char 或者 varchar ，能用 varchar(20) 的就不用 varchar(255)

2 ，时间戳字段尽量用 int 型 ,

3 ，金额字段尽量使用decimal类型：decimal默认为decimal(10,0)。float占4字节，double占8字节，decimail(M,D)占M+2个字节。不定义fload, double的精度和标度 时，存储按给出的数值存储（和OS以及当前硬件有关），由于误差，若数值比较，最好使用decimal类型。

4 ，ip字段用int (11) unsigned ：入库时需要把ip转换成int类型：select inet_aton('10.55.22.100'); 读取时需要把int类型的信息转换成ip：select

inet_ntoa('171382372'); ,

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

6 ，不能在数据库中存放图片。

七、操作规范

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

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

如无备注，则数值类型的字段请使用 UNSIGNED 属性；

如无备注，所有字段都设置 NOT NULL ，并设置默认值；

如无备注，所有的布尔值字段，如 is_hot 、 is_deleted ，都必须设置一个默认值，并设为 0 ；

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

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

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

如无说明，建表时一律采用 innodb 引擎； .

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

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

八、数据库用户创建规范

采用 26 个英文字母 ( 区分大小写 ) 和 0-9 这十个自然数 , 加上下划线 '_' 组成 , 共 37 个字符 . 不能出现其他字符 ( 注释除外 ). 用户命名规则，分为四段，每段规则如下

示例： d_zq_pc_dml

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

第二段：项目名首字母

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

第四段：权限 (sel:select ， dml:select ， insert ， update ， delete)

注意事项 :

1) 以上命名都不得超过 16 个字符的系统限制 .

2) 数据库用户一律采用小写

3) 禁止程序具有 file 、 super 权限

九、其他设计技巧

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

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

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

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

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

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

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

附：建表示例

CREATE TABLE `crm_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=1 DEFAULT CHARSET=utf8