最常见的 SQL 反模式是什么？

What are the most common SQL anti-patterns?

提问by dkretz

All of us who work with relational databases have learned (or are learning) that SQL is different. Eliciting the desired results, and doing so efficiently, involves a tedious process partly characterized by learning unfamiliar paradigms, and finding out that some of our most familiar programming patterns don't work here. What are the common antipatterns you've seen (or yourself committed)?

我们所有使用关系数据库的人都知道（或正在学习）SQL 是不同的。获得想要的结果并有效地执行，涉及一个乏味的过程，部分特点是学习不熟悉的范式，并发现我们最熟悉的一些编程模式在这里不起作用。你见过（或你自己犯过）的常见反模式是什么？

采纳答案by Juliet

I am consistently disappointed by most programmers' tendency to mix their UI-logic in the data access layer:

我一直对大多数程序员在数据访问层中混合他们的 UI 逻辑的倾向感到失望：

SELECT
    FirstName + ' ' + LastName as "Full Name",
    case UserRole
        when 2 then "Admin"
        when 1 then "Moderator"
        else "User"
    end as "User's Role",
    case SignedIn
        when 0 then "Logged in"
        else "Logged out"
    end as "User signed in?",
    Convert(varchar(100), LastSignOn, 101) as "Last Sign On",
    DateDiff('d', LastSignOn, getDate()) as "Days since last sign on",
    AddrLine1 + ' ' + AddrLine2 + ' ' + AddrLine3 + ' ' +
        City + ', ' + State + ' ' + Zip as "Address",
    'XXX-XX-' + Substring(
        Convert(varchar(9), SSN), 6, 4) as "Social Security #"
FROM Users

Normally, programmers do this because they intend to bind their dataset directly to a grid, and its just convenient to have SQL Server format server-side than format on the client.

通常，程序员这样做是因为他们打算将他们的数据集直接绑定到网格，而且在服务器端使用 SQL Server 格式比在客户端使用格式更方便。

Queries like the one shown above are extremely brittle because they tightly couple the data layer to the UI layer. On top of that, this style of programming thoroughly prevents stored procedures from being reusable.

上面显示的查询非常脆弱，因为它们将数据层与 UI 层紧密耦合。最重要的是，这种编程风格彻底阻止了存储过程的可重用性。

回答by Amy B

Here are my top 3.

这是我的前 3 名。

Number 1. Failure to specify a field list. (Edit: to prevent confusion: this is a production code rule. It doesn't apply to one-off analysis scripts - unless I'm the author.)

编号 1. 未能指定字段列表。（编辑：为防止混淆：这是生产代码规则。它不适用于一次性分析脚本 - 除非我是作者。）

SELECT *
Insert Into blah SELECT *

should be

应该

SELECT fieldlist
Insert Into blah (fieldlist) SELECT fieldlist

Number 2. Using a cursor and while loop, when a while loop with a loop variable will do.

数字 2. 使用游标和 while 循环，当使用循环变量的 while 循环就可以了。

DECLARE @LoopVar int

SET @LoopVar = (SELECT MIN(TheKey) FROM TheTable)
WHILE @LoopVar is not null
BEGIN
  -- Do Stuff with current value of @LoopVar
  ...
  --Ok, done, now get the next value
  SET @LoopVar = (SELECT MIN(TheKey) FROM TheTable
    WHERE @LoopVar < TheKey)
END

Number 3. DateLogic through string types.

数字 3. DateLogic 通过字符串类型。

--Trim the time
Convert(Convert(theDate, varchar(10), 121), datetime)

Should be

应该

--Trim the time
DateAdd(dd, DateDiff(dd, 0, theDate), 0)

---

---

I've seen a recent spike of "One query is better than two, amiright?"

我看到最近出现了“一个查询比两个查询好，对吗？”的消息。

SELECT *
FROM blah
WHERE (blah.Name = @name OR @name is null)
  AND (blah.Purpose = @Purpose OR @Purpose is null)

This query requires two or three different execution plans depending on the values of the parameters. Only one execution plan is generated and stuck into the cache for this sql text. That plan will be used regardless of the value of the parameters. This results in intermittent poor performance. It is much better to write two queries (one query per intended execution plan).

此查询需要两个或三个不同的执行计划，具体取决于参数的值。对于这个 sql 文本，只生成一个执行计划并卡在缓存中。无论参数的值如何，都将使用该计划。这会导致间歇性的性能不佳。最好编写两个查询（每个预期执行计划一个查询）。

回答by annakata

• Human readable password fields, egad. Self explanatory.

• Using LIKE against indexedcolumns, and I'm almost tempted to just say LIKE in general.

• Recycling SQL-generated PK values.

• Surprise nobody mentioned the god-tableyet. Nothing says "organic" like 100 columns of bit flags, large strings and integers.

