數(shù)據(jù)庫SQL小技巧大揭秘：IGNORE選項讓你的數(shù)據(jù)處理更從容
作者：懂點IT的耿小廚
IGNORE 提供了一種在插入或更新時處理主鍵、唯一鍵沖突、非空約束字段未賦值、字段超長等異常時內(nèi)部自動處理的方法，使得操作不因為某一行的沖突而中斷，而是繼續(xù)處理。
在 MySQL 中，IGNORE 是一種在插入或更新數(shù)據(jù)時處理沖突的選項。具體來說，在 INSERT | UPDATE 語句中，IGNORE 的作用是在插入或更新數(shù)據(jù)時忽略特定的錯誤，而不導(dǎo)致整個操作失敗。另外，IGNORE 選項還可以在非空約束、寫入的字段內(nèi)容超過字段長度時進行截斷處理等，下面是幾個具體的例子。
一、主鍵或唯一鍵沖突
1、初始化測試表并初始化數(shù)據(jù)
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)
2、主鍵沖突
插入一個表中已存在的主鍵數(shù)據(jù)時，如果不添加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選項后，結(jié)果如下：
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條記錄沖突，但是進行了warning提示，然后繼續(xù)進行其他無沖突項的處理。
如果需查看warning信息，可以使用 show warnings 命令查看。
3、唯一鍵沖突
繼續(xù)以上的表，先正常方式插入一條唯一鍵已存在的記錄。
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)可見，因為報錯，數(shù)據(jù)未插入。
下面通過添加ignore批量插入數(shù)據(jù)。
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>
可見，和主鍵沖突類似，有沖突的數(shù)據(jù)將會忽略告警而繼續(xù)進行后續(xù)操作。
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>
更新唯一鍵：
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、列出字段賦值為null時
當(dāng)列出需賦值的字段，但是對其中的非空字段賦值為null時，結(jié)果如下：
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)
結(jié)果為：會插入一個空字符串在表中而不會像正常SQL那樣因為非空約束而失敗。
有人疑惑，上面是空字符串么，驗證結(jié)果如下：
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、未列出字符串類型字段名
當(dāng)賦值時未在字段列表中加入有非空約束的字符串類型的字段時，情況如下：
mysql> insert into test1(id,name,c1) values (8,'aaa','aa');
ERROR 1364 (HY000): Field 'card_no' doesn't 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' 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)
可見，字段未列出時，也可以插入成功，也是將其插入一個空字符串。
3、未列整型字段時
當(dāng)賦值時未在字段列表中加入有非空約束的整型類型的字段時，情況如下：
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>
結(jié)果：此時插入了0 （整型的默認值）。
三、字段超長
依舊進行在上述的測試表上進行測試
1、字符串超長
當(dāng)字符串類型超長時，正常結(jié)果如下：
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)結(jié)果：數(shù)據(jù)會因超長而未插入。
而使用ignore選項后，結(jié)果如下：
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>
結(jié)果：數(shù)據(jù)以截斷的方式插入成功了。
2、整型數(shù)據(jù)超長
當(dāng)普通方式插入一個超過int類型最大值的數(shù)據(jù)時，會直接因數(shù)據(jù)超過范圍而報錯。例如：
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)
而使用ignore選項后，可以插入數(shù)據(jù)，例如：
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>
結(jié)果： 會以截斷的方式插入（int的最大值）
四、結(jié)語
總的來說，IGNORE 提供了一種在插入或更新時處理主鍵、唯一鍵沖突、非空約束字段未賦值、字段超長等異常時內(nèi)部自動處理的方法，使得操作不因為某一行的沖突而中斷，而是繼續(xù)處理。但也因為其特點，會導(dǎo)致結(jié)果與預(yù)期不符的情況。在實際操作中還是建議使用正常的方式進行處理，以免出現(xiàn)不必要的故障。
責(zé)任編輯：姜華
來源：
數(shù)據(jù)庫干貨鋪
