自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

數(shù)據(jù)庫SQL小技巧大揭秘：IGNORE選項(xiàng)讓你的數(shù)據(jù)處理更從容

作者：懂點(diǎn)IT的耿小廚
數(shù)據(jù)庫 MySQL
IGNORE 提供了一種在插入或更新時處理主鍵、唯一鍵沖突、非空約束字段未賦值、字段超長等異常時內(nèi)部自動處理的方法，使得操作不因?yàn)槟骋恍械臎_突而中斷，而是繼續(xù)處理。

在 MySQL 中，IGNORE 是一種在插入或更新數(shù)據(jù)時處理沖突的選項(xiàng)。具體來說，在 INSERT | UPDATE 語句中，IGNORE 的作用是在插入或更新數(shù)據(jù)時忽略特定的錯誤，而不導(dǎo)致整個操作失敗。另外，IGNORE 選項(xiàng)還可以在非空約束、寫入的字段內(nèi)容超過字段長度時進(jìn)行截斷處理等，下面是幾個具體的例子。

一、主鍵或唯一鍵沖突

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選項(xiàng)后，結(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條記錄沖突，但是進(jìn)行了warning提示，然后繼續(xù)進(jìn)行其他無沖突項(xiàng)的處理。

如果需查看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)

可見，因?yàn)閳箦e，數(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ù)進(jìn)行后續(xù)操作。

4、update操作

除了insert可以搭配ignore選項(xiàng)，update也可以添加ignore選項(xiàng)，例如：

更新主鍵：

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那樣因?yàn)榉强占s束而失敗。

有人疑惑，上面是空字符串么，驗(yàn)證結(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 （整型的默認(rèn)值）。

三、字段超長

依舊進(jìn)行在上述的測試表上進(jìn)行測試

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選項(xiàng)后，結(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選項(xiàng)后，可以插入數(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)部自動處理的方法，使得操作不因?yàn)槟骋恍械臎_突而中斷，而是繼續(xù)處理。但也因?yàn)槠涮攸c(diǎn)，會導(dǎo)致結(jié)果與預(yù)期不符的情況。在實(shí)際操作中還是建議使用正常的方式進(jìn)行處理，以免出現(xiàn)不必要的故障。

責(zé)任編輯：姜華 來源： 數(shù)據(jù)庫干貨鋪
MySQLIGNORE
相關(guān)推薦

2018-09-08 17:17:52

數(shù)據(jù)庫MySQL小技巧

2024-09-25 14:16:35

2023-09-25 13:19:41

pandasPython

2023-11-29 13:56:00

數(shù)據(jù)技巧

2011-08-19 13:28:25

海量數(shù)據(jù)索引優(yōu)化

2020-08-31 10:48:11

MySQL數(shù)據(jù)庫數(shù)據(jù)庫技巧

2024-06-24 00:05:00

Python代碼

2010-05-31 17:15:39

MySQL數(shù)據(jù)庫

2018-09-17 16:30:24

數(shù)據(jù)庫MySQL小技巧

2024-10-09 17:22:20

Python庫

2020-05-07 17:03:49

Python編碼開發(fā)

2023-11-28 10:17:37

2018-07-16 00:09:30

數(shù)據(jù)科學(xué)大數(shù)據(jù)機(jī)器學(xué)習(xí)

2009-05-18 13:18:54

字符Oracle字符串

2011-03-08 09:27:34

SQL Server數(shù)死鎖

2018-08-14 11:05:25

2019-08-27 17:32:10

數(shù)據(jù)處理PandasPython

2011-03-01 16:30:55

Oracle

2011-03-11 16:25:53

Oracle數(shù)據(jù)庫

2024-12-03 09:28:54

元組數(shù)據(jù)庫
點(diǎn)贊
收藏

51CTO技術(shù)棧公眾號