SQL 电子邮件验证约束
我有一个包含 Email 字段的数据库表。不知何故，无效的电子邮件地址通过了应用程序验证，并进入了数据库字段。
如何向该字段添加约束，以便不可能存储无效的电子邮件地址？
I've got a database table which contains an Email field. Somehow an invalid email address has got through application validation, and got into the database field.
How can I add a constraint to this field so that its not possible for an invalid email address to be stored?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这在一定程度上取决于有效对您意味着什么。对我来说，电子邮件地址可能是有效的（也就是说，它是根据 RFC 5322 正确形成的），但我可能无法向其发送邮件。您是否需要测试 a) 地址格式或 b) 向其发送电子邮件的能力取决于应用程序。
如果您只是想确保用户的电子邮件地址格式正确，则只能使用正则表达式进行验证。祝你好运。请参阅 RFC 3696，“3. 对电子邮件地址的限制”令人沮丧的细节。
以下是我过去用来帮助我的客户做出有关电子邮件验证的决策的一些指导要点。
纠正错误。 （接受任何带有“@”符号和“.”的内容
大致是正确的地方。）
要求用户单击链接进行确认。 （你仍然可以接受
任何带有“@”符号和“.”的东西大致在正确的位置。
需要数据库和Web应用程序开发之间的合作。）
真正有效的电子邮件地址的任意子集。 （我在什么时候见过这个
一家公司收购另一家公司，并要求每个人都
在这些电子邮件帐户实际使用之前分配一个新的电子邮件地址
创建。）
根据您选择的方式，您将需要更宽松或更严格的正则表达式。根据我的经验，大多数时候大多数人都会采取宽松的验证方式。
It depends a little on what valid means to you. To me, an email address might be valid (that is, it's formed correctly according to RFC 5322), but I might not be able to send to it. Whether you need to test a) the format of the address or b) your ability to send email to it is application-dependent.
If you just want to make sure the user's email address is in the correct format, you're stuck with validation by regular expression. Good luck with that. See RFC 3696, "3. Restrictions on email addresses" for the depressing details.
Off the top of my head, here are some guiding points that I've used in the past to help my clients make decisions about email validation.
correct mistakes. (Accept anything with an "@" symbol and a "." in
roughly the right place.)
require the user to click a link to confirm. (You can still accept
anything with an "@" symbol and a "." in roughly the right place.
Requires cooperation between the database and web app development.)
arbitrary subset of truly valid email addresses. (I've seen this when
one company buys out another company, and requires everybody to be
assigned a new email address before those email accounts are actually
created.)
Depending on which way you go, you'll need a looser or tighter regular expression. In my experience, most of the time most people go with looser validation.
根据这篇文章， SQL Server 支持 CHECK 约束中的正则表达式。
将其与可靠的电子邮件验证正则表达式结合起来，您就可以开始了。
您还可以在 LIKE 中使用.com/sql/sql_check.asp" rel="nofollow">CHECK 约束。
您还可以启用 CLR 集成 SQL Server 和 创建一个 CLR 函数（如果您使用的是 SQL Server 2005 或更高版本）并在 CHECK 约束中调用它。 CLR 函数将处理正则表达式验证。
According to this article, SQL Server supports Regular Expressions in CHECK constraints.
Couple this with a solid email validation regular expression and you should be good to go.
You can also use LIKE in the CHECK constraint.
Also you can enable CLR Integration in SQL Server and create a CLR function if you are using SQL Server 2005 or higher and call it in the CHECK constraint. The CLR function would handle the regular expression validation.