SQL开发规范及常见问题

为了项目的稳定，代码的高效，管理的便捷，在开发团队内部会制定各种各样的规范。
数据库设计过程中表、字段等的命名规范也算是设计规范的一部分，不过设计规范更多的是为了确保数据库设计的合理性、为了项目最终的协调稳定性，而命名规范则更多的是为了确保设计的正式和统一。
设计规范更多的是为了合理，命名规范更多的是为了统一，团队协作中，统一在某种程度上比局部设计开发的好坏更重要。违反了约定，局部设计开发的再好，反而可能影响到项目整体的稳定协调。

这篇文章主要是借鉴他人之规范，总结出一套自己的开发规范，以供我这样的新手使用，用来规范开发，如果有什么好的提议，请拍砖交流，本篇文章长期更新~

基本规范

• 数据库、表、字段等所有名称的可用字符范围为：A-Z，a-z，0-9和_下划线，首位字符必须是英文字母
• 数据库及表名均不允许出现数字，字段名除非特殊情况不允许出现数字
• 命名使用具有意义的英文词汇，词汇间用下划线分隔，禁止使用汉语拼音，且均使用单数名
• 数据库对象命名不能直接使用数据库保留关键字，但分段中可以使用。如USER不能用于表名、列名等，但是USER_NAME可以用于列名，USER_INFO也可以用于表名
• MySQL数据库、表、字段等名称统一使用小写，单词间用_下划线分隔
• Oracle表、字段等名称统一使用大写，单词间用_下划线分隔
• SQLServer数据库、表等名称采用Pascal命名法，字段名称采用Camel命名法，大小写字母混排

表约定

• 同一个模块的表尽可能使用相同的前缀，表名称尽可能表达含义
• 多个单词以下划线_分隔
• 表名不能超过30个字符
• 普通表名以T_开头，表示为table，命名规则为t_模块名（或有意义的简写）_+table_name
• 建议：表的命名方式建议采用T_MOUDLE_ENTITY方式。MOUDLE表示数据库对象所属的系统、模块名或者主题分类。ENTITY表示目的表代表的实体名称。MOUDLE 只能由一个单词组成，ENTITY可以根据需要有多个单词组成。
• 临时表(运营、开发或数据库人员临时用作临时进行数据采集用的中间表)命名规则：加上tmp前缀和8位时间后缀（tmp_test_user_20181109）
• 备份表（DBA备份用作保存历史数据的中间表）命名规则：加上bak前缀和8位时间后缀（bak_test_user_20181109）

列约定

• 列名无需使用前缀，如使用数据类型编码作为前缀；
• 列名字母全部大写；
• 建议：日期类型字段推荐以“_DATE”结尾的名字命名，时间类型的字段推荐以“_TIME”结尾的名字命名。
• 建议：主键列命名为“ID”或者以 “_ID”为后缀进行命名。对于需要在其他表中引用的主键字段以“_ID”后缀方式命名，普通表主键无需加后缀。如基础信息表的主键一般应命名为“ENTITIE_ID”方式，而通常业务数据明细表的主键则直接命名为“ID”
• 如无备注，则表中的第一个id字段一定是主键且为自动增长
• 如无备注，则数值类型的字段请使用UNSIGNED属性
• 如无备注，排序字段order_id在程序中默认使用降序排列
• 如无备注，所有字段都设置NOT NULL，并设置默认值
• 如无备注，所有的布尔值字段，如is_hot、is_deleted，都必须设置一个默认值，并设为0
• 所有的数字类型字段，都必须设置一个默认值，并设为0
• 针对varchar类型字段的程序处理，请验证用户输入，不要超出其预设的长度

一些开发问题

util.Date类型数据插入MySQL时数据库时日期少一天

在使用高版本的MySQL时(我用的是5.7)，会发现插入时间的时候总是少10多个小时。这是因为高版本的驱动需要指定时区，将默认的serverTimezone=UTC改为Asia/Shanghai或者HongKong就可以了

jdbc:mysql://localhost:3306/holiday?useSSL=false&serverTimezone=Asia/Shanghai

MySQL中文乱码

问题描述：前后端分离项目，后端调用写入数据库的操作时中文乱码。
排查：前端传的中文是正常的；在控制台打印完整带参数SQL语句中也是正常显示中文，于是问题只能是数据库软件的编码问题。
在mysql里查询下

show variables like ‘%char%’；

会出来一堆，大部分是utf8，但其中:

character-set-server=latin1
character-set-databases=latin1

很显然，就是这儿。MySQL5.7 刚刚安装好后，配置文件是 /etc/mysql/mysql.conf.d/mysqld.cnf，而且并没有my.cnf这个文件。似乎以前版本的mysql，安装后的配置文件都是/etc/mysql/my.cnf
打开/etc/mysql/mysql.conf.d/mysqld.cnf，发现第一句话就是：可以把这个拷贝成/etc/mysql/my.cnf

cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/my.cnf

然后打开my.cnf，添上：

[client]
default-character-set=utf8

在[mysqld]这段里面，添上

character-set-server=utf8

保存并重启MySQL服务就行了。

不要在 MySQL 中使用utf8

在做小程序后端的时候有一个表是用来存储用户名的，刚好我的用户名含有emoji，结果在MySQL上emoji变成了问号，一查才发现原来MySQL的utf8不是真的UTF-8。

“utf8”只支持每个字符最多三个字节，而真正的 UTF-8 是每个字符最多四个字节。
MySQL 一直没有修复这个 bug，他们在 2010 年发布了一个叫作“utf8mb4”的字符集，绕过了这个问题。

而emoji占有4个字节的存储空间，所以自然保存不了。
所以尽量使用uft8mb4吧。