MySQL Practical Experience of Using Insert Statement

1. Several syntaxes of Insert

1-1. Ordinary insert statement

INSERT INTO table (`a`, `b`, `c`, ...) VALUES ('a', 'b', 'c', ...);

I will not go into details here, just pay attention to the order. I do not recommend you to remove the content in the front brackets. Don’t ask why, as you may get scolded by your colleagues.

1-2. Insert or update

If we want to insert a new record (INSERT), but update the record if it already exists, we can use the "INSERT INTO ... ON DUPLICATE KEY UPDATE ..." statement:

Scenario example: This table stores the user's historical recharge amount. If it is the first recharge, a new record will be added. If the user has recharged before, the historical recharge amount will be accumulated. It is necessary to ensure that the data of a single user is not entered repeatedly.

At this time, you can use the "INSERT INTO ... ON DUPLICATE KEY UPDATE ..." statement.

Note: The "INSERT INTO ... ON DUPLICATE KEY UPDATE ..." statement determines uniqueness (existence) based on the unique index or primary key. As shown in the following SQL, you need to create a unique index (Unique) on the username field and set the transId to auto-increment.

-- User Chen Haha recharged 30 yuan to buy a membership INSERT INTO total_transaction (t_transId, username, total_amount, last_transTime, last_remark) 
 VALUES (null, 'chenhaha', 30, '2020-06-11 20:00:20', 'Add Membership') 
 ON DUPLICATE KEY UPDATE total_amount=total_amount + 30, last_transTime='2020-06-11 20:00:20', last_remark ='充会员';
 
-- User Chen Haha recharged 100 yuan to buy Blind Man's Supreme Fist skin INSERT INTO total_transaction (t_transId, username, total_amount, last_transTime, last_remark) 
 VALUES (null, 'chenhaha', 100, '2020-06-11 20:00:20', 'Purchase Lee Sin Supreme Fist skin') 
 ON DUPLICATE KEY UPDATE total_amount=total_amount + 100, last_transTime='2020-06-11 21:00:00', last_remark ='Purchase Lee Sin Supreme Fist Skin';

If the record with username='chenhaha' does not exist, the INSERT statement will insert a new record. Otherwise, the current record with username='chenhaha' will be updated, and the updated fields are specified by UPDATE.

By the way, ON DUPLICATE KEY UPDATE is a MySQL-specific syntax. For example, when migrating MySQL to Oracle or other DBs, similar statements must be changed to MERGE INTO syntax, which makes the compatibility issue so bad that one wants to curse. But there is no way, just like xlsx written in WPS cannot be opened by Office.

1-3. Insert or replace

If we want to insert a new record (INSERT), but if the record already exists, delete the original record first and then insert the new record.

Scenario example: This table stores the most recent transaction order information for each customer. It is required to ensure that the data of a single user is not entered repeatedly, and to have the highest execution efficiency, the least interaction with the database, and support high availability of the database.

At this time, you can use the "REPLACE INTO" statement, so you don't have to query first and then decide whether to delete and then insert.

The "REPLACE INTO" statement determines whether a uniqueness (existence) is based on a unique index or primary key.
The "REPLACE INTO" statement determines whether a uniqueness (existence) is based on a unique index or primary key.
The "REPLACE INTO" statement determines whether a uniqueness (existence) is based on a unique index or primary key.

Note: As shown in the following SQL, you need to create a unique index (Unique) on the username field and set the transId to auto-increment.

-- 20 points recharge REPLACE INTO last_transaction (transId,username,amount,trans_time,remark) 
 VALUES (null, 'chenhaha', 30, '2020-06-11 20:00:20', 'Member Recharge');
 
-- Buy skin at 21 o'clock REPLACE INTO last_transaction (transId, username, amount, trans_time, remark) 
 VALUES (null, 'chenhaha', 100, '2020-06-11 21:00:00', 'Purchase Lee Sin Supreme Fist skin');

If the record with username='chenhaha' does not exist, the REPLACE statement will insert a new record (first recharge), otherwise, the current record with username='chenhaha' will be deleted and then the new record will be inserted.

Do not give a specific value for id, otherwise it will affect SQL execution, unless there are special business requirements.

