mysql - 在 MYSQL 中为电话或邮政编码创建约束？

Question

创建下表(MySQL 8.0):

create table Customer_Dim(
customer_ID     int not null,
FirstName       varchar(30) not null,
LastName        varchar(30) not null,
Gender          char(1) not null CHECK (gender in ('m','f')),
Street1         varchar(100) not null,
Street2         varchar(100) null,
City            varchar(30) not null,
StateAbbrev     char(2) not null,
ZipCode         char(5) not null CHECK (ZipCode LIKE repeat('[0-9]',5)),
PrimaryPhone    varchar(10) not null CHECK (PrimaryPhone LIKE repeat('[0-9]',10)),
EmailAddress    varchar(50) default null,
constraint custkey Primary Key(customer_ID)
);

但是当我输入以下插入语句时:

insert into Customer_DIM
values (10001, 'Josh', 'Dexter', 'M', '123 E Elm st.', null, 'Denver', 'CO', '80002', '2023459767', 'test@aol.com')

它无法通过电话和邮政编码检查。据我所知，它应该有效吗？

Answer 1

我相信 regexp 需要 repeat(重复字符)

使用 repeat 会得到这样的结果:

mysql> select repeat('[0-9]', 5);

+---------------------------+
| repeat('[0-9]', 5)        |
+---------------------------+
| [0-9][0-9][0-9][0-9][0-9] |
+---------------------------+

因此，请改用 regexp:

create table Customer_Dim(
customer_ID     int not null,
FirstName       varchar(30) not null,
LastName        varchar(30) not null,
Gender          char(1) not null CHECK (gender in ('m','f')),
Street1         varchar(100) not null,
Street2         varchar(100) null,
City            varchar(30) not null,
StateAbbrev     char(2) not null,
ZipCode         char(5) not null CHECK (ZipCode regexp '[0-9]{5}'),
PrimaryPhone    varchar(10) not null CHECK (PrimaryPhone regexp'[0-9]{10}'),
EmailAddress    varchar(50) default null,
constraint custkey Primary Key(customer_ID)
);

相同(好的)示例插入:

insert into Customer_Dim
values (10001, 'Josh', 'Dexter', 'M', '123 E Elm st.', null, 'Denver', 'CO', '80002', '2023459767', 'test@aol.com')

坏手机示例插入:

insert into Customer_Dim
values (10001, 'Josh', 'Dexter', 'M', '123 E Elm st.', null, 'Denver', 'CO', '80002', '202359767', 'test@aol.com')

给出错误:

Check constraint 'Customer_Dim_chk_3' is violated.

错误的 zip 示例插入:

insert into Customer_Dim
values (10001, 'Josh', 'Dexter', 'M', '123 E Elm st.', null, 'Denver', 'CO', '8002', '2023459767', 'test@aol.com')

给出错误:

Check constraint 'Customer_Dim_chk_2' is violated.

数据库fiddle example包含以上所有信息。(包括最后的 repeat 示例)

知道在8.0.16版本之前，MySQL检查约束被解析但被忽略: