This is the 26th day of my participation in the August More Text Challenge

One of the most common way is to set the primary key or unique index for field, when inserted into the duplicate data, throw an error, the program is terminated, this can cause trouble for subsequent processing, so need to make special treatment in the insert statement, as far as possible to avoid or ignore abnormal, let me make a brief introduction, interested friends can try:

Select id, username, sex, address as primary key and idx_username as unique index (idx_username).

1.insert ignore into

Namely when inserting data, if the data exist, it ignores the inserts, premise condition is inserted into the data fields set the primary key or unique index, test the SQL statement as follows, when inserting data, this article will first retrieve the MySQL database for data (i.e., idx_username index), if present, is to ignore the insert, if not, Data is normally inserted:

INSERT IGNORE INTO user (username,sex,address) VALUES ('hanpang'.'boy'.'HongKong')
Copy the code

2.on duplicate key update

MySQL > insert (idx_USERNAME); MySQL > insert (idx_USERNAME); MySQL > insert (idx_USERNAME); Update; if not, insert:

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

3.replace into

Is inserted into the data, if the data is deleted to insert again, premise condition as above, insert data fields need to set up the primary key or unique index, test the SQL statement as follows, when insert the record of this article, the MySQL database will first retrieve the existing data (idx_username index), if there is, first remove the old data, then to insert again, If not, insert directly:

REPLACE INTO user (username,sex,address) VALUES ('hanpang'.'boy'.'HongKong')
Copy the code

4.insert if not exists

The insert into… The select… where not exist … When inserting a data, check whether the data exists in the MySQL database first. If not, insert the data normally. If yes, ignore it:

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

5. Insert data in batches

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
)
Copy the code

Set global MAX_allowed_packet = 2*1024*1024*10 if your string is too long

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');
Copy the code

In actual development, we prefer to use bulk add operations through program code (using transaction commit, bulk insert database), using the above method is suitable for inserting test data or other low requirements, it is really fast.

6. Batch updates

(1) Replace into batch update (remember to have 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');
Copy the code

(2) Insert into… On Duplicate Key Update Batch updates

useINSERTWhen table T(id,A,B,C,D) is inserted, it is expected that the unique record through A,B index, update C,D when there is duplicationINSERT INTO T(A,B,C,D) VALUES (a,b,c,d) ON DUPLICATE KEY UPDATE C=C+1, D=d 
Copy the code

(3) use mysql’s own statements 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)
Copy the code

Create temporary table, first update temporary table, 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;
Copy the code