Shulou(Shulou.com)06/03 Report--

This article mainly introduces how to use insertion and batch statements in MySQL, which has certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article. Let Xiaobian take you to understand it together.

preface

The most common way is to set the primary key or unique index for the field, when inserting duplicate data, throw an error, the program terminates, but this will bring trouble to subsequent processing, so you need to do special processing on the insertion statement, try to avoid or ignore exceptions, the following I briefly introduce, interested friends can try:

Here, for the convenience of demonstration, I created a new user test table, which mainly has four fields: id, username, sex, and address. The primary key is id (self-increasing), and the unique index (idx_username) is set for the username field.

1.insert ignore into

That is, when inserting data, if the data exists, ignore the insertion, 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, MySQL database will first retrieve the existing data (i.e. idx_username index), if it exists, ignore the insertion, if it does not exist, insert the data 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, update the operation. The preconditions are the same as above, and the inserted data field is set with a primary key or unique index. The test SQL statement is as follows. When inserting this record, MySQL database will first retrieve the existing data (idx_username index). If it exists, update the operation. If it does not exist, insert it directly:

INSERT IGNORE INTO user (username,sex,address) VALUES ('hanpang','boy','HongKong')on duplicate key updateSEX='boy',address='HongKong'3.replace into

That is, when inserting data, if the data exists, delete it and then insert it. The preconditions are 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, 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

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

INSERT INTO user (username,sex,address) SELECT 'hanpang','boy','HongKong' FROM userWHERE NOT EXISTS (SELECT username FROM user WHERE username='hanpang')5. Bulk Insert Data

The above insert statement is a table data structure that can be used with batch insert statements:

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

Personal habits use this batch operation mode, SQL statement concatenation, but when your string is too long (millions of data), you need to set the command to mysql:

INSERT INTO exampleVALUES(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 commit, batch insertion into the database), using the above method to insert test data or other low requirements is more appropriate, the speed is really fast.

6. Batch update

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

INSERT INTO exampleVALUES(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

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

When inserting, you want to index unique records through A,B, and update C,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) Build batch updates using mysql's own statements

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

(4) Create temporary table, update temporary table first, and then update from 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; Thank you for reading this article carefully, I hope Xiaobian shared "How to use insertion and batch statements in MySQL" This article is helpful to everyone, but also hope that everyone supports more, pay attention to the industry information channel, more relevant knowledge waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.