SQL开发规范

1、数据库建模遵循9个基本原则 所有表必须要有独立于业务的自增主键 统一命名为id，类型为bigint；所有表必须要有如下系统字段，

Name	Code	DataType	Length	Not Null	Default
主键	id	Bigint		是	（表中的第一个字段）
……				是	其他业务字段
删除标识	is_delete	tinyint	1	是	0（未删除）
创建时间	create_time	DateTime		是	记录创建时间
更新时间	update_time	DateTime		是	记录更新时间
创建人	create_user	Varchar（50）	50	是
更新人	update_user	Varchar（50）	50	是
时间戳	ts	timestamp		是	当前时间：数据库自动维护

 

2、数据库名，表名，字段名命名统一使用小写字母，下划线不超过4个。

3、最小原则：尽可能用存储空间小的类型；

4、最短原则：在满足业务需求的条件下使用最小长度；

5、 Not null原则：所有字段一律设为not null，并设置一个默认值；

Null和not Null

1)、 清楚“空值” 和 “NULL” 的概念 数据库的字段ID设为NOT NULL, 仅仅说明该字段不能为NULL, 也就是说只有在 INSERT INTO table(ID) VALUES(NULL);这种情况下数据库会报错， INSERT INTO table(ID) VALUES( ‘ ‘);数据库系统会根据ID设的缺省值填充 2)、 含有空值的列很难进行查询优化，而且对表索引时不会存储NULL值的， 所以如果索引的字段可以为NULL，索引的效率会下降很多。 因为它们使得索引、索引的统计信息以及比较运算更加复杂。 你应该用0、一个特殊的值或者一个空串代替空值。

6、禁止使用BLOB/TEXT/BIT类型，禁止在数据库中保存图片、xml等大资源；

7、尽量使用数值型字段，若只含数值信息的字段尽量不要设计为字符型；

尽量使用数值型字段，若只含数值信息的字段尽量不要设计为字符型; INT[M]，M值代表什么含义？ 注意数值类型括号后面的数字只是表示宽度而跟存储范围没有关系， 比如INT(3)默认显示3位，空格补齐，超出时正常显示 为什么建议使用TIMESTAMP来存储时间而不是DATETIME？ DATETIME和TIMESTAMP都是精确到秒，优先选择TIMESTAMP， 因为TIMESTAMP只有4个字节，而DATETIME8个字节。 同时TIMESTAMP具有自动赋值以及自动更新的特性。

8、禁止使用外键；

9、字符集统一使用UTF-8；

10、表的列数不超过40列，表中的索引不超过6个，表名长不超过50个字符

11、索引命名为：idx_字段名1_字段名2_......

12、经常出现在Where子句中，并且选择性高的字段建索引(status)；

13、索引应该建在小字段上，对于超长字段（比如varchar）取前50的字符建索引(前缀索引)；

14、复合索引的建立需要进行仔细分析，尽量考虑用单字段索引代替

A、正确选择复合索引中的主列字段，一般是选择性较好的字段；

B、 复合索引的几个字段是否经常同时以AND方式出现在Where子句中？ 单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；

C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；

D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；

E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引

复合索引=联合索引 WHERE 条件 对于多个条件 1 2条件 都符合 但是第一个值才是有序的 如果用第二个字段的条件 则不能使用索引 

15、删除无用的索引，避免对执行计划造成负面影响；

16、 SQL语句中IN包含的值不超过100(生产库)。

17、 UPDATE、DELETE语句不使用LIMIT。

18、 WHERE条件中使用合适的类型，避免MySQL进行隐式类型转化。 

19、 SELECT语句只获取需要的字段(万能SQL)。

20、 SELECT、INSERT语句显式的指明字段名称，不使用SELECT *，但不适用于INSERT INTO table()。

21、使用SELECT column_name1, column_name2 FROM table WHERE [condition]而不是SELECT column_name1 FROM table WHERE [condition]和SELECT column_name2 FROM table WHERE [condition]。 WHERE条件中的非等值条件（IN、BETWEEN、<、<=、>、>=）会导致后面的条件是使用不了索引。

22、避免使用存储过程、触发器、函数等，容易将业务逻辑和DB耦合在一起，并且MySQL的存储过程、触发器、函数中存在一定的bug。

23 、生产系统中尽量避免使用JOIN  SQL中不超过两个JOIN。  

24、使用合理的SQL语句减少与数据库的交互次数。

25、不使用ORDER BY RAND()，使用其他方法替换。

因为ORDER BY rand()会将数据从磁盘中读取，进行排序， 会消耗大量的IO和CPU，可以在程序中获取一个rand值，然后通过在从数据库中获取对应的值。

26、统计表中记录数时使用COUNT(*)，而不是COUNT(primary_key)和COUNT(1)。

count详解： count(*)将返回表格中所有存在的行的总数包括值为null的行， 然而count(列名)将返回表格中除去null以外的所有行的总数(有默认值的列也会被计入）. distinct 列名,得到的结果将是除去值为null和重复数据后的结果count(1)与count(*)比较： 如果你的数据表没有主键，那么count(1)比count(*)快 如果有主键的话，那主键（联合主键）作为count的条件也比count(*)要快 如果你的表只有一个字段的话那count(*)就是最快的 count(*) count(1) 两者比较: 主要还是要count(1)所相对应的数据字段。 如果count(1)是聚集索引,id,那肯定是count(1)快。但是差的很小。 因为count(*),自动会优化指定到那一个字段。所以没必要去count(?)，用count(*),sql会帮你完成优化.

27、 生产系统中不能使用子查询  不能使用EXISTS() NOT EXISTS()

我们公司开发中遵循的一些SQL开发规范，不是什么标准，也不是什么手册。