When an INSERT statement is executed to add a set of records to a table that has a unique key defined, it may happen that the value of the key in some of the records to be inserted is the same as that of records already in the table.
The default behaviour in this case is that the execution fails with an error message “execute failed: Duplicate entry”. When this happens, no record is inserted, not even those whose keys do not clash with the keys of previously existing records.
Example:
Let’s say we are developing an application that needs a “customers” table, with columns “clientid”,”name” and “email”. There will be a unique key defined on the “clientid” field.
We can create the table, and insert the first records successfully:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> create table customers (clientid integer, name text, email text);
Query OK, 0 rows affected (0.14 sec)
mysql> create unique index idx_customers on customers(clientid);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into customers (clientid, name, email) values
-> (1,'Juan Lopez','jlopez@gmail.com'),
-> (2,'Luis Fernandez','luis.fernandez@hotmail.com');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
|
Next, we want to insert two more records in the table, but the client id of one of them is already used by one of the existing records. The sentence fails with the “duplicate entry” error message.
|
|
mysql> insert into customers (clientid, name, email) values
-> (2,'Emilio Merino','emilio@empresa.com'),
-> (3,'Ana Balmes','ana@balmes.com');
ERROR 1062 (23000): Duplicate entry '2' for key 'idx_customers'
|
MySQL implements some extension to the standard SQL, that allow us to treat this event in a flexible way:
INSERT IGNORE, UPDATE IGNORE
Using the IGNORE modifier, those new records that clash with existing records are discarded, while non-conflicting records are inserted in the table:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
mysql> insert IGNORE into customers (clientid, name, email) values
-> (2,'Emilio Merino','emilio@empresa.com'),
-> (3,'Ana Balmes','ana@balmes.com');
Query OK, 1 row affected (0.02 sec)
Records: 2 Duplicates: 1 Warnings: 0
mysql> select * from customers;
+----------+----------------+----------------------------+
| clientid | name | email |
+----------+----------------+----------------------------+
| 1 | Juan Lopez | jlopez@gmail.com |
| 2 | Luis Fernandez | luis.fernandez@hotmail.com |
| 3 | Ana Balmes | ana@balmes.com |
+----------+----------------+----------------------------+
3 rows in set (0.00 sec)
|
The IGNORE modifier can also be used in a UPDATE sentence. The effect is again to discard those updates that would cause a unique key conflict.
REPLACE
A REPLACE statement can be used in place of an INSERT statement. Using REPLACE, new records that do not conflict with existing records are inserted in the table as usual. When a record to be inserted causes a duplicate key conflict, the previously existing record is deleted, and the new record is inserted:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
mysql> replace into clientes (idcliente, nombre, email) values
-> (2,'Emilio Merino','emilio@empresa.com'),
-> (3,'Ana Balmes','ana@balmes.com');
Query OK, 4 rows affected (0.00 sec)
Records: 2 Duplicates: 2 Warnings: 0
mysql> select * from customers;
+----------+---------------+--------------------+
| clientid | name | email |
+----------+---------------+--------------------+
| 1 | Juan Lopez | jlopez@gmail.com |
| 2 | Emilio Merino | emilio@empresa.com |
| 3 | Ana Balmes | ana@balmes.com |
+----------+---------------+--------------------+
3 rows in set (0.00 sec)
|
INSERT … ON DUPLICATE KEY UPDATE
The INSERT command, with the “ON DUPLICATE KEY UPDATE update-statement”, when a record to be inserted conflicts with an existing records, does not insert the new record, but executes the “update-statement” instead. This can be used to flag the existence of duplicates.
For instance, a field “total” can be added to the table customers, to keep track of the number of duplicates, as follows:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
mysql> alter table customers add column total integer not null default 1;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into customers (clientid, name, email) values
-> (2,'John Doe','john@doe.com')
-> on duplicate key update total=total+1;
Query OK, 2 rows affected (0.03 sec)
mysql> select * from customers;
+----------+---------------+--------------------+-------+
| clientid | name | email | total |
+----------+---------------+--------------------+-------+
| 1 | Juan Lopez | jlopez@gmail.com | 1 |
| 2 | Emilio Merino | emilio@empresa.com | 2 |
| 3 | Ana Balmes | ana@balmes.com | 1 |
+----------+---------------+--------------------+-------+
3 rows in set (0.00 sec)
|
As can be seen, the values of name and email in the record with clientid=2 have not been changed, but the value of the “total” column has been increased by one.
LOAD DATA INFILE ‘filename’ [ IGNORE | REPLACE ]
The LOAD DATA INFILE command can also be used with the modifiers IGNORE and REPLACE, resulting in the same behaviour as above explained for conflicting records
Komentar
Posting Komentar
Silakan dikomen...