SQL 身份证验证约束

最新推荐文章于 2021-03-03 17:19:58 发布

炎神魂

最新推荐文章于 2021-03-03 17:19:58 发布

阅读量4.7k

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL 文章标签： sql 数据库 CHECK约束表达式身份证

本文链接：https://blog.youkuaiyun.com/a758397318/article/details/22829875

SQL 专栏收录该内容

1 篇文章

订阅专栏

因在数据库中设计一个字段，存储身份信息，网上找不到好的方法来不允许不合法的身份号码存储到记录中，编者只有自己写了一个只允许在数据库中输入合法身份证的约束，供大家参考

在设计身份证约束前，我们先设计出生日期的约束

首先，在表中建立一个字段，下面以IDCardNumber_Birthday为例，字段类型为 nchar(8)

打开对象资源管理器，选择要设置约束的表展开，右键点击约束，选择新建约束

在CHECK约束界面，点击表达式右边的编辑按钮

将以下表达式复制到弹出编辑界面（记得将下列代码中IDCardNumber_Birthday替换为自己的字段名）：

([IDCardNumber_Birthday] like '[1-2][0,9][0-9][0-9][0-1][0-9][0-3][0-9]' AND ((substring([IDCardNumber_Birthday],(1),(1))='1' AND substring([IDCardNumber_Birthday],(2),(1))='9' OR substring([IDCardNumber_Birthday],(1),(1))='2' AND substring([IDCardNumber_Birthday],(2),(1))='0') AND(substring([IDCardNumber_Birthday],(5),(1))='1' AND substring([IDCardNumber_Birthday],(6),(1))<='2' OR substring([IDCardNumber_Birthday],(5),(1))='0' AND (substring([IDCardNumber_Birthday],(6),(1))>='1' AND substring([IDCardNumber_Birthday],(6),(1))<>'2' OR substring([IDCardNumber_Birthday],(6),(1))='2' AND (substring([IDCardNumber_Birthday],(7),(1))<'2' OR substring([IDCardNumber_Birthday],(7),(1))='2' AND ((substring([IDCardNumber_Birthday],(3),(1))*(10)+substring([IDCardNumber_Birthday],(4),(1)))%(4)=(0) OR (substring([IDCardNumber_Birthday],(3),(1))*(10)+substring([IDCardNumber_Birthday],(4),(1)))%(4)<>(0) AND substring([IDCardNumber_Birthday],(8),(1))<>'9')))) AND (substring([IDCardNumber_Birthday],(7),(1))='3' AND ((substring([IDCardNumber_Birthday],(6),(1))='4' OR substring([IDCardNumber_Birthday],(6),(1))='6' OR substring([IDCardNumber_Birthday],(6),(1))='9' OR substring([IDCardNumber_Birthday],(5),(1))='1' AND substring([IDCardNumber_Birthday],(6),(1))='1') AND substring([IDCardNumber_Birthday],(8),(1))='0' OR (substring([IDCardNumber_Birthday],(6),(1))='3' OR substring([IDCardNumber_Birthday],(6),(1))='5' OR substring([IDCardNumber_Birthday],(6),(1))='7' OR substring([IDCardNumber_Birthday],(6),(1))='8' OR substring([IDCardNumber_Birthday],(5),(1))='0' AND substring([IDCardNumber_Birthday],(6),(1))='1' OR substring([IDCardNumber_Birthday],(5),(1))='1' AND substring([IDCardNumber_Birthday],(6),(1))='0' OR substring([IDCardNumber_Birthday],(5),(1))='1' AND substring([IDCardNumber_Birthday],(6),(1))='2') AND substring([IDCardNumber_Birthday],(8),(1))<='1') OR substring([IDCardNumber_Birthday],(7),(1))='0' AND substring([IDCardNumber_Birthday],(8),(1))>='1' OR substring([IDCardNumber_Birthday],(7),(1))='2' OR substring([IDCardNumber_Birthday],(7),(1))='1')))

以上表达式将年约束在1900-2099之间，如不需此约束，请删去红色部分

下面身份证字段的约束设计，我们都知道，身份证的组成结构是6位地址码，接下来是8位出生日期，然后是3位随机序号（第3位单数为男，双数为女），最后1位校检位，我们先设计一个IDCradNumber字段，类型nchar(18)为避免出现不合法的出生日期，必须使用上面的出生日期约束，只需要将上面代码的substring()函数第二个参数的值加6（出生日期在6位地址码后面），再和校检表达式做AND运算即可

表达式如下：

