前言
与大多数编程语言一样,我们在编写SQL语句时可以遵循一套合适的规范来提升代码的可读性、易维护性和一致性,这在团队中是提升效率的手段之一。虽然SQL官方并没有特意给出一套对应的规范,但是从SQL语句发展到现在,我相信有很多组织和个人早已有了对应的一套规范,接下来我们可以从中总结一些比较常用的作为自己在工作中的指南。
一般规则
- 使用一致的、描述性的名称;
- 使用空格 2 2 2 个或者 4 4 4 个(尽量保持一致),避免使用 T a b Tab Tab 缩进;
- 加入必要的注释,块注释 / ∗ ∗ / /* */ /∗∗/ ,行注释为 − − -- −− ,并在末尾换行;
- 使用单引号
'
作为被引号包裹的标识符; - 运算符前后添加空格,逗号
,
后添加空格,避免行尾有空格; - 每行不超过80个字符。
命名惯例
- 避免名称和保留字(关键字)一样;
- 关键词、函数名称采用大写,字段名、表名采用小蛇式(全小写词并以下划线
_
进行连接); - 名称要以字母开头,不能以下划线结尾,名称中仅可以字母、数字和下划线组成;
- 不要在名称中出现连续下划线
__
,这样很难进行辨认; - 尽量避免使用缩写(例如:使用
tt
代替temp_table
),如若使用尽量让缩写简明易懂; - 字段名总是使用单数。
对齐和换行
-
避免川流式对齐代码:
/* Good */ SELECT id FROM table_name WHERE column = "col" ;
/* Bad */ SELECT id FROM talbe_name WHERE column = "col" ;
-
多个元素组合无法呈现在一行中时,应将第一个元素另起一行:
/* Good */ SELECT CASE postcode WHEN 'BN1' THEN 'Brighton' WHEN 'EH1' THEN 'Edinburgh' END AS city FROM table_name ;
/* Bad */ SELECT CASE postcode WHEN 'BN1' THEN 'Brighton' WHEN 'EH1' THEN 'Edinburgh' END AS city FROM table_name ;
-
由括号构成的多行,结尾括号应单独一行:
/* Good */ SELECT id FROM table_name WHERE postcode IN ( 'looooooooooooooooooooooooong_BN1', 'loooooooooooooooooooooooooog_EH1' )
/* Bad */ SELECT id FROM table_name WHERE postcode IN ('looooooooong_BN1', 'looooooooong_EH1')
-
多行采用右侧逗号和左侧关键字连接:
/* Good */ SELECT id, name FROM talbe_name WHERE id > 1 AND name LIKE "%Tom%" ;
/* Bad */ SELECT id , name FROM table_name WHERE id > 1 AND name LIKE "%Tom%" ;
-
根关键词建议单独一行,多个参数单独一行:
/* Good */ SELECT id, name FROM table_name WHERE id > 1 AND name LIKE "%Tom%" LIMIT 10 ;
/* Acceptable */ SELECT id, name FROM table_name WHERE id > 1 AND name LIKE "%Tom%" LIMIT 10 ;
/* Bad */ SELECT id, name FROM table_name WHERE id > 1 AND name LIKE "%Tom%" LIMIT 10 ;
明确指定
-
使用
AS
明确指定别名,而非隐式:/* Good */ SELECT table_name_1.id AS user_id, table_name_2.name AS user_name FROM looooooooong_table_name_1 AS table_name_1 LEFT JOIN looooooooong_table_name_2 AS table_name_2 ON table_name_1.id = table_name_2.id ;
/* Bad */ SELECT table_name_1.id user_id, table_name_2.name user_name FROM looooooooong_table_name_1 table_name_1 LEFT JOIN looooooooong_table_name_2 table_name_2 ON table_name_1.id = table_name_2.id ;
-
避免使用隐式关联:
/* Good */
SELECT
table_name_1.id,
table_name_2.name
FROM
table_name_1
INNER JOIN
table_name_2
ON
table_name_1.id = table_name_2.id
;
/* Bad */
SELECT
table_name_1.id,
table_name_2.name
FROM
table_name_1,
table_name_2
ON
table_name_1.id = table_name_2.id
;
-
明确关联类型:
/* Good */ SELECT table_name_1.id, table_name_2.name FROM table_name_1 INNER JOIN table_name_2 ON table_name_1.id = table_name_2.id ;
/* Bad */ SELECT table_name_1.id, table_name_2.name FROM table_name_1 JOIN table_name_2 ON table_name_1.id = table_name_2.id ;
-
明确指定分组列:
/* Good */ SELECT submission_date, normalized_channel IN ('nightly', 'aurora', 'beta') AS is_prerelease, COUNT(*) AS count FROM telemetry.clients_daily WHERE submission_date > '2019-07-01' GROUP BY submission_date, is_prerelease ;
/* Bad */ SELECT submission_date, normalized_channel IN ('nightly', 'aurora', 'beta') AS is_prerelease, COUNT(*) AS count FROM telemetry.clients_daily WHERE submission_date > '2019-07-01' GROUP BY 1, 2 ;
子查询
-
尽量使用 Common Table Expressions (CTEs) 而非子查询:
/* Good */ WITH sample AS ( SELECT client_id, submission_date FROM main_summary WHERE sample_id = '42' ) SELECT * FROM sample LIMIT 10
/* Bad */ SELECT * FROM ( SELECT client_id, submission_date FROM main_summary WHERE sample_id = '42' ) LIMIT 10
-
尽量在 CTEs 中处理查询而非主语句中:
/* Good */ WITH backings_per_category AS ( SELECT ... ), backers AS ( SELECT backings_per_category.backer_id, COUNT(backings_per_category.id) AS projects_backed_per_category INNER JOIN ksr.users AS users ON users.id = backings_per_category.backer_id GROUP BY backings_per_category.backer_id ), backers_and_creators AS ( ... ) SELECT * FROM backers_and_creators;
/* Bad */ WITH backings_per_category AS ( SELECT ... ), backers AS ( SELECT backer_id, COUNT(backings_per_category.id) AS projects_backed_per_category ), backers_and_creators AS ( ... ) SELECT * FROM backers_and_creators INNER JOIN backers ON backers_and_creators ON backers.backer_id = backers_and_creators.backer_id
其它
- 尽量使用
!=
替代<>
表示不等于; - 尽量使用
BETWEEN
而不是多个AND
语句; - 尽量使用
IN
而不是多个OR
语句; - 尽量避免使用
SELECT * FROM ...
全局扫描; - 尽量避免使用无意义的别名,例如
a、b、c
等。
参考目录
阅读更多规范请大家移步以下链接: