我们所有使用关系数据库的人都知道(或正在学习)SQL是不同的。获得期望的结果,并有效地这样做,涉及到一个乏味的过程,其部分特征是学习不熟悉的范例,并发现一些我们最熟悉的编程模式在这里不起作用。常见的反模式是什么?


当前回答

select some_column, ...
from some_table
group by some_column

假设结果将按some_column排序。我在Sybase上看到过这种情况,其中假设成立(目前)。

其他回答

我最不喜欢的是

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. 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. 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. 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.

我只是把这个放在一起,基于一些SQL响应这里在SO。

认为触发器之于数据库就像事件处理程序之于OOP是一种严重的反模式。有一种看法是,任何旧的逻辑都可以放入触发器中,当一个事务(事件)在表上发生时被触发。

不正确的。最大的区别之一是触发器是同步的——而且是完全同步的,因为它们在集合操作上是同步的,而不是在行操作上。在OOP方面,正好相反——事件是实现异步事务的有效方法。

我需要把我自己目前最喜欢的放在这里,只是为了使列表完整。我最喜欢的反模式是不测试您的查询。

这适用于以下情况:

您的查询涉及多个表。 您认为您有一个查询的最优设计,但不需要测试您的假设。 您接受第一个有效的查询,不知道它是否接近优化。

任何针对非典型或不充分数据进行的测试都不算数。如果它是一个存储过程,将测试语句放入注释中并保存它,并保存结果。否则,将其与结果一起放入代码中的注释中。

对于存储时间值,应该只使用UTC时区。不应使用当地时间。

select some_column, ...
from some_table
group by some_column

假设结果将按some_column排序。我在Sybase上看到过这种情况,其中假设成立(目前)。