SQL 语句写的烂怎么办？我帮你解决😇

使用规范语法和格式，不仅可以使得代码简洁易懂。更有利于别人后来对代码的增加迭代。本文出于对MySQL在代码中的语法规范和格式做了如下梳理。

1. 使用小写的SQL

它与大写SQL一样易读，而且不必一直按住shift键。

-- Good
select * from users

-- Bad
SELECT * FROM users

-- Bad
Select * From users

2. 单行查询 和 多行查询

只有当你选择:

• 所有列(*)或选择1或2列
• 您的查询没有额外的复杂性

-- Good
select * from users

-- Good
select id from users

-- Good
select id, email from users

-- Good
select count(*) from users

这样做的原因很简单，当所有内容都在一行时，仍然很容易阅读。但一旦你开始添加更多的列或更复杂的代码，如果是多行代码就更容易阅读:

-- Good
select
    id,
    email,
    created_at
from users

-- Good
select *
from users
where email = 'example@domain.com'

对于具有1或2列的查询，可以将这些列放在同一行上。对于3+列，将每个列名放在它自己的行上，包括第一项:

-- Good
select id, email
from users
where email like '%@gmail.com'

-- Good
select user_id, count(*) as total_charges
from charges
group by user_id

-- Good
select
    id,
    email,
    created_at
from users

-- Bad
select id, email, created_at
from users

-- Bad
select id,
    email
from users

3. 左对齐

-- Good
select id, email
from users
where email like '%@gmail.com'

-- Bad
select id, email
  from users
 where email like '%@gmail.com'

4. 使用单引号

一些SQL语法，如BigQuery支持使用双引号，但是对于大多数语法，双引号最终将引用列名。因此，单引号更可取:

-- Good
select *
from users
where email = 'example@domain.com'

-- Bad
select *
from users
where email = "example@domain.com"

5.!= 优于 <>

因为!=读起来像“not equal”，更接近我们大声说出来的方式。

-- Good
select count(*) as paying_users_count
from users
where plan_name != 'free'

6. 逗号放在行尾

-- Good
select
    id,
    email
from users

-- Bad
select
    id
    , email
from users

7.缩进条件

如果只有一个条件，请将其保留在与以下相同的行

select email
from users
where id = 1234

当有多个缩进时，将每个缩进比where更深一层。将逻辑运算符放在前一个条件的末尾:

select id, email
from users
where 
    created_at >= '2019-03-01' and 
    vertical = 'work'

8.避免在括号内使用空格

-- Good
select *
from users
where id in (1, 2)

-- Bad
select *
from users
where id in ( 1, 2 )

9.in 查询 将值多个缩进行

-- Good
select *
from users
where email in (
    'user-1@example.com',
    'user-2@example.com',
    'user-3@example.com',
    'user-4@example.com'
)

10.表名应该是名词的复数形式

-- Good
select * from users
select * from visit_logs

-- Bad
select * from user
select * from visitLog

11. 字段使用 snake_case名字

-- Good
select
    id,
    email,
    timestamp_trunc(created_at, month) as signup_month
from users

-- Bad
select
    id,
    email,
    timestamp_trunc(created_at, month) as SignupMonth
from users

12.列名约定

• 布尔字段 的前缀应该是 is_ 、 has_  或   does_ 。例如，is_customer、has_unsubscribe等
• 日期的字段 应该以 _date 作为后缀。例如,report_date。
• 日期+时间字段 应以 _at 为后缀。例如，created_at，posted_at等。

13. 列顺序约定

首先放置主键，然后是外键，然后是所有其他列。如果表有任何系统列（created_at，updated_at，is_deleted等），将它们放在最后。

-- Good
select
    id,
    name,
    created_at
from users

-- Bad
select
    created_at,
    name,
    id,
from users

14. Include inner for inner joins

最好显式，以便连接类型非常清楚:

-- Good
select
    email,
    sum(amount) as total_revenue
from users
inner join charges on users.id = charges.user_id

-- Bad
select
    email,
    sum(amount) as total_revenue
from users
join charges on users.id = charges.user_id

15.对于连接条件，设置后立即放置第一个引用的表

通过这种方式，它可以更容易地确定您的连接是否会导致结果散开

-- Good
select
    ...
from users
left join charges on users.id = charges.user_id
-- primary_key = foreign_key --> one-to-many --> fanout

select
    ...
from charges
left join users on charges.user_id = users.id
-- foreign_key = primary_key --> many-to-one --> no fanout

-- Bad
select
    ...
from users
left join charges on charges.user_id = users.id

16.单个连接条件应与连接位于同一行

-- Good
select
    email,
    sum(amount) as total_revenue