Tips:
ON DUPLICATE KEY UPDATE: If the inserted row has a duplicate unique index or primary key, the old update is executed; if it does not cause a duplicate unique index or primary key, a new row is added directly.
REPLACE INTO: If the inserted row has a duplicate unique index or primary key, delete the old record and enter the new record; if it does not cause a duplicate unique index or primary key, add a new row directly.

replace into vs. insert on duplicate udpate:

1. When there is no duplicate primary key or unique index, replace into is the same as insert on duplicate udpate.

2. When the primary key or unique index is repeated, replace deletes the old record and enters the new record, so all the original records will be cleared. At this time, if the fields of the replace statement are incomplete, some of the original values ​​​​such as the c field will be automatically filled with the default value (such as Null).

3. Careful friends will find that insert on duplicate udpate only affects one row, while REPLACE INTO may affect multiple rows. Why? Written in the last section of the article~

1-4. Insert or ignore

If we want to insert a new record (INSERT), but if the record already exists, we want to ignore it and do nothing. In this case, we can use the INSERT IGNORE INTO ... statement: There are many scenarios, so I will not go into details with examples.

Note: As above, the "INSERT IGNORE INTO …" statement determines uniqueness (whether it exists) based on a unique index or primary key. You need to create a unique index (Unique) on the username field and set transId to auto-increment.

-- User added for the first time INSERT IGNORE INTO users_info (id, username, sex, age ,balance, create_time) 
 VALUES (null, 'chenhaha', 'Male', 26, 0, '2020-06-11 20:00:20');
 
-- Second addition, directly ignore INSERT IGNORE INTO users_info (id, username, sex, age ,balance, create_time) 
 VALUES (null, 'chenhaha', 'Male', 26, 0, '2020-06-11 21:00:20');

2. Inserting large amounts of data

2-1. Three treatment methods

2-1-1. Single loop insertion

We took 100,000 pieces of data and conducted some tests. If the insertion method is to insert one by one in a program traversal loop. The speed of inserting a row on MySQL is between 0.01s and 0.03s.

The average speed of inserting one by one is 0.02*100000, which is about 33 minutes.

The following code is a test example:

1. Time test of inserting 100,000 records in a normal loop

 @Test
 public void insertUsers1() {
  
  User user = new User();
  
  user.setUserName("Captain Teemo");
  user.setPassword("dying");
  user.setPrice(3150);
  user.setHobby("Grow mushrooms");
  
  for (int i = 0; i < 100000; i++) {
   user.setUserName("Captain Teemo" + i);
   //Call the insert method userMapper.insertUser(user);
  }
 }

The execution speed is 30 minutes, which is 0.018*100000. It can be said to be very slow.

It was found that the cost of optimizing by inserting one by one was too high. Then go to query optimization method. It was found that the batch insertion method can significantly improve the speed.

Increase the insertion speed of 100,000 data records to about 1-2 minutes↓

2-1-2. Modify SQL statements for batch insertion

insert into user_info (user_id, username, password, price, hobby)
 values ​​（null,'Captain Teemo 1','123456',3150,'Growing mushrooms'）,(null,'Garen','123456',450,'Stepping on mushrooms');

Use batch insert to insert 100,000 records. The test code is as follows:

 @Test
 public void insertUsers2() {
   
  List<User> list= new ArrayList<User>();
		
  User user = new User();
  user.setPassword("dying");
  user.setPrice(3150);
  user.setHobby("Grow mushrooms");
		
  for (int i = 0; i < 100000; i++) {
   user.setUserName("Captain Teemo" + i);
   // Put a single object into the parameter list list.add(user);
   
  }
  userMapper.insertListUser(list);
 }

Batch insert takes 0.046s, which is equivalent to the speed of inserting one or two pieces of data. Therefore, using batch insert will greatly improve the data insertion speed. When there is a large amount of data to insert, use batch insert optimization.

How to write batch insert:

DAO definition layer method:

Integer insertListUser(List<User> user);

SQL writing in mybatis Mapper:

