SQL:避免硬编码或幻数

问题描述

问题:还有哪些其他策略可以避免在 SQL 脚本或存储过程中使用幻数或硬编码值?

Question: What are some other strategies on avoiding magic numbers or hard-coded values in your SQL scripts or stored procedures?

考虑一个存储过程，它的工作是根据 StatusID 或其他一些 FK 查找表或值范围检查/更新记录的值.

Consider a stored procedure whose job is to check/update a value of a record based on its StatusID or some other FK lookup table or range of values.

考虑一个 Status 表，其中 ID 是最重要的，因为它是另一个表的 FK:

Consider a Status table where the ID is most important, as it's a FK to another table:

要避免的 SQL 脚本类似于:

The SQL scripts that are to be avoided are something like:

DECLARE  @ACKNOWLEDGED tinyint

SELECT  @ACKNOWLEDGED = 3   --hardcoded BAD

UPDATE  SomeTable
SET     CurrentStatusID = @ACKNOWLEDGED
WHERE   ID = @SomeID

这里的问题是这不是可移植的，并且明确依赖于硬编码值.将其部署到另一个关闭身份插入的环境时，存在细微的缺陷.

The problem here is that this is not portable and is explicitly dependent on the hard-coded value. Subtle defects exist when deploying this to another environment with identity inserts off.

还试图避免基于文本描述/状态名称的 SELECT:

Also trying to avoid a SELECT based on the text description/name of the status:

UPDATE  SomeTable
SET     CurrentStatusID = (SELECT ID FROM [Status] WHERE [Name] = 'Acknowledged')
WHERE   ID = @SomeID

问题:还有哪些其他策略可以避免在 SQL 脚本或存储过程中使用幻数或硬编码值?

Question: What are some other strategies on avoiding magic numbers or hard-coded values in your SQL scripts or stored procedures?

关于如何实现这一目标的一些其他想法:

Some other thoughts on how to achieve this:

• 添加一个新的 bit 列(命名为IsAcknowledged")和一组规则，其中只能有一个值为 1 的行.这将有助于找到唯一的行:SELECT ID FROM [Status] WHERE [IsAcknowledged] = 1)

• add a new bit column (named like 'IsAcknowledged') and sets of rules where there can be only one row with a value of 1. This would help in finding the unique row: SELECT ID FROM [Status] WHERE [IsAcknowledged] = 1)

推荐答案

在某种程度上，将会有一些值的硬编码".消除它们的想法来自两个方面:

At some level, there's going to be some "hard coding" of values. The idea of eliminating them comes from two sides:

1. 使代码更具可读性(即说 'Acknowledged' 而不是 3 可能会让你的意图更明显阅读器.
2. 使代码更加动态，其中一个函数可以接受一个参数，而不是几个不接受参数的函数(这显然是一种简化，但无论如何其含义应该是不言而喻的)

1. Making the code more readable (i.e., saying 'Acknowledged' rather than 3 is probably going to make your intentions more obvious to the reader.
2. Making the code more dynamic, where one function can take a parameter rather than several functions that don't (this is a simplification obviously, but the meaning should be fairly self-evident anyway)

为各种状态制作bit 列可能是一个好主意，也可能是一个坏主意；这真的只取决于数据.如果数据经历了不同的阶段"(接收、确认、审核中、拒绝、接受、响应等)，那么这种方法很快就会失去可行性(更不用说必须确保在任何给定时间，只有一列设置为 1).另一方面，如果状态真的像您描述的那样简单，那么这样做可以使代码更具可读性并且索引性能更好.

Making bit columns for various states can be a good or bad idea; it really just depends on the data. If the data goes through various "stages" (Received, Acknowledged, Under Review, Rejected, Accepted, Responded, etc.) then that approach quickly scales itself out of viability (not to mention the irritating process of having to ensure that only one of the columns is set to 1 at any given time). If, on the other hand, the state is really as simple as you describe, then doing that can make the code more readable and indexes perform better.

硬编码值中最大的禁忌是引用其他实体的硬编码值(换句话说，硬编码对应对象的主键).字符串 'Acknowledged' 仍然是一个硬编码值，它的含义更透明，它不是对其他东西的引用.对我来说，归结为:如果你能(合理地)查一下，就去做.如果你不能(或者从性能或可维护性的角度来看，如果某些事情使它成为不合理的任务)，请对其进行硬编码.使用它，您可以使用 Acknowledged 查找 3 的值；您无法从其他任何内容中查找 Acknowledged.

The biggest no-no in hard coding values is hard coding values that reference other entities (in other words, hard coding the primary key for a corresponding object). The string 'Acknowledged' is still a hard-coded value, it's just more transparent in its meaning and it isn't a reference to something else. For me, it boils down to this: if you can (reasonably) look it up, do it. If you can't (or if something makes it an unreasonable task either from a performance or maintainability perspective), hard code it. Using this, you can look up the value of 3 by using Acknowledged; you can't look up Acknowledged from anything else.

这篇关于SQL:避免硬编码或幻数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！