当只有空字符串通过 HTTP POST 来自客户端时，处理数据库中的空值与空字符串
我的 MySQL 数据库有仔细定义的字段。某些字段（如果有可能未知）允许 NULL 值。
我正在编写一个基于网络的 CMS 来处理所述数据库中的数据。显然，HTML 表单中的 post 数组从不包含空值，只包含空字符串。
我不想通过添加“NULL 复选框”或类似的内容来迷惑 CMS 的用户，但我无法从帖子数组中判断字段是否应该为 null 或为空。
对于允许空值的字段，我是否应该在保存时将所有空字符串转换为 NULL 值？
解决此类难题有哪些好的做法？
My MySQL database has carefully defined fields. Some fields, if there is a chance that they can be unknown, allow a NULL value.
I'm writing a web-based CMS to handle the data in said database. Obviously, the post arrays from the HTML forms never contain null values, only empty strings.
I don't want to confuse the users of my CMS by adding a "NULL checkbox" or anything like that but I can't tell from the post arrays whether a field should be null or empty.
Should I convert all empty strings to NULL values on save for fields that allow null values?
What are some good practices for this type of conundrum?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这是一个棘手的问题。当然，用户无法知道他们是否想要插入空字符串或什么都不插入。所以答案实际上取决于你。您想允许用户输入空字符串吗？它这样做有什么意义吗？如果空字符串意味着 NULL 字符串所没有的含义并且定义良好，则继续并允许两者。
如果它们之间没有区别，请选择一个并坚持使用。我个人不明白为什么你需要保留这样的空字符串，这只会让以后的事情变得更加混乱。只需坚持使用 NULL 来表示缺少数据。
现在就做出决定，记录下来并坚持下去。
That is a sticky question. Of course there is no way for a user to know whether they want to insert an empty string or nothing at all. So the answer is really up to you. Do you want to allow users to enter empty strings? Does it have any meaning for it to do so? If an empty string means something that a NULL string doesn't and that is well defined, go ahead and allow both.
If there is no distinction between them, pick one and stick with it. I personally don't see why you would need to keep empty strings like that, it just makes things far more confusing later. Just stick with NULL to represent an absence of data.
Make your decision now, document it and stick by it.
您是否真的想处理实际正确的
NULL及其棘手的三值逻辑？根据我的经验，您很少真正想要
NULL带来的明确的不确定性。为用户不关心填写的值存储空字符串通常更实用。然后您可以继续进行诸如WHERE t.field<>'x'或WHERE t0.field=t1.field之类的搜索，而不必担心它会做什么当一个或两个都为空时，到您的布尔逻辑。如果您已经拥有一个依赖于 null 的不确定性的工作数据库，并且这是您需求的固有部分，那么很好，坚持使用 null （在这种情况下，您可能必须将空用户输入转换为将 nullable 字段设置为 null 只是因为最终用户永远无法理解无和 null 之间的概念差异）。
但就我个人而言，我仍然只对可选的外键引用使用空值（当不使用单独的连接表时）。
Do you really want to deal with actual proper
NULL, with its tricky three-value logic?In my experience you rarely actually want the explicit uncertainty that comes with
NULL. Storing an empty string for values the user doesn't care to fill in is usually more practical. Then you can go ahead and do searches likeWHERE t.field<>'x'orWHERE t0.field=t1.fieldwithout having to worry about what that does to your boolean logic when one or both are null.If you've already got a working database that relies on the uncertain nature of null, and that's an intrinsic part of your requirements, then fine, stick with null (and in that case you'll probably have to convert empty user input in a nullable field to null just because no end-user is ever going to be able to understand the conceptual difference between nothing and null).
But personally I only still use nulls for optional foreign key references (when not using a separate join table).