我可以配置 SQL Server 2008 数据库以便将所有空字符串保存为 NULL 吗?
在我当前的项目中,从域中排除空字符串会很方便,因此无论在哪里遇到空字符串,它都会被视为NULL,例如如果我 UPDATE [table] SET [Something]='', SQL Server应该将其视为 SET [Something]=NULL,以便在下一个 SELECT 中将 NULL 作为 [Something] 值返回。
我应该使用触发器还是有更好的方法?
In my current project it would be convenient to exclude empty strings from the domain, so that is an empty string is met wherever, it is treated as NULL, for example if I UPDATE [table] SET [Something]='', SQL Server should treat it as SET [Something]=NULL, so that in next SELECT a NULL is returned as [Something] value.
Should I use triggers or there is a better way?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
没有自动方法强制将空字符串转换为 null。但是,您可以对所有 char、varchar、nchar 和 nvarchar 列添加检查约束,这要求值大于零或为 null。如果客户端应用程序未将空字符串转换为 null,这将导致客户端应用程序抛出异常。
除了使用检查约束之外,确保空字符串转换为 NULL 的理想位置是数据访问层。您应该封装打开连接、对数据库执行某些操作然后关闭连接的代码。据推测,该代码将采用一组参数作为参数化查询的参数。在该代码中,它可以检查数据类型是否为字符串,如果是,则将其转换为 null。
There is no automatic means to force an empty string to be converted into a null. However, you could add check constraints on all char, varchar, nchar, and nvarchar colums which requires that the value be greater than zero or null. That would cause the client app to throw an exception if it did not convert the empty strings to null.
Beyond the use of check constraints, the ideal place to ensure that empty strings are converted to NULL is your data access layer. You should encapsulate the code that opens a connection, executes something against the database and then closes the connection. Presumably, that code will take an array of arguments used as the parameters to your parametrized queries. In that code, it can check if the data type is a string and if so, convert it to null.
值得注意的是,NULL 并不总是与空字符串相同(即使今天是这样,明天它可能会混淆某人删除空字符串)。例如,这两种情况是否相同:
在将所有空字符串强制为 NULL 之前,您应该考虑知道该字段已具有值是否对您有价值。另外 - 单个空格是空字符串吗?多个空格? CHR(254)?
It's worth noting that NULL is not always the same thing as an empty string (even though today it is, tomorrow it may confuse someone to have their empty string deleted). For example, are these two situations identical:
You should consider whether there is value to you in knowing that the field has had a value before you force all empty strings to NULL. Also - is a single space an empty string? Multiple spaces? CHR(254)?
我希望。所有插入和更新表达式都需要正确处理这个问题。
我有多种语言的函数,可用于正确构造字符串表达式。在 PHP 中,还有
另一种用于整数值的方法:
等等。
有关确保业务规则正确区分零和 null 的所有常见警告都适用于第二个。然而,我从未见过任何故意插入零长度字符串的有用理由。似乎当你这样做时,有人想要对它们进行排序,然后你就有麻烦了。
I wish. All your insert and update expressions need to handle this properly.
I have functions in several languages that I use for constructing string expressions properly. In PHP it's
There's another one for integer values:
etc.
All the usual admonitions about making sure your business rules are making proper distinctions between zero and null apply to the second. I've never seen any useful reason to intentionally insert zero-length strings however. It seems when you do, someone wants to sort on them and then you're in trouble.