Detailed explanation of several examples of insert and batch statements in MySQL

Preface

The most common way is to set a primary key or unique index for the field. When inserting duplicate data, an error is thrown and the program terminates, but this will cause trouble for subsequent processing. Therefore, special processing is required for the insert statement to avoid or ignore exceptions as much as possible. I will briefly introduce it below. Interested friends can try it:

For the convenience of demonstration, I created a new user test table with four fields: id, username, sex, and address. The primary key is id (auto-increment), and a unique index (idx_username) is set for the username field.

1.insert ignore into

That is, when inserting data, if the data exists, the insertion is ignored. The prerequisite is that the inserted data field has a primary key or unique index set. The test SQL statement is as follows. When inserting this data, the MySQL database will first search for existing data (that is, the idx_username index). If it exists, the insertion is ignored. If it does not exist, the data is inserted normally:

INSERT IGNORE INTO user (username,sex,address) VALUES ('hanpang','boy','HongKong')

2. on duplicate key update

That is, when inserting data, if the data exists, an update operation is performed. The prerequisite is the same as above, and the inserted data field is also set with a primary key or unique index. The test SQL statement is as follows. When inserting this record, the MySQL database will first retrieve the existing data (idx_username index). If it exists, an update operation is performed. If it does not exist, it is directly inserted:

INSERT IGNORE INTO user (username,sex,address) VALUES ('hanpang','boy','HongKong')
on duplicate key update
SEX='boy',address='HongKong'

3. replace into

That is, when inserting data, if the data exists, delete it and then insert it. The prerequisite is the same as above. The inserted data field needs to set the primary key or unique index. The test SQL statement is as follows. When inserting this record, the MySQL database will first retrieve the existing data (idx_username index). If it exists, delete the old data first and then insert it. If it does not exist, insert it directly:

REPLACE INTO user (username,sex,address) VALUES ('hanpang','boy','HongKong')

4.insert if not exists

That is, insert into ... select ... where not exist .... This method is suitable for inserting data fields that do not have a primary key or unique index. When inserting a piece of data, first determine whether the data exists in the MySQL database. If it does not exist, insert it normally. If it exists, ignore it:

INSERT INTO user (username,sex,address) 
SELECT 'hanpang','boy','HongKong' FROM user
WHERE NOT EXISTS (SELECT username FROM user WHERE username='hanpang')

5. Batch insert data

The above insert statements can use batch insert statements, and the table data structure is:

CREATE TABLE example (
    example_id INT NOT NULL,
    name VARCHAR( 50 ) NOT NULL,
    value VARCHAR( 50 ) NOT NULL,
    other_value VARCHAR( 50 ) NOT NULL
)

I am used to using this batch operation method to concatenate SQL statements, but when your string is too long (millions of data), you need to set instructions for MySQL:

INSERT INTO example
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4');

In actual development, we prefer to use batch addition operations through program code (using transaction submission and batch insertion into the database). The above method is more suitable for inserting test data or other low requirements, and the speed is indeed fast.

6. Batch Update

(1) replace into batch update (remember to have a primary key or index)

INSERT INTO example
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4');

(2) insert into ... on duplicate key update batch update

When using INSERT, there is a table T (id, A, B, C, D)

When inserting, we want to use A and B to index unique records, and update C and D when there are duplicates.

INSERT INTO T(A,B,C,D) VALUES (a,b,c,d) ON DUPLICATE KEY UPDATE C=C+1，D=d 

(3) Use the built-in statements of MySQL to build batch updates

UPDATE yoiurtable
	SET dingdan = CASE id 
		WHEN 1 THEN 3 
		WHEN 2 THEN 4
		WHEN 3 THEN 5 
		END
WHERE id IN (1,2,3)

(4) Create a temporary table, update the temporary table first, and then update from the temporary table

create temporary table tmp(id int(4) primary key,dr varchar(50));
insert into tmp values ​​(0,'gone'), (1,'xx'),...(m,'yy');
update test_tbl, tmp set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id;

Summarize

This is the end of this article about several types of insert and bulk statements in MySQL. For more relevant MySQL insert and bulk statement content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!