在 MySQL 中，数据数据IGNORE 是小技选项一种在插入或更新数据时处理冲突的选项。具体来说，揭秘在 INSERT | UPDATE 语句中，让的容IGNORE 的处理作用是在插入或更新数据时忽略特定的错误，而不导致整个操作失败。数据数据另外，小技选项IGNORE 选项还可以在非空约束、揭秘写入的让的容字段内容超过字段长度时进行截断处理等，下面是处理几个具体的例子。 一、数据数据主键或唯一键冲突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.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17. 
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.PRIMARY1.2. 加上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: 1 mysql> 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.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26. 
可以看到，有1条记录冲突，但是进行了warning提示，然后继续进行其他无冲突项的高防服务器处理。 如果需查看warning信息，可以使用 show warnings 命令查看。 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)1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18. 可见，因为报错，数据未插入。 
下面通过添加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: 1 mysql> 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.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30. 
可见，和主键冲突类似，有冲突的数据将会忽略告警而继续进行后续操作。 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: 1 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>1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24. 
更新唯一键： 复制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: 1 mysql> 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>1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33. 
二、忽略非空约束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 null mysql> 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)1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24. 
结果为：会插入一个空字符串在表中而不会像正常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)1.2.3.4.5.6.7.8.9. 
2、未列出字符串类型字段名当赋值时未在字段列表中加入有非空约束的字符串类型的源码库字段时，情况如下： 复制mysql> insert into test1(id,name,c1) values (8,aaa,aa); ERROR 1364 (HY000): Field card_no doesnt have a default value mysql> 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 doesnt 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)1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22. 
可见，字段未列出时，也可以插入成功，也是将其插入一个空字符串。 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 doesnt 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>1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33. 
结果：此时插入了0 （整型的默认值）。 三、字段超长依旧进行在上述的测试表上进行测试 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 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)1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26. 结果：数据会因超长而未插入。 而使用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>1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33. 
结果：数据以截断的方式插入成功了。 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 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 | | 9 | 1000000001 | abc | a1 | +----+------------+---------+------+ 7 rows in set (0.00 sec)1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28. 
而使用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>1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38. 
结果： 会以截断的方式插入（int的最大值） 四、结语总的来说，IGNORE 提供了一种在插入或更新时处理主键、唯一键冲突、云服务器非空约束字段未赋值、字段超长等异常时内部自动处理的方法，使得操作不因为某一行的冲突而中断，而是继续处理。但也因为其特点，会导致结果与预期不符的情况。在实际操作中还是建议使用正常的方式进行处理，以免出现不必要的故障。 |
喜欢
讨厌