mysql operations summarize INSERT and REPLACE

The implication is to modify the data. There are three statements in standard SQL: INSERT, UPDATE, and DELETE. There is one more REPLACE statement in MySQL, so this article discusses how to make the update statement in SQL available in the context of MySQL.
1. INSERT and REPLACE
Both the INSERT and REPLACE statements insert new data into the table. The syntax of these two statements is similar. The main difference is how they handle duplicate data.
1. General use of INSERT
The INSERT statement in MySQL is not quite the same as the standard INSERT statement. In the standard SQL statement, the INSERT statement that inserts one record at a time has only one form.
INSERT INTO tablename VALUES (column values);
There is another form in MySQL.
INSERT INTO SET column_name1 = value1, column_name2 = value2,... ;
The first method separates the column name from the column value. When used, the column name must correspond to the number 1 of the column value. The following statement inserts a record into the users table:
INSERT INTO users (id, name, age) VALUES (123, 'yao Ming', 25);
The second method allows column names and column values to appear and be used in pairs, and the following statement produces a middle sample effect.
INSERT INTO users SET id = 123, name = 'yao Ming', age = 25;
If you use SET, you must assign at least 1 column. If a field USES a truncated value (such as default or self-increment), both methods omit the fields. If self-increment is used on id field, the above two statements can be written as follows:
INSERT INTO users (name, age) VALUES (' yao Ming ', 25);
INSERT INTO uses SET name = 'yao Ming', age = 25;
MySQL also made some changes to VALUES. If nothing is written in VALUES, MySQL inserts a new record using the default value for each column in the table.
INSERT INTO users () VALUES();
If nothing is written after the table name, it represents an assignment to all the fields in the table. In this way, not only do the values in VALUES correspond to column number 1, but the order cannot be reversed. INSERT INTO users VALUES (123, 'yao Ming', 25);
An error is reported if the INSERT statement is written as MySQL.
INSERT INTO users VALUES (' yao Ming ', 25);
2. Insert multiple records using INSERT
If you look at this title, you might ask, what's the point? If you call INSERT multiple times, you can insert multiple records. However, using this method increases the load on the server, because every time the SQL server performs the same analysis, optimization, and other operations on SQL. Fortunately, MySQL offers another solution, which is to insert multiple records using one INSERT statement. This is not the standard SQL syntax, so it can only be used in MySQL.
INSERT INTO users (name, age) VALUES (' yao Ming ', 25), (' Bill Gates', 50), (' martians', 600);
The INSERT statement above inserts three consecutive records into the users table. It is important to note that after VALUES in the INSERT statement above, the value of each record must be placed in 1 pair (...). In the middle, use "," partition. Let's say I have a table, table1
CREATE TABLE table1 (n INT);
If you want to insert 5 records into table1, the following is written incorrectly:
INSERT INTO table1 (i) VALUES(1,2,3,4,5);
MySQL will throw the following error
ERROR 1136: Column count doesn t match value count at row 1
The correct way to write it is:
INSERT INTO table1 (i) VALUES (1), (2), (3), (4), (5);
Of course, you can also omit the column names in this way, so that the number of values in brackets has to be 1 to 1 for every 1 pair, and that number has to be 1 to the number of columns. Such as:
INSERT INTO table1 VALUES (1), (2), (3), (4), (5);
3. REPLACE statement
We may encounter this situation frequently when using a database. If a table has a unique index on a field, then when we insert another record into the table with an existing key value, we will throw a primary key conflict error. Of course, we might want to override the old record value with the new record value. If you use the traditional approach, you must first delete the original record using the DELETE statement and then insert the new record using INSERT. MySQL provides us with a new solution, the REPLACE statement. When inserting a record with REPLACE, REPLACE performs the same function as INSERT if it is not repeated. If there is a duplicate record, REPLACE replaces the original record with the value of the new record.
The biggest advantage of using REPLACE is that you can combine DELETE and INSERT to form an atomic operation. This eliminates the need to consider complex operations such as adding transactions while using both DELETE and INSERT.
When REPLACE is used, the table must have a unique index, and the field in which the index is located must not allow null values, otherwise REPLACE is exactly like INSERT.
After REPLACE is executed, the system returns the number of affected rows. If 1 is returned, there is no duplicate record in the table. If 2 is returned, there is one duplicate record. If the value returned is greater than 2, there are multiple 1-only indexes, and multiple records are deleted and inserted.
The syntax of REPLACE is very similar to INSERT. The following REPLACE statement inserts or updates a record.
REPLACE INTO users (id, name, age) VALUES (123, 'zhao benshan', 50);
Insert multiple records:
REPLACE INTO users (id, name, age) VALUES (123, 'zhao benshan', 50), (134, 'Mary', 15);
REPLACE can also use the SET statement
REPLACE INTO users id = 123, name = 'zhao benshan', age = 50;
It was mentioned above that REPLACE may affect more than three records because there are more than one unique index in the table. In this case, REPLACE will consider each unique index, delete the duplicate record corresponding to each index, and insert the new record. Suppose you have an table1 table with three fields a, b, c. They all have one unique index.
CREATE NULL table1(a INT NOT NULL UNIQUE,b INT UNIQUE
Suppose that there are already three records in table1
a b c
1 1 1
2 2 2
3 3 3
Next we insert a record into table1 using the REPLACE statement.
REPLACE INTO table1(a, b, c) VALUES(1,2,3);
The results returned are as follows
Query OK, 4 rows affected (0.00 sec)
The records in table1 are as follows
a b c
1 2 3
As you can see, REPLACE deleted all three of the original records and then inserted (1, 2, 3)