1.【强制】不要使用count(列名)或count(常量)来替代count(*),count(*)就是SQL92定义的标准统计行数的语法,跟数据库无关,跟NULL和非NULL无关。
说明:count(*)会统计值为NULL的行,而count(列名)不会统计此列为NULL值的行。
主要用法有COUNT(*)、COUNT(字段)和COUNT(1)。
因为COUNT(*)是SQL92定义的标准统计行数的语法,所以MySQL对他进行了很多优化,MyISAM中会直接把表的总行数单独记录下来供COUNT(*)查询,而InnoDB则会在扫表的时候选择最小的索引来降低成本。当然,这些优化的前提都是没有进行where和group的条件查询。
在InnoDB中COUNT(*)和COUNT(1)实现上没有区别,而且效率一样,但是COUNT(字段)需要进行字段的非NULL判断,所以效率会低一些。
因为COUNT(*)是SQL92定义的标准统计行数的语法,并且效率高,所以请直接使用COUNT(*)查询表的行数!
2.【强制】count(distinct col) 计算该列除NULL之外的不重复数量。注意 count(distinct col1, col2) 如果其中一列全为NULL,那么即使另一列有不同的值,也返回为0。
3.【强制】当某一列的值全是NULL时,count(col)的返回结果为0,但sum(col)的返回结果为NULL,因此使用sum()时需注意NPE问题。
正例:
--可以使用如下方式来避免sum的NPE问题:
SELECT IFNULL(SUM(column), 0) FROM table;
IFNULL() 函数用于判断第一个表达式是否为 NULL,如果为 NULL 则返回第二个参数的值,如果不为 NULL 则返回第一个参数的值。
4.【强制】使用ISNULL()来判断是否为NULL值。
说明:
NULL与任何值的直接比较都为NULL。
1) NULL<>NULL的返回结果是NULL,而不是false。
2) NULL=NULL的返回结果是NULL,而不是true。
3) NULL<>1的返回结果是NULL,而不是true。
<> 表示不等于
反例:
--在SQL语句中,如果在null前换行,影响可读性。
select * from table where column1 is null and column3 is not null;
而ISNULL(column)是一个整体,简洁易懂。从性能数据上分析,ISNULL(column)执行效率更快一些。
5.【强制】对于数据库中表记录的查询和变更,只要涉及多个表,都需要加表名(或别名)进行限定。
说明:
多表join后作为条件进行查询记录、更新记录、删除记录时,如果出现没有限定表名(或别名)的列名在多个表中均有存在,那么会抛出异常。
正例:
select t1.name from table_first as t1 , table_second as t2 where t1.id=t2.id;
反例:
在某业务中,由于多表关联查询语句没有加表名(或别名)的限制,正常运行两年后,最近在某个表中增加一个同名字段,在预发布环境做数据库变更后,线上查询语句全部出现出1052异常:Column 'name' in field list is ambiguous,导致票务交易下跌。
6.【强制】在代码中写分页查询逻辑时,若count为0应直接返回,避免执行后面的分页语句。
7.【强制】不得使用外键与级联,一切外键概念必须在应用层解决。
说明:
(概念解释)学生表中的student_id是主键,那么成绩表中的student_id则为外键。如果更新学生表中的student_id,同时触发成绩表中的student_id更新,则为级联更新。外键与级联更新适用于单机低并发,不适合分布式、高并发集群;级联更新是强阻塞,存在数据库更新风暴的风险;外键影响数据库的插入速度。
8.【强制】禁止使用存储过程,存储过程难以调试和扩展,更没有移植性。
存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象。
CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body proc_parameter: [ IN | OUT | INOUT ] param_name type characteristic: COMMENT 'string' | LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } routine_body: Valid SQL routine statement [begin_label:] BEGIN [statement_list] …… END [end_label]
9.【强制】IDB数据订正(特别是删除或修改记录操作)时,要先select,避免出现误删除,确认无误才能提交执行。
10.【推荐】SQL语句中表的别名前加as,并且以t1、t2、t3、…的顺序依次命名。
说明:
1)别名可以是表的简称,或者是根据表出现的顺序,以t1、t2、t3的方式命名。
2)别名前加as使别名更容易识别。
正例:
select t1.name from table_first as t1, table_second as t2 where t1.id=t2.id;