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

Question

与关系型数据库打交道的我们皆知（或正在学习）SQL的独特性。要达到所需结果且高效地做到这一点，需要进行一项繁琐的过程，其中一部分特点是学习不熟悉的范例，并发现我们最熟悉的一些编程模式在此处不起作用。您见过哪些常见反模式（或者自己犯了哪些）？

- doofledorfer

---

仅支持David答案的第1点，请查看此示例，了解在某些情况下使用select *可能会产生意外结果的情况。 - kristof

Answer 1

大多数程序员倾向于将其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

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

像上面所示的查询非常脆弱，因为它们紧密耦合了数据层和 UI 层。除此之外，这种编程风格彻底防止了存储过程的可重用性。

- Princess

---

一个最大程度跨越尽可能多的层次/抽象层级的紧密耦合的典型代表模式。 - dkretz

出于性能方面的考虑，我经常这样做，虽然这可能不利于解耦。SQL Server 所做的迭代更改比中间层代码更快。我不理解你提到重用性的观点 - 如果你希望这样做，没有什么可以阻止你运行存储过程并重新命名列名。 - Joe Pineda

我最喜欢的是人们嵌入HTML和JavaScript，例如SELECT '{{link1：'+ name'}}'。 - Matt Rogish

通过这样的查询，您可以使用简单的修改语句来编辑网站中的网格。或更改导出内容，或重新格式化报告中的日期。这可以让客户感到满意，并为我节省时间。所以谢谢了，但是我还是会坚持使用这样的查询。 - Andomar

耶稣，真的有人这么做吗？ - Axarydax

是的，有人确实这样做。我经常看到这种情况发生在使用MSSQLServer作为后端的MSAccess应用程序中，因为您需要将查询直接绑定到连续表单。 - Oliver

Answer 2

以下是我的前三：

第一。未指定字段列表。（编辑说明：为了避免混淆：这是一个生产代码规则。它不适用于一次性分析脚本 - 除非我是作者。）

SELECT *
Insert Into blah SELECT *

应该是这样的

SELECT fieldlist
Insert Into blah (fieldlist) SELECT fieldlist

第二种方法是使用游标和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

第三个。通过字符串类型实现日期逻辑。

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

应该是这样的

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

最近我看到了“一个查询优于两个，是吧？”这句话的频繁出现。

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

这个查询需要根据参数值生成两到三个不同的执行计划。只有一个执行计划被生成并保留在缓存中，用于此SQL文本。无论参数值如何，都将使用该计划。这导致间歇性的性能下降。最好编写两个查询（每个查询对应一个预期的执行计划）。

- David B

---

嗯，就点2和点3来说，我会给你加一分，但开发人员有时会过度强调规则1。它有时也有其作用。 - annakata

#1的推理背后是什么？ - jalf

当你使用 "select *" 时，你会获得表中的所有内容。这些列可能会更改名称和顺序。客户端代码经常依赖于列名和顺序。每6个月我都会被问到如何在修改表时保留列顺序。如果遵循规则，那就不重要了。 - Amy B

有时我使用第二种方法，其他时候我会选择游标方式（不过这时我会先将查询结果保存到表变量中，然后再在其上打开游标）。我一直想知道是否有人对两种方法进行了性能测试。 - Joe Pineda

当然，几乎总是应该在无法找出使用基于集合的 SQL 完成工作的情况下才考虑使用游标。我曾经花费约 45 分钟仔细剖析一个可怕的、巨大的 PL/SQL 游标存储过程（画了这个烂东西的图），它填充了一个大临时表然后选择了临时表的内容返回给调用者以呈现报告。在实际硬件上运行需要 8.5 分钟。在整个事情都被分解成图之后，我能够用单个查询来代替它，在不到 2 秒钟内返回相同的结果。游标啊…… - Craig Tullis

Answer 3

• 易于人类阅读的密码字段，不用解释。

• 对索引列使用LIKE操作符，我几乎想说一般都可以使用LIKE。

• 重复使用SQL生成的主键值。

• 令人惊讶的是还没有人提到神表。没有任何东西比拥有100个位标记、大字符串和整数的列更能说明“生命力”了。

• 然后还有“我想念.ini文件”模式：将CSV文件、竖线分隔字符串或其他需要解析的数据存储在大型文本字段中。

• 对于MS SQL Server来说，完全不需要使用游标。使用其他方法可以完成游标任务。

- annakata

---

关于光标的观点不正确，我会犹豫地说任何特定行为是100%正确或100%错误。 - Shawn

到目前为止，我看到的每个光标防御示例都在使用错误的工具。但如果你只懂 SQL，你要么使用它不当，要么学会编写其他类型的软件。 - dkretz

LIKE '%blah%' 如何使用索引？索引依赖于排序，而这个示例搜索了一个随机的字符串中间位置。（索引按第一个字符首先排序，因此查看中间的四个字符会得到几乎是随机的排序…） - MatBailie

在大多数数据库服务器上（至少在我使用过的），如果使用前缀搜索（LIKE 'xxx%'），LIKE操作可以利用索引...也就是说，只要通配符不是出现在搜索字符串的开头。我认为你们可能有点误解了彼此的意思。 - Cowan

就像你不喜欢 LIKE '%LIKE'。 - Johan

Answer 4

不必深入挖掘：不使用预处理语句。

- stesch

---

是的。在我的经验中，它与“不捕获错误”密切相关。 - dkretz

@stesch：与使用视图并具有可变报告日期相比，这算不了什么。如果您有一个可变的报告日期（我假设大多数应用程序都有），则视图是一种反模式。我本来会在另一个答案中添加这个内容，但不幸的是它已经关闭了。 - Stefan Steiger

