前言:
当前我们对“mysql非空约束关键字”都比较注重,兄弟们都想要剖析一些“mysql非空约束关键字”的相关内容。那么小编同时在网摘上搜集了一些关于“mysql非空约束关键字””的相关知识,希望我们能喜欢,看官们一起来了解一下吧!#头条创作挑战赛#
在 MySQL 中,IGNORE 是一种在插入或更新数据时处理冲突的选项。具体来说,在 INSERT | UPDATE 语句中,IGNORE 的作用是在插入或更新数据时忽略特定的错误,而不导致整个操作失败。另外,IGNORE 选项还可以在非空约束、写入的字段内容超过字段长度时进行截断处理等,下面是几个具体的例子。
1. 主键或唯一键冲突
1.1 初始化测试表并初始化数据
mysql> create table test1(id int not null primary key,card_no varchar(10) not null,name varchar(20) not null, c1 varchar(2) ,unique key uq_card_no(card_no));Query OK, 0 rows affected (0.05 sec)mysql> insert into test1(id,card_no,name,c1) values(1,'1000000000','abc','a')Query OK, 1 row affected (0.01 sec)mysql> select * from test1;+----+------------+------+------+| id | card_no | name | c1 |+----+------------+------+------+| 1 | 1000000000 | abc | a |+----+------------+------+------+1 row in set (0.00 sec)
1.2 主键冲突
插入一个表中已存在的主键数据时,如果不添加ignore,则会报主键冲突
mysql> insert into test1(id,card_no,name,c1) values(1,'1000000001','abc','a');ERROR 1062 (23000): Duplicate entry '1' for key 'test1.PRIMARY'
加上ignore选项后,结果如下:
mysql> select * from test1;+----+------------+------+------+| id | card_no | name | c1 |+----+------------+------+------+| 1 | 1000000000 | abc | a |+----+------------+------+------+1 row in set (0.00 sec)mysql> insert ignore into test1(id,card_no,name,c1) values(1,'1000000001','abc','a'), -> (2,'1000000001','ttt','b');Query OK, 1 row affected, 1 warning (0.01 sec)Records: 2 Duplicates: 1 Warnings: 1mysql> show warnings;+---------+------+---------------------------------------------+| Level | Code | Message |+---------+------+---------------------------------------------+| Warning | 1062 | Duplicate entry '1' for key 'test1.PRIMARY' |+---------+------+---------------------------------------------+1 row in set (0.00 sec)mysql> select * from test1;+----+------------+------+------+| id | card_no | name | c1 |+----+------------+------+------+| 1 | 1000000000 | abc | a || 2 | 1000000001 | ttt | b |+----+------------+------+------+2 rows in set (0.00 sec)
可以看到,有1条记录冲突,但是进行了warning提示,然后继续进行其他无冲突项的处理。
如果需查看warning信息,可以使用 show warnings 命令查看。
1.3 唯一键冲突
继续以上的表,先正常方式插入一条唯一键已存在的记录
mysql> select * from test1;+----+------------+------+------+| id | card_no | name | c1 |+----+------------+------+------+| 1 | 1000000000 | abc | a || 2 | 1000000001 | ttt | b |+----+------------+------+------+2 rows in set (0.00 sec)mysql> insert into test1(id,card_no,name,c1) values (4,'1000000000','ccccc','a');ERROR 1062 (23000): Duplicate entry '1000000000' for key 'test1.uq_card_no'mysql> select * from test1;+----+------------+------+------+| id | card_no | name | c1 |+----+------------+------+------+| 1 | 1000000000 | abc | a || 2 | 1000000001 | ttt | b |+----+------------+------+------+2 rows in set (0.00 sec)
可见,因为报错,数据未插入。
下面通过添加ignore批量插入数据
mysql> select * from test1;+----+------------+------+------+| id | card_no | name | c1 |+----+------------+------+------+| 1 | 1000000000 | abc | a || 2 | 1000000001 | ttt | b |+----+------------+------+------+2 rows in set (0.00 sec)mysql> insert ignore into test1(id,card_no,name,c1) values -> (4,'1000000000','ccccc','a'), -> (5,'1000000003','ccccabc','a');Query OK, 1 row affected, 1 warning (0.00 sec)Records: 2 Duplicates: 1 Warnings: 1mysql> show warnings;+---------+------+---------------------------------------------------------+| Level | Code | Message |+---------+------+---------------------------------------------------------+| Warning | 1062 | Duplicate entry '1000000000' for key 'test1.uq_card_no' |+---------+------+---------------------------------------------------------+1 row in set (0.00 sec)mysql> select * from test1;+----+------------+---------+------+| id | card_no | name | c1 |+----+------------+---------+------+| 1 | 1000000000 | abc | a || 2 | 1000000001 | ttt | b || 5 | 1000000003 | ccccabc | a |+----+------------+---------+------+3 rows in set (0.00 sec)mysql>
可见,和主键冲突类似,有冲突的数据将会忽略告警而继续进行后续操作。
1.4 update操作
除了insert可以搭配ignore选项,update也可以添加ignore选项,例如:
更新主键:
mysql> select * from test1;+----+------------+---------+------+| id | card_no | name | c1 |+----+------------+---------+------+| 1 | 1000000000 | abc | a || 2 | 1000000001 | ttt | b || 5 | 1000000003 | ccccabc | a |+----+------------+---------+------+3 rows in set (0.00 sec)mysql> update test1 set id = id +1;ERROR 1062 (23000): Duplicate entry '2' for key 'test1.PRIMARY'mysql> update ignore test1 set id = id +1;Query OK, 2 rows affected, 1 warning (0.01 sec)Rows matched: 3 Changed: 2 Warnings: 1mysql> select * from test1;+----+------------+---------+------+| id | card_no | name | c1 |+----+------------+---------+------+| 1 | 1000000000 | abc | a || 3 | 1000000001 | ttt | b || 6 | 1000000003 | ccccabc | a |+----+------------+---------+------+3 rows in set (0.00 sec)mysql>
更新唯一键:
mysql> select * from test1;+----+------------+---------+------+| id | card_no | name | c1 |+----+------------+---------+------+| 1 | 1000000000 | abc | a || 3 | 1000000001 | ttt | b || 6 | 1000000003 | ccccabc | a |+----+------------+---------+------+3 rows in set (0.00 sec)mysql> show warnings;Empty set (0.00 sec)mysql> update test1 set card_no=card_no +1;ERROR 1062 (23000): Duplicate entry '1000000001' for key 'test1.uq_card_no'mysql> update ignore test1 set card_no=card_no +1;Query OK, 2 rows affected, 1 warning (0.02 sec)Rows matched: 3 Changed: 2 Warnings: 1mysql> show warnings;+---------+------+---------------------------------------------------------+| Level | Code | Message |+---------+------+---------------------------------------------------------+| Warning | 1062 | Duplicate entry '1000000001' for key 'test1.uq_card_no' |+---------+------+---------------------------------------------------------+1 row in set (0.00 sec)mysql> select * from test1;+----+------------+---------+------+| id | card_no | name | c1 |+----+------------+---------+------+| 1 | 1000000000 | abc | a || 3 | 1000000002 | ttt | b || 6 | 1000000004 | ccccabc | a |+----+------------+---------+------+3 rows in set (0.00 sec)mysql>
2. 忽略非空约束
2.1 列出字段赋值为null时
当列出需赋值的字段,但是对其中的非空字段赋值为null时,结果如下:
mysql> select * from test1;+----+------------+---------+------+| id | card_no | name | c1 |+----+------------+---------+------+| 1 | 1000000000 | abc | a || 3 | 1000000002 | ttt | b || 6 | 1000000004 | ccccabc | a |+----+------------+---------+------+3 rows in set (0.00 sec)mysql> insert into test1(id,card_no,name,c1) values -> (7,'1000000005',null,'aa');ERROR 1048 (23000): Column 'name' cannot be nullmysql> insert ignore into test1(id,card_no,name,c1) values (7,'1000000005',null,'aa');Query OK, 1 row affected, 1 warning (0.01 sec)mysql> select * from test1;+----+------------+---------+------+| id | card_no | name | c1 |+----+------------+---------+------+| 1 | 1000000000 | abc | a || 3 | 1000000002 | ttt | b || 6 | 1000000004 | ccccabc | a || 7 | 1000000005 | | aa |+----+------------+---------+------+4 rows in set (0.00 sec)
结果为:会插入一个空字符串在表中而不会像正常SQL那样因为非空约束而失败。
有人疑惑,上面是空字符串么,验证结果如下:
mysql> select * from test1 where name='';+----+------------+------+------+| id | card_no | name | c1 |+----+------------+------+------+| 7 | 1000000005 | | aa |+----+------------+------+------+1 row in set (0.00 sec)mysql> select * from test1 where name is null;Empty set (0.00 sec)
2.2 未列出字符串类型字段名
当赋值时未在字段列表中加入有非空约束的字符串类型的字段时,情况如下:
mysql> insert into test1(id,name,c1) values (8,'aaa','aa');ERROR 1364 (HY000): Field 'card_no' doesn't have a default valuemysql> insert ignore into test1(id,name,c1) values (8,'aaa','aa');Query OK, 1 row affected, 1 warning (0.01 sec)mysql> show warnings;+---------+------+----------------------------------------------+| Level | Code | Message |+---------+------+----------------------------------------------+| Warning | 1364 | Field 'card_no' doesn't have a default value |+---------+------+----------------------------------------------+1 row in set (0.00 sec)mysql> select * from test1;+----+------------+---------+------+| id | card_no | name | c1 |+----+------------+---------+------+| 1 | 1000000000 | abc | a || 3 | 1000000002 | ttt | b || 6 | 1000000004 | ccccabc | a || 7 | 1000000005 | | aa || 8 | | aaa | aa |+----+------------+---------+------+5 rows in set (0.01 sec)
可见,字段未列出时,也可以插入成功,也是将其插入一个空字符串
2.3 未列整型字段时
当赋值时未在字段列表中加入有非空约束的整型类型的字段时,情况如下:
mysql> select * from test1;+----+------------+---------+------+| id | card_no | name | c1 |+----+------------+---------+------+| 1 | 1000000000 | abc | a || 3 | 1000000002 | ttt | b || 6 | 1000000004 | ccccabc | a || 7 | 1000000005 | | aa || 8 | | aaa | aa |+----+------------+---------+------+5 rows in set (0.01 sec)mysql> insert ignore into test1(card_no,name,c1) values ('1000000006','bbb','aa');Query OK, 1 row affected, 1 warning (0.00 sec)mysql> show warnings;+---------+------+-----------------------------------------+| Level | Code | Message |+---------+------+-----------------------------------------+| Warning | 1364 | Field 'id' doesn't have a default value |+---------+------+-----------------------------------------+1 row in set (0.00 sec)mysql> select * from test1;+----+------------+---------+------+| id | card_no | name | c1 |+----+------------+---------+------+| 0 | 1000000006 | bbb | aa || 1 | 1000000000 | abc | a || 3 | 1000000002 | ttt | b || 6 | 1000000004 | ccccabc | a || 7 | 1000000005 | | aa || 8 | | aaa | aa |+----+------------+---------+------+6 rows in set (0.00 sec)mysql>
结果:此时插入了0 (整型的默认值)。
3. 字段超长
依旧进行在上述的测试表上进行测试
3.1 字符串超长
当字符串类型超长时,正常结果如下:
mysql> select * from test1;+----+------------+---------+------+| id | card_no | name | c1 |+----+------------+---------+------+| 0 | 1000000006 | bbb | aa || 1 | 1000000000 | abc | a || 3 | 1000000002 | ttt | b || 6 | 1000000004 | ccccabc | a || 7 | 1000000005 | | aa || 8 | | aaa | aa |+----+------------+---------+------+6 rows in set (0.00 sec)mysql> insert into test1(id,card_no,name,c1) values(9,'1000000001','abc','a12345');ERROR 1406 (22001): Data too long for column 'c1' at row 1mysql> select * from test1;+----+------------+---------+------+| id | card_no | name | c1 |+----+------------+---------+------+| 0 | 1000000006 | bbb | aa || 1 | 1000000000 | abc | a || 3 | 1000000002 | ttt | b || 6 | 1000000004 | ccccabc | a || 7 | 1000000005 | | aa || 8 | | aaa | aa |+----+------------+---------+------+6 rows in set (0.00 sec)
结果:数据会因超长而未插入。
而使用ignore选项后,结果如下:
mysql> insert ignore into test1(id,card_no,name,c1) values(9,'1000000001','abc','a12345');Query OK, 1 row affected, 1 warning (0.01 sec)mysql> show warnings;+---------+------+-----------------------------------------+| Level | Code | Message |+---------+------+-----------------------------------------+| Warning | 1265 | Data truncated for column 'c1' at row 1 |+---------+------+-----------------------------------------+1 row in set (0.00 sec)mysql> select * from test1;+----+------------+---------+------+| id | card_no | name | c1 |+----+------------+---------+------+| 0 | 1000000006 | bbb | aa || 1 | 1000000000 | abc | a || 3 | 1000000002 | ttt | b || 6 | 1000000004 | ccccabc | a || 7 | 1000000005 | | aa || 8 | | aaa | aa || 9 | 1000000001 | abc | a1 |+----+------------+---------+------+7 rows in set (0.00 sec)mysql> desc test1;+---------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+---------+-------------+------+-----+---------+-------+| id | int | NO | PRI | NULL | || card_no | varchar(10) | NO | UNI | NULL | || name | varchar(20) | NO | | NULL | || c1 | varchar(2) | YES | | NULL | |+---------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec)mysql>
结果:数据以截断的方式插入成功了。
3.2 整型数据超长
当普通方式插入一个超过int类型最大值的数据时,会直接因数据超过范围而报错。例如:
mysql> select * from test1;+----+------------+---------+------+| id | card_no | name | c1 |+----+------------+---------+------+| 0 | 1000000006 | bbb | aa || 1 | 1000000000 | abc | a || 3 | 1000000002 | ttt | b || 6 | 1000000004 | ccccabc | a || 7 | 1000000005 | | aa || 8 | | aaa | aa || 9 | 1000000001 | abc | a1 |+----+------------+---------+------+7 rows in set (0.00 sec)mysql> insert into test1(id,card_no,name,c1) values(999999999999999999999,'1000000003','abc','a2');ERROR 1264 (22003): Out of range value for column 'id' at row 1mysql> select * from test1;+----+------------+---------+------+| id | card_no | name | c1 |+----+------------+---------+------+| 0 | 1000000006 | bbb | aa || 1 | 1000000000 | abc | a || 3 | 1000000002 | ttt | b || 6 | 1000000004 | ccccabc | a || 7 | 1000000005 | | aa || 8 | | aaa | aa || 9 | 1000000001 | abc | a1 |+----+------------+---------+------+7 rows in set (0.00 sec)
而使用ignore选项后,可以插入数据,例如:
mysql> select * from test1;+----+------------+---------+------+| id | card_no | name | c1 |+----+------------+---------+------+| 0 | 1000000006 | bbb | aa || 1 | 1000000000 | abc | a || 3 | 1000000002 | ttt | b || 6 | 1000000004 | ccccabc | a || 7 | 1000000005 | | aa || 8 | | aaa | aa || 9 | 1000000001 | abc | a1 |+----+------------+---------+------+7 rows in set (0.00 sec)mysql> insert ignore into test1(id,card_no,name,c1) values(999999999999999999999,'1000000003','abc','a2');Query OK, 1 row affected, 2 warnings (0.01 sec)mysql> show warnings;+---------+------+---------------------------------------------+| Level | Code | Message |+---------+------+---------------------------------------------+| Warning | 1264 | Out of range value for column 'id' at row 1 || Warning | 1264 | Out of range value for column 'id' at row 1 |+---------+------+---------------------------------------------+2 rows in set (0.00 sec)mysql> select * from test1;+------------+------------+---------+------+| id | card_no | name | c1 |+------------+------------+---------+------+| 0 | 1000000006 | bbb | aa || 1 | 1000000000 | abc | a || 3 | 1000000002 | ttt | b || 6 | 1000000004 | ccccabc | a || 7 | 1000000005 | | aa || 8 | | aaa | aa || 9 | 1000000001 | abc | a1 || 2147483647 | 1000000003 | abc | a2 |+------------+------------+---------+------+8 rows in set (0.00 sec)mysql>
结果: 会以截断的方式插入(int的最大值)
4. 结语
总的来说,IGNORE 提供了一种在插入或更新时处理主键、唯一键冲突、非空约束字段未赋值、字段超长等异常时内部自动处理的方法,使得操作不因为某一行的冲突而中断,而是继续处理。但也因为其特点,会导致结果与预期不符的情况。在实际操作中还是建议使用正常的方式进行处理,以免出现不必要的故障。
扫码关注
标签: #mysql非空约束关键字