Primary key Unique key repeat insert workaround

[MySQL Diary] primary key unique key repeat insert workaround

When we insert data, it is possible to encounter the problem of repeated data insertion, but the data is not allowed to have duplicate values:

?

1

1. CREATE TABLE stuInfo (
2. id INT NOT NULL COMMENT ‘序号‘,
3. name VARCHAR(20) NOT NULL DEFAULT ‘‘ COMMENT ‘姓名‘,
4. age INT NOT NULL DEFAULT 0 COMMENT ‘年龄‘,
5. PRIMARY KEY (id),
6. UNIQUE KEY uniq_name(name)
7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘学生表‘;

?

1

1. mysql> INSERT INTO stuInfo (id,name,age) VALUES (1,‘yoona‘,20),(1,‘xiaosi‘,25),(2,‘aa‘,24);
2. ERROR 1062 (23000): Duplicate entry ‘1‘ for key ‘PRIMARY‘

Solution:

1. IGNORE

Use Ignore to automatically ignore duplicate record rows when the inserted value encounters a primary key (PRIMARY key) or a unique key, and does not affect the insertion of subsequent record lines.

?

1

1. INSERT IGNORE INTO stuInfo (name,birthday,is_deleted) VALUES (‘yoona‘,‘1990-01-05‘,0),(‘aa‘,‘1990-01-16‘,0),(‘bb‘,‘1990-01-17‘,0);

Operation Result:
?

1

2. mysql> INSERT IGNORE INTO stuInfo (id,name,age) VALUES (1,‘yoona‘,20),(1,‘xiaosi‘,25),(2,‘aa‘,24);
4. Query OK, 2 rows affected (0.02 sec)
6. Records: 3 Duplicates: 1 Warnings: 0
10. mysql> select * from stuInfo;
12. +----+-------+-----+
14. | id | name | age |
16. +----+-------+-----+
18. | 1 | yoona | 20 |
20. | 2 | aa | 24 |
22. +----+-------+-----+
24. 2 rows in set (0.00 sec)

We can see from the running results that only two lines are affected, meaning that (1, ' Yoona ', 20) data is inserted, (1, ' Xiaosi ', 25) The data is automatically ignored, (2, ' AA ', 24) The data is not repeated and will not be affected by duplicate data;

2.REPLACE

Use replace when the inserted record encounters a primary key or a unique key is repeated, delete the duplicate row of records in the table before inserting.

?

1

1. mysql> REPLACE INTO stuInfo (name,birthday,is_deleted) VALUES (‘yoona‘,‘1990-01-15‘,0),(‘yoona‘,‘1990-02-16‘,0),(‘aa‘,‘1990-01-13‘,0);
2. Query OK, 4 rows affected (0.02 sec)
3. Records: 3 Duplicates: 1 Warnings: 0

Operation Result:

?

1

1. mysql> select * from stuInfo; +----+-------+------------+------------+
2. | id | name | birthday | is_deleted |
3. +----+-------+------------+------------+
4. | 21 | yoona | 1990-02-16 | 0 |
5. | 22 | aa | 1990-01-13 | 0 |
6. +----+-------+------------+------------+
7. 2 rows in set (0.00 sec)

The information from the output can be seen to be 4 rows affected, stating that it was first inserted (' Yoona ', ' 1990-01-15 ', 0) and then deleted (' Yoona ', ' 1990-01-15 ', 0).

3.ON DUPLICATE KEY UPDATE

When the inserted record encounters a primary key or a unique key is duplicated, the update operation defined later is performed. Equivalent to performing an insert operation, and then performing an update operation based on the primary key or unique key.

?

1

1. DROP TABLE IF EXISTS stuInfo;
2. CREATE TABLE stuInfo (
3. id INT NOT NULL COMMENT ‘序号‘,
4. name VARCHAR(20) NOT NULL DEFAULT ‘‘ COMMENT ‘姓名‘,
5. age INT NOT NULL DEFAULT 0 COMMENT ‘年龄‘,
6. PRIMARY KEY (id),
7. UNIQUE KEY uniq_name(name)
8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘学生表‘;

After the on DUPLICATE KEY update, the values are explained:

Vaules (age) refers to the value of the record to be inserted

Age refers to the value of the table itself, which is inserted.

(1) The first case:

?

1

1. #VALUES(age) 待插入值 25
2. INSERT INTO stuInfo (id,name,age) VALUES (1,‘yoona‘,20),(1,‘xiaosi‘,25) ON DUPLICATE KEY UPDATE age = VALUES(age) + 1;

Equivalent:

?

1

1. INSERT INTO stuInfo (id,name,age) VALUES (1,‘yoona‘,20);
2. UPDATE stuInfo
3. SET age = VALUES(age) + 1
4. WHERE id = 1;

Operation Result:

?

1

1. mysql> INSERT into StuInfo ( Id,name,age) VALUES (1, ' Yoona ', +), (1, ' Xiaosi ', ") on DUPLICATE KEY UPDATE-age = VALUES (age) + 1;
2. query OK, 3 rows affected (0.01 sec)
3. records:2 duplicates:1 warnings:0
4.  
5. mysql> select * from Stuinfo;
6. +----+-------+-----+
7. | ID | name | age |
8. +----+-------+-----+
9. | 1 | yoona | |
10. +----+-------+-----+
11. 1 row in Set (0.00 sec)

(2) The second case:

?

1

1. #age 已插入值 20
2. INSERT INTO stuInfo (id,name,age) VALUES (1,‘yoona‘,20),(1,‘xiaosi‘,25) ON DUPLICATE KEY UPDATE age = age + 1;

Equivalent:

?

1

1. INSERT INTO stuInfo (id,name,age) VALUES (1,‘yoona‘,20);
2. UPDATE stuInfo
3. SET age = age + 1
4. WHERE id = 1;

Operation Result:

?

1

1. mysql> INSERT INTO stuInfo (id,name,age) VALUES (1,‘yoona‘,20),(1,‘xiaosi‘,25) ON DUPLICATE KEY UPDATE age = age + 1;
2. Query OK, 3 rows affected (0.02 sec)
3. Records: 2 Duplicates: 1 Warnings: 0
5. mysql> select * from stuInfo;
6. +----+-------+-----+
7. | id | name | age |
8. +----+-------+-----+
9. | 1 | yoona | 21 |
10. +----+-------+-----+
11. 1 row in set (0.00 sec)

If you encounter repeated insertions of data, the on DUPLICATE KEY update is used to modify the data that has been inserted, you can use the Get duplicate inserted data (directly using the field name), or you can get the duplicate data to insert (values (field name)). We do not insert the data to be inserted again.

Duplicate inserted data: In the example above (1, ' Yoona ', 20)

Repeat the data to be inserted: In the example above (1, ' Yoona ', 25)

Primary key Unique key repeat insert workaround