([IDCardNumber] like '[1-6][1-7][0-9][0-9][0-9][0-9][1-2][0,9][0-9][0-9][0-1][0-9][0-3][0-9][0-9][0-9][0-9][0-9,X,x]'

AND ((substring([IDCardNumber],(7),(1))='1' AND substring([IDCardNumber],(8),(1))='9' OR substring([IDCardNumber],(7),(1))='2' AND substring([IDCardNumber],(8),(1))='0') AND (substring([IDCardNumber],(11),(1))='1' AND substring([IDCardNumber_Birthday],(12),(1))<='2' OR substring([IDCardNumber],(11),(1))='0' AND (substring([IDCardNumber],(12),(1))>='1' AND substring([IDCardNumber],(12),(1))<>'2' OR substring([IDCardNumber],(12),(1))='2' AND (substring([IDCardNumber],(13),(1))<'2' OR substring([IDCardNumber],(13),(1))='2' AND ((substring([IDCardNumber],(9),(1))*(10)+substring([IDCardNumber],(10),(1)))%(4)=(0) OR (substring([IDCardNumber],(9),(1))*(10)+substring([IDCardNumber],(10),(1)))%(4)<>(0) AND substring([IDCardNumber],(14),(1))<>'9')))) AND (substring([IDCardNumber],(13),(1))='3' AND ((substring([IDCardNumber],(12),(1))='4' OR substring([IDCardNumber],(12),(1))='6' OR substring([IDCardNumber],(12),(1))='9' OR substring([IDCardNumber],(11),(1))='1' AND substring([IDCardNumber],(12),(1))='1') AND substring([IDCardNumber],(14),(1))='0' OR (substring([IDCardNumber],(12),(1))='3' OR substring([IDCardNumber],(12),(1))='5' OR substring([IDCardNumber],(12),(1))='7' OR substring([IDCardNumber],(12),(1))='8' OR substring([IDCardNumber],(11),(1))='0' AND substring([IDCardNumber],(12),(1))='1' OR substring([IDCardNumber],(11),(1))='1' AND substring([IDCardNumber],(12),(1))='0' OR substring([IDCardNumber],(11),(1))='1' AND substring([IDCardNumber],(12),(1))='2') AND substring([IDCardNumber],(14),(1))<='1') OR substring([IDCardNumber],(13),(1))='0' AND substring([IDCardNumber],(14),(1))>='1' OR substring([IDCardNumber],(13),(1))='2' OR substring([IDCardNumber],(13),(1))='1')))

AND

(((substring([IDCardNumber],(18),(1))<>’x’ or substring([IDCardNumber],(18),(1))<>’X’) and (substring([IDCardNumber],(1),(1))*(7)+ substring([IDCardNumber],(2),(1))*(9) + substring([IDCardNumber],(3),(1))*(10) + substring([IDCardNumber],(4),(1))*(5) + substring([IDCardNumber],(5),(1))*(8) + substring([IDCardNumber],(6),(1))*(4) + substring([IDCardNumber],(7),(1))*(2) + substring([IDCardNumber],(8),(1))*(1) + substring([IDCardNumber],(9),(1))*(6) + substring([IDCardNumber],(10),(1))*(3) + substring([IDCardNumber],(11),(1))*(7) + substring([IDCardNumber],(12),(1))*(9) + substring([IDCardNumber],(13),(1))*(10) + substring([IDCardNumber],(14),(1))*(5) + substring([IDCardNumber],(15),(1))*(8) + substring([IDCardNumber],(16),(1))*(4) + substring([IDCardNumber],(17),(1))*(2))%11= (12-substring([IDCardNumber],(18),(1)))%11) or

((substring([IDCardNumber],(18),(1))=’x’ or substring([IDCardNumber],(18),(1))=’X’) and (substring([IDCardNumber],(1),(1))*(7)+ substring([IDCardNumber],(2),(1))*(9) + substring([IDCardNumber],(3),(1))*(10) + substring([IDCardNumber],(4),(1))*(5) + substring([IDCardNumber],(5),(1))*(8) + substring([IDCardNumber],(6),(1))*(4) + substring([IDCardNumber],(7),(1))*(2) + substring([IDCardNumber],(8),(1))*(1) + substring([IDCardNumber],(9),(1))*(6) + substring([IDCardNumber],(10),(1))*(3) + substring([IDCardNumber],(11),(1))*(7) + substring([IDCardNumber],(12),(1))*(9) + substring([IDCardNumber],(13),(1))*(10) + substring([IDCardNumber],(14),(1))*(5) + substring([IDCardNumber],(15),(1))*(8) + substring([IDCardNumber],(16),(1))*(4) + substring([IDCardNumber],(17),(1))*(2))%11=2)

)

以上表达式，蓝色为增加的字符约束及身份证校检约束

为避免不合法的地址码出现在身份信息中，编者的建议是，将身份证字段拆分成两个字段，一部分存储地址码，一部分存储剩余的号码，再去下载地址码的Excel表（百度文库就有），再到数据库中建张新表，将地址编码导入到新表忠，然后把存储身份证信息的表建立外键参照新表的地址码，至于substring()函数的字符偏移量，就要读者自己去修改了