from users
inner join charges on users.id = charges.user_id
group by email

-- Bad
select
    email,
    sum(amount) as total_revenue
from users
inner join charges
on users.id = charges.user_id
group by email

当你有多个连接条件时，将每个条件放在它们自己的缩进行中：

-- Good
select
    email,
    sum(amount) as total_revenue
from users
inner join charges on 
    users.id = charges.user_id and
    refunded = false
group by email

17.避免别名表

-- Good
select
    email,
    sum(amount) as total_revenue
from users
inner join charges on users.id = charges.user_id

-- Bad
select
    email,
    sum(amount) as total_revenue
from users u
inner join charges c on u.id = c.user_id

唯一的例外是，当需要多次连接到一个表并需要区分它们时。

18. 除非必须，否则不要包含表名

-- Good
select
    id,
    name
from companies

-- Bad
select
    companies.id,
    companies.name
from companies

19.始终重命名聚合和函数包装的参数

-- Good
select count(*) as total_users
from users

-- Bad
select count(*)
from users

-- Good
select timestamp_millis(property_beacon_interest) as expressed_interest_at
from hubspot.contact
where property_beacon_interest is not null

-- Bad
select timestamp_millis(property_beacon_interest)
from hubspot.contact
where property_beacon_interest is not null

20.明确布尔条件

-- Good
select * from customers where is_cancelled = true
select * from customers where is_cancelled = false

-- Bad
select * from customers where is_cancelled
select * from customers where not is_cancelled

21. 使用as给字段起别名

-- Good
select
    id,
    email,
    timestamp_trunc(created_at, month) as signup_month
from users

-- Bad
select
    id,
    email,
    timestamp_trunc(created_at, month) signup_month
from users

22.按列名分组，而不是按编号分组

-- Good
select user_id, count(*) as total_charges
from charges
group by user_id

-- Bad
select
    user_id,
    count(*) as total_charges
from charges
group by 1

23.使用列别名分组

-- Good
select
  timestamp_trunc(com_created_at, year) as signup_year,
  count(*) as total_companies
from companies
group by signup_year

-- Bad
select
  timestamp_trunc(com_created_at, year) as signup_year,
  count(*) as total_companies
from companies
group by timestamp_trunc(com_created_at, year)

24.首先对列进行分组

-- Good
select
  timestamp_trunc(com_created_at, year) as signup_year,
  count(*) as total_companies
from companies
group by signup_year

-- Bad
select
  count(*) as total_companies,
  timestamp_trunc(com_created_at, year) as signup_year
from mysql_helpscout.helpscout_companies
group by signup_year

25. when 声明的案例

-- Good
select
    case
        when event_name = 'viewed_homepage' then 'Homepage'
        when event_name = 'viewed_editor' then 'Editor'
    end as page_name
from events

-- Good too
select
    case
        when event_name = 'viewed_homepage'
            then 'Homepage'
        when event_name = 'viewed_editor'
            then 'Editor'
    end as page_name
from events

-- Bad 
select
    case when event_name = 'viewed_homepage' then 'Homepage'
        when event_name = 'viewed_editor' then 'Editor'
    end as page_name
from events

26. Use CTEs, not subqueries

避免子查询;cte将使您的查询更容易阅读和推理。

使用CTEs时，用新行填充查询。

如果您使用任何CTE，请始终使用名为final的CTE，并在末尾选择* from final。这样，您就可以快速检查查询中用于调试结果的其他cte的输出。

关闭CTE圆括号时应使用与with和CTE名称相同的缩进级别。

-- Good
with ordered_details as (

    select
        user_id,
        name,
        row_number() over (partition by user_id order by date_updated desc) as details_rank
    from billingdaddy.billing_stored_details

),

final as (

    select user_id, name
    from ordered_details
    where details_rank = 1

)

select * from final

-- Bad
select user_id, name
from (
    select
        user_id,
        name,
        row_number() over (partition by user_id order by date_updated desc) as details_rank
    from billingdaddy.billing_stored_details
) ranked
where details_rank = 1

27.使用有意义的CTE名称

-- Good
with ordered_details as (

-- Bad
with d1 as (

28. Window functions

您可以将其全部保留在自己的行中，也可以根据其长度将其分解为多个：

-- Good
select
    user_id,
    name,
    row_number() over (partition by user_id order by date_updated desc) as details_rank
from billingdaddy.billing_stored_details

-- Good
select
    user_id,
    name,
    row_number() over (
        partition by user_id
        order by date_updated desc
    ) as details_rank
from billingdaddy.billing_stored_details

原文

本作品采用《CC 协议》，转载必须注明作者和本文链接