Answer 5

使用无意义的表别名：

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

使阅读一条大型的SQL语句变得比必要的困难。

- Tony Andrews

---

别名？见过真实的列名也有这样的。 - annakata

简洁的别名可以接受。如果您想要一个有意义的名称，那么根本不要使用别名。 - Joel Coehoorn

他说的不是“简练”，而是“毫无意义”。在我的观点中，使用e、d和j作为示例查询中的别名是没有问题的。 - Robert Rossney

没问题，Robert - e, d 和 j 对我来说都可以。 - Tony Andrews

我会使用“emp”表示员工，“dep”表示部门，“job”（或可能是“jb”）表示工作。 - Andrei Rînea

Answer 6

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

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

- user1228

---

所有这些都可以通过使用某种（任何一种）数据库抽象层来实现有用的处理。 - dkretz

@doofledorfer：同意，在这种情况下，中间层肯定会更好，而且还可以提供结果缓存作为一个不错的副作用。 - Joe Pineda

很棒的例子。如果开发人员理解如何用更好的解决方案替换它，他们就已经走了成为一个不错的SQL开发人员的一半路程。 - Steve McLeod

Answer 7

我的恼人之处是由总经理最好朋友的狗美容师的8岁儿子组成的450列Access表和不正常化数据结构导致存在的可疑查找表。

通常情况下，这种查找表看起来像这样：

ID INT，
名称 NVARCHAR(132)，
IntValue1 INT，
IntValue2 INT，
CharValue1 NVARCHAR(255)，
CharValue2 NVARCHAR(255)，
Date1 DATETIME，
Date2 DATETIME

我已经失去了统计使用这种丑陋数据结构系统的客户数量。

- Pete OHanlon

---

更糟糕的是，我读到在最新版本的Access中，这实际上是自动支持的，这让我担心会鼓励更多这种Value1、Value2、Value3...列的迷信。 - Joe Pineda

等等 - 那么这个8岁的儿子是那个狗美容师的儿子？ - barrypicker

Answer 8

我最不喜欢的是以下几点：

1. 在创建表、存储过程等时使用空格。我可以接受驼峰命名法或下划线，单数或复数，大写或小写，但是如果需要引用一个带有空格的表或列，特别是如果它们之间的空格很奇怪（是的，我遇到过这种情况），那真的会让我很恼火。

2. 非规范化的数据。一张表不必完全规范化，但当我遇到一个员工信息表中包含当前评估得分或他们的主要任何事项时，这告诉我我可能需要在某个时候创建一个单独的表，然后尝试保持它们同步。我会先规范化数据，然后如果看到非规范化可以提高效率的地方，我才会考虑使用。

3. 过度使用视图或游标。视图有其作用，但当每张表都被包装在一个视图中时就太多了。我有时候需要使用游标，但通常你可以使用其他机制来完成。

4. Access。程序可以成为反模式吗？我们公司使用的是SQL Server，但有些人会因为Access易于获取、"易于使用"和对非技术用户的"友好性"而使用它。这里有太多需要讲解的，但如果你处于类似的环境中，你就知道。

- rubyyot

---

#4 - 还有另一个线程专门讨论<a href='https://dev59.com/Q3RC5IYBdhLWcg3wVvnL'>Access</a> :)。 - dkretz

Access不是DBMS，而是一个带有非常简单的数据库管理器的RAD环境。除非你添加类似VB语言和Crystal Reports的工具，否则SQL Server、Oracle等数据库管理系统永远无法取代它。 - Joe Pineda

@JoePineda 不确定你在引用VB和Crystal Reports时想表达什么，但我认为12年后，C#和SSRS已经远远超越它们了。虽然不幸的是，Access仍然被一些不太懂技术的人使用，但它肯定已经死了。 :) - J.D.

Answer 9

存储时间值时，只应该使用UTC时区，不应该使用本地时间。

- Frank Schwieterman

---

到目前为止，我仍然没有找到一种简单好用的方法来将过去日期的UTC时间转换为本地时间，因为在考虑夏令时时需要考虑到不同年份和国家之间变更日期的差异以及国内所有例外情况。因此，UTC并不能让你免除转换的复杂性。但是，知道每个存储日期时间的时区是很重要的。 - ckarras

许多地方实行夏令时，因此在时间转换的一小时内的时间值可能会存在歧义。 - Frank Schwieterman

这对于现在和过去来说肯定是正确的，但对于未来，特别是相当遥远的未来，显式时区通常是必需的。如果您有一个30年期权，刚刚写入并在2049-09-27T17:00:00纽约时间到期，那么您不能盲目地假设那将是21:00:00Z。美国国会很可能会更改夏令时规则。您必须将本地时间和真实时区（America/New_York）分开。 - John Cowan

Answer 10

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

- Oscar Cabrero

---

还可以扩展到使用任何其他常见前缀来命名所有存储过程，使得在已排序的列表中进行筛选更加困难。 - dkretz

对doofledorfer评论点赞！我经常看到这种情况，觉得这很愚蠢，确实让查找特定SP变得非常困难！！！类似的还有"vw_"代表视图，"tbl_"代表表格等，我是多么讨厌它们！ - Joe Pineda

如果您正在将对象脚本化到文件中（例如：用于源代码控制、部署或迁移），那么前缀可能会很有用。 - Rick

为什么要在每个存储过程前面都加上sp或usp前缀呢？这只会让查找所需的存储过程变得更加困难。 - Ryan Lundy