<insert id="insertListUser" parameterType="java.util.List">
  INSERT INTO `db`.`user_info`
   ( `id`,
    `username`,
    `password`,
    `price`,
    `hobby`) 
   values
  <foreach collection="list" item="item" separator="," index="index">
   (null,
   #{item.userName},
   #{item.password},
   #{item.price},
   #{item.hobby})
  </foreach>
 
 </insert>

This will allow batch insert operations:

Note: But when the amount of batch operation data is large. For example, if I insert 100,000 records into a SQL statement and the data packet to be operated exceeds 1M, MySQL will report the following error:

Error message:

Mysql You can change this value on the server by setting the max_allowed_packet' variable. Packet for query is too large (6832997 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.

explain:

The packet used for the query was too large (6832997 > 1048576). You can change this value on your server by setting the max_allowed_packet variable.

From the explanation you can see that the packet used for the operation is too large. The SQL content data size to be inserted here is 6M, so an error is reported.

Solution:

The database is MySQL57. I checked the information and found that it is a system parameter problem of MySQL:

max_allowed_packet, whose default value is 1048576 (1M),

Query:

show VARIABLES like '%max_allowed_packet%';

Modify the value of this variable: in the [mysqld] section of the my.ini (Windows) or /etc/mysql.cnf (Linux) file in the MySQL installation directory

max_allowed_packet = 1M, if changed to 20M (or larger, if there is no such line, add this line), as shown below

Save and restart the MySQL service. Now you can execute SQL statements with a size greater than 1M and less than 20M.

But what if 20M is not enough?

2-1-3. Insert in batches and multiple cycles

If it is inconvenient to modify the database configuration or there is too much content to be inserted, you can also control it through the backend code. For example, to insert 100,000 pieces of data, you can insert 1,000 pieces each time in 100 batches, which only takes a few seconds. Of course, if the content of each piece is very large, that is another matter. .

2-2. Other ways to optimize slow insertion speed

A. Use the show processlist command to check whether there are other long processes or a large number of short processes occupying thread pool resources? See if you can reduce the pressure on the primary database by allocating some processes to the backup database; or kill some useless processes first? (manually scratching head o_O)

B. To import data in large quantities, you can also close the index first and then open the index after the data is imported.

Disable: ALTER TABLE user_info DISABLE KEYS;
Enable: ALTER TABLE user_info ENABLE KEYS;

3. The pitfalls of REPLACE INTO syntax

It was mentioned above that REPLACE may affect more than 3 records, because there is more than one unique index in the table. In this case, REPLACE will consider each unique index, delete the duplicate records corresponding to each index, and then insert the new record. Suppose there is a table1 with three fields a, b, c. What if they both have a unique index? Let’s test it with some data earlier.

-- Test table creation, fields a, b, c all have unique indexes CREATE TABLE table1(a INT NOT NULL UNIQUE, b INT NOT NULL UNIQUE, c INT NOT NULL UNIQUE);
--Insert three test data INSERT into table1 VALUES(1,1,1);
INSERT into table1 VALUES(2,2,2);
INSERT into table1 VALUES(3,3,3);

At this point, table1 already has 3 records, and fields a, b, and c are all unique indexes.

mysql> select * from table1;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
+---+---+---+
3 rows in set (0.00 sec)

Next we use the REPLACE statement to insert a record into table1.

REPLACE INTO table1(a, b, c) VALUES(1,2,3);

mysql> REPLACE INTO table1(a, b, c) VALUES(1,2,3);
Query OK, 4 rows affected (0.04 sec)

At this time, query the records in table1 as follows, only one piece of data is left~

mysql> select * from table1;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+
1 row in set (0.00 sec)

(Boss: After inserting the first 10w data, and inserting 5w data, there are still 8w data left?? Did you feed our data to the dog?!)

REPLACE INTO syntax review: If the inserted row has a duplicate unique index or primary key, delete the old record and enter the new record; if it does not cause a duplicate unique index or primary key, add a new row directly.

We can see that when using REPLACE INTO, each unique index will be affected, which may cause accidental deletion of data. Therefore, it is recommended not to use REPLACE INTO in a table with multiple unique indexes.

Summarize

This is the end of this article about the experience of using the Insert statement in MySQL practice. For more relevant content about the experience of using the MySQL Insert statement, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!