• Then there's the "I miss .ini files"pattern: storing CSVs, pipe delimited strings or other parse required data in large text fields.

• And for MS SQL server the use of cursors at all. There's a better way to do any given cursor task.

• 人类可读的密码字段，egad。不言自明。

• 对索引列使用LIKE，我几乎想在一般情况下只说 LIKE。

• 回收 SQL 生成的 PK 值。

• 令人惊讶的是，还没有人提到神桌。没有什么比 100 列位标志、大字符串和整数更能说明“有机”了。

• 然后是“我想念 .ini 文件”模式：在大文本字段中存储 CSV、管道分隔字符串或其他解析所需的数据。

• 而对于 MS SQL 服务器，根本就没有使用游标。有一种更好的方法来执行任何给定的游标任务。

Edited because there's so many!

编辑，因为有这么多！

回答by stesch

Don't have to dig deep for it: Not using prepared statements.

不必深挖：不使用准备好的语句。

回答by Tony Andrews

Using meaningless table aliases:

使用无意义的表别名：

from employee t1,
department t2,
job t3,
...

Makes reading a large SQL statement so much harder than it needs to be

使阅读大型 SQL 语句变得比它需要的要困难得多

回答by Tony Andrews

var query = "select COUNT(*) from Users where UserName = '" 
            + tbUser.Text 
            + "' and Password = '" 
            + tbPassword.Text +"'";

1. Blindly trusting user input
2. Not using parameterized queries
3. Cleartext passwords

1. 盲目相信用户输入
2. 不使用参数化查询
3. 明文密码

回答by Pete OHanlon

My bugbears are the 450 column Access tables that have been put together by the 8 year old son of the Managing Director's best friends dog groomer and the dodgy lookup table that only exists because somebody doesn't know how to normalise a datastructure properly.

我的问题是由总经理最好的朋友狗美容师的 8 岁儿子整理的 450 列 Access 表和仅存在的狡猾查找表，因为有人不知道如何正确规范化数据结构。

Typically, this lookup table looks like this:

通常，此查找表如下所示：

ID INT,
Name NVARCHAR(132),
IntValue1 INT,
IntValue2 INT,
CharValue1 NVARCHAR(255),
CharValue2 NVARCHAR(255),
Date1 DATETIME,
Date2 DATETIME

I've lost count of the number of clients I've seen who have systems that rely on abominations like this.

我已经数不清有多少客户拥有依赖于这种可憎之物的系统。

回答by Jamal Hansen

The ones that I dislike the most are

我最不喜欢的是

1. Using spaces when creating tables, sprocs etc. I'm fine with CamelCase or under_scores and singular or plurals and UPPERCASE or lowercase but having to refer to a table or column [with spaces], especially if [ it is oddly spaced] (yes, I've run into this) really irritates me.

2. Denormalized data. A table doesn't have to be perfectly normalized, but when I run into a table of employees that has information about their current evaluation score or their primary anything, it tells me that I will probably need to make a separate table at some point and then try to keep them synced. I will normalize the data first and then if I see a place where denormalization helps, I'll consider it.

3. Overuse of either views or cursors. Views have a purpose, but when each table is wrapped in a view it's too much. I've had to use cursors a few times, but generally you can use other mechanisms for this.

4. Access. Can a program be an anti-pattern? We have SQL Server at my work, but a number of people use access due to it's availabilty, "ease of use" and "friendliness" to non-technical users. There is too much here to go into, but if you've been in a similar environment, you know.

1. 在创建表、sprocs 等时使用空格。我可以使用 CamelCase 或 under_scores 以及单数或复数以及大写或小写，但必须引用表或列 [带空格]，特别是如果 [它是奇怪的间隔]（是的，我遇到了这个）真的让我很恼火。

2. 非规范化数据。表格不必完全规范化，但是当我遇到一个包含有关他们当前评估分数或主要内容信息的员工表格时，它告诉我我可能需要在某个时候制作一个单独的表格，并且然后尝试使它们保持同步。我会先规范化数据，然后如果我看到非规范化有帮助的地方，我会考虑它。

3. 过度使用视图或游标。视图是有目的的，但是当每个表都包含在视图中时，它就太多了。我不得不多次使用游标，但通常您可以为此使用其他机制。

4. 使用权。程序可以是反模式吗？我们在我的工作中有 SQL Server，但由于它的可用性、“易用性”和对非技术用户的“友好性”，许多人使用访问权限。这里有太多东西要讲，但如果您曾在类似的环境中工作，您就会知道。

回答by Oscar Cabrero

use SP as the prefix of the store procedure name because it will first search in the System procedures location rather than the custom ones.

使用 SP 作为存储过程名称的前缀，因为它将首先搜索系统过程位置而不是自定义位置。

回答by Rockcoder

Overuse of temporary tables and cursors.

过度使用临时表和游标。