标准使用 'Z' 而不是 NULL 来表示丢失的数据? [英] Standard use of 'Z' instead of NULL to represent missing data?
问题描述
在是否应该使用 NULL 的争论之外:我负责使用 NULL 表示丢失或从未输入"的现有数据库；数据.它不同于空字符串，它的意思是用户设置了这个值，他们选择了‘空’".
Outside of the argument of whether or not NULLs should ever be used: I am responsible for an existing database that uses NULL to mean "missing or never entered" data. It is different from empty string, which means "a user set this value, and they selected 'empty'."
该项目的另一个承包商坚决认为对我来说空值不存在；我从不使用 NULL，其他人也不应该使用"论据的一方.然而，让我感到困惑的是，由于承包商的团队确实承认失踪/从未进入"和未进入"之间的区别.和故意清空或由用户指示为未知"，他们在整个代码和存储过程中使用单个字符Z"来表示丢失/从未输入"；与整个数据库其余部分的 NULL 含义相同.
Another contractor on the project is firmly on the "NULLs do not exist for me; I never use NULL and nobody else should, either" side of the argument. However, what confuses me is that since the contractor's team DOES acknowledge the difference between "missing/never entered" and "intentionally empty or indicated by the user as unknown," they use a single character 'Z' throughout their code and stored procedures to represent "missing/never entered" with the same meaning as NULL throughout the rest of the database.
尽管我们的共享客户要求更改此设置，并且我支持此请求，但团队将此称为标准做法"；在 DBA 中远比我先进；他们不愿意仅根据我的无知要求更改为使用 NULL.那么，谁能帮助我克服我的无知?是否有任何标准的，或一小群人，甚至是 SQL 专家中的一个响亮的声音提倡使用Z"代替 NULL?
Although our shared customer has asked for this to be changed, and I have supported this request, the team cites this as "standard practice" among DBAs far more advanced than I; they are reluctant to change to use NULLs based on my ignorant request alone. So, can anyone help me overcome my ignorance? Is there any standard, or small group of individuals, or even a single loud voice among SQL experts which advocates the use of 'Z' in place of NULL?
我有承包商的回复要补充.当客户要求删除特殊值以允许没有数据的列中为 NULL 时，他是这样说的:
I have a response from the contractor to add. Here's what he said when the customer asked for the special values to be removed to allow NULL in columns with no data:
基本上，我设计了数据库以尽可能避免 NULL.理由如下:
• 字符串 [VARCHAR] 字段中的 NULL 永远不是必需的，因为空(零长度)字符串提供完全相同的信息.
• 整数字段(例如 ID 值)中的 NULL 可以通过使用数据中永远不会出现的值(例如，整数 IDENTITY 字段的 -1)来处理.
• 日期字段中的 NULL 很容易导致日期计算复杂化.例如，在计算日期差异的逻辑中，例如 [RecoveryDate] 和 [OnsetDate] 之间的天数差异，如果一个或两个日期为 NULL，该逻辑就会崩溃——除非明确允许两个日期为 NULL.这是额外的工作和额外的处理.如果默认"或占位符"日期用于 [RecoveryDate] 和 [OnsetDate](例如，1/1/1900")，数学计算可能会显示不寻常"值 - 但日期逻辑不会爆炸.
NULL 处理历来是开发人员在存储过程中出错的一个领域.
在我担任 DBA 的 15 年中，我发现最好尽可能避免 NULL.
这似乎证实了对这个问题的主要负面反应.不是应用公认的 6NF 方法来设计出 NULL，而是使用特殊值来尽可能避免 NULL".我以开放的心态发布了这个问题，我很高兴我学到了更多关于NULL 是有用的/NULL 是邪恶的"的知识.辩论，但我现在很自在地将特殊价值"方法贴上完全无意义的标签.
This seems to validate the mostly negative reaction to this question. Instead of applying an accepted 6NF approach to designing out NULLs, special values are used to "avoid NULLs wherever possible." I posted this question with an open mind, and I am glad I learned more about the "NULLs are useful / NULLs are evil" debate, but I am now quite comfortable labeling the 'special values' approach to be complete nonsense.
空(零长度)字符串提供完全相同的信息.
an empty (zero-length) string furnishes exactly the same information.
不，它没有；在我们正在修改的现有数据库中，NULL 表示从未输入"；和空字符串表示输入为空".
No, it doesn't; in the existing database we are modifying, NULL means "never entered" and empty string means "entered as empty".
NULL 处理历来是开发人员在存储过程中出错的一个领域.
NULL handling has traditionally been an area where developers make mistakes in stored procedures.
是的，但是这些错误已被成千上万的开发人员犯了数千次，并且已知并记录了避免这些错误的经验教训和注意事项.正如这里提到的:无论您接受还是拒绝 NULL，缺失值的表示都是 已解决的问题.没有必要仅仅因为开发人员不断犯易于克服(和易于识别)的错误而发明新的解决方案.
Yes, but those mistakes have been made thousands of times by thousands of developers, and the lessons and caveats for avoiding those mistakes are known and documented. As has been mentioned here: whether you accept or reject NULLs, representation of missing values is a solved problem. There is no need to invent a new solution just because developers continue make easy-to-overcome (and easy-to-identify) mistakes.
作为一个脚注:我从事 DBE 和开发人员已有 20 多年(这当然足以让我了解数据库工程师和数据库管理员之间的区别).在我的整个职业生涯中，我一直在NULL 是有用的"中.营，虽然我知道有几个非常聪明的人不同意.我对特殊价值观"非常怀疑.方法，但对如何以正确的方式避免 NULL"的学术研究不够精通；以坚定立场.我总是喜欢学习新事物——20 年后我还有很多东西要学.感谢所有为此做出贡献的人.
推荐答案
解雇你的承包商.
好吧，说真的，这不是标准做法.这可以简单地看出，因为我曾经使用过的所有 RDBMS 都实现了 NULL、NULL 的逻辑、在外键中考虑了 NULL、对 COUNT 中的 NULL 有不同的行为等.
Okay, seriously, this isn't standard practice. This can be seen simply because all RDBMS that I have ever worked with implement NULL, logic for NULL, take account of NULL in foreign keys, have different behaviour for NULL in COUNT, etc, etc.
我实际上认为使用Z"或任何其他占位符更糟.您仍然需要代码来检查Z".但是您还需要证明Z"并不意味着Z"，它意味着其他的东西.而且您必须确保阅读此类文档.然后，如果Z"成为有效数据，会发生什么?(例如首字母的字段?)
I would actually contend that using 'Z' or any other place holder is worse. You still require code to check for 'Z'. But you also need to document that 'Z' doesn't mean 'Z', it means something else. And you have to ensure that such documentation is read. And then what happens if 'Z' ever becomes a valid piece of data? (Such as a field for an initial?)
在基本层面上，即使不争论 NULL 与 'Z' 的有效性，我也会坚持承包商符合贵公司内部存在的标准做法，而不是他的.在具有替代标准实践的环境中建立他的标准实践会导致混淆、维护开销、误解，并最终增加成本和错误.
At a basic level, even without debating the validity of NULL vs 'Z', I would insist that the contractor conforms to standard practices that exist within your company, not his. Instituting his standard practice in an environment with an alternative standard practice will cause confusion, maintenance overheads, mis-understanding, and in the end increased costs and mistakes.
在我看来，有些情况下使用 NULL 的替代方法是有效的.但只有在这样做减少代码的地方，而不是创建需要考虑的特殊情况.
There are cases where using an alternative to NULL is valid in my opinion. But only where doing so reduces code, rather than creating special cases which require accounting for.
例如，我已将其用于日期绑定数据.如果数据在开始日期和结束日期之间有效，则可以通过不包含 NULL 值来简化代码.相反，可以将 NULL 开始日期替换为1900 年 1 月 1 日"，将 NULL 结束日期替换为2079 年 12 月 31 日".
I've used that for date bound data, for example. If data is valid between a start-date and an end-date, code can be simplified by not having NULL values. Instead a NULL start-date could be replaced with '01 Jan 1900' and a NULL end-date could be replaced with '31 Dec 2079'.
这仍然可以改变预期的行为，因此应谨慎使用:
This still can change behaviour from what may be expected, and so should be used with care:
WHERE end-date IS NULL不再给出仍然有效的数据- 您刚刚创建了自己的千年虫
- 等
WHERE end-date IS NULLno longer give data that is still valid- You just created your own millennium bug
- etc.
这等价于改造抽象，使得所有属性都可以始终具有有效值.它明显不同于将特定含义隐式编码为任意选择的值.
This is equivalent to reforming abstractions such that all properties can always have valid values. It is markedly different from implicitly encoding specific meaning into arbitrarily chosen values.
仍然解雇承包商.
这篇关于标准使用 'Z' 而不是 NULL 来表示丢失的数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
