成人免费xxxxx在线视频软件_久久精品久久久_亚洲国产精品久久久_天天色天天色_亚洲人成一区_欧美一级欧美三级在线观看

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

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

在 MySQL 中，IGNORE 是一種在插入或更新數(shù)據(jù)時處理沖突的選項。具體來說，在 INSERT | UPDATE 語句中，IGNORE 的作用是在插入或更新數(shù)據(jù)時忽略特定的錯誤，而不導致整個操作失敗。另外，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選項后，結果如下：

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時

當列出需賦值的字段，但是對其中的非空字段賦值為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)

圖片

結果為：會插入一個空字符串在表中而不會像正常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、未列出字符串類型字段名

當賦值時未在字段列表中加入有非空約束的字符串類型的字段時，情況如下：

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、未列整型字段時

當賦值時未在字段列表中加入有非空約束的整型類型的字段時，情況如下：

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 （整型的默認值）。

三、字段超長

依舊進行在上述的測試表上進行測試

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)

結果：數(shù)據(jù)會因超長而未插入。

而使用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>

圖片

結果：數(shù)據(jù)以截斷的方式插入成功了。

2、整型數(shù)據(jù)超長

當普通方式插入一個超過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>

圖片

結果： 會以截斷的方式插入（int的最大值）

四、結語

總的來說，IGNORE 提供了一種在插入或更新時處理主鍵、唯一鍵沖突、非空約束字段未賦值、字段超長等異常時內(nèi)部自動處理的方法，使得操作不因為某一行的沖突而中斷，而是繼續(xù)處理。但也因為其特點，會導致結果與預期不符的情況。在實際操作中還是建議使用正常的方式進行處理，以免出現(xiàn)不必要的故障。

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

2018-09-08 17:17:52

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

2024-09-25 14:16:35

2023-09-25 13:19:41

pandasPython

2020-05-07 17:03:49

Python編碼開發(fā)

2023-11-28 10:17:37

2018-07-16 00:09:30

數(shù)據(jù)科學大數(shù)據(jù)機器學習

2023-11-29 13:56:00

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

2021-05-19 08:21:09

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

2024-06-24 00:05:00

Python代碼

2024-10-09 17:22:20

Python庫

2011-08-19 13:28:25

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

2020-08-31 10:48:11

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

2010-05-31 17:15:39

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

2018-09-17 16:30:24

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

2018-08-14 11:05:25

2019-08-27 17:32:10

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

2009-05-18 13:18:54

字符Oracle字符串

2010-10-09 10:34:12

SQL Azure云數(shù)

2011-03-08 09:27:34

SQL Server數(shù)死鎖

2011-03-01 16:30:55

Oracle
點贊
收藏

51CTO技術棧公眾號

主站蜘蛛池模板： 91一区| 午夜精品久久久久久不卡欧美一级 | 亚洲精品在线免费观看视频 | 精品一级电影 | 国产精品1区2区3区 欧美 中文字幕 | 丝袜美腿一区二区三区动态图 | 久久久久久久久久久久91 | 欧美影院 | 日韩中文av在线 | 免费精品在线视频 | 久久久久久久久久毛片 | 欧美成人一区二区 | 日韩欧美二区 | 亚洲一区 | 国产精品一区二区在线 | 欧美精品久久久久久久久老牛影院 | 丝袜 亚洲 欧美 日韩 综合 | www.五月婷婷.com | 蜜桃在线视频 | 狠狠综合网 | 精品久久久久久久久久久久久久 | 国产一区二区在线视频 | 精品国产一区二区三区久久 | 亚洲一区二区三区在线播放 | 97精品国产97久久久久久免费 | 欧美激情综合 | 成年视频在线观看 | 中文字幕亚洲欧美日韩在线不卡 | 天天视频一区二区三区 | 亚洲综合色自拍一区 | 成人午夜视频在线观看 | 色综合一区二区 | www.色五月.com | 日日摸日日添日日躁av | 天堂一区二区三区 | 一区二区三区日本 | 超碰免费在线观看 | 久视频在线 | 天天看夜夜 | 国产激情视频在线观看 | 自拍在线|