MySQL Operations summary inserts and REPLACE_MYSQL

The implication is that the data is modified. There are 3 statements in standard SQL, which are insert, update, and delete. There is another replace statement in MySQL, so this article takes MySQL as the background to discuss how to make an UPDATE statement in SQL.
First, insert and replace
The INSERT and replace statements have the ability to insert new data into a table. The syntax for these two statements is similar. The main difference between them is how to handle duplicate data.
1. General usage of Inserts
Insert statements in MySQL are not the same as standard inserts, and in standard SQL statements there is only one form of INSERT statement that inserts one record at a time.
INSERT into tablename (column name ...) Values (column value);
And there's another form in MySQL.
INSERT into tablename SET column_name1 = value1, column_name2 = value2, ...;
The first method separates the column names from the column values, and when used, the column names must be the same as the number of column values. As the following statement inserts a record into the users table:
INSERT into the users (ID, name, age) VALUES (123, ' Yao ', 25);
The second method allows column names and column values to appear and be used in pairs, such as the following statement, which produces a medium-like effect.
INSERT into users SET ID = 123, name = ' Yao Ming ', age = 25;
If you use the Set method, you must assign at least one column. If a field uses a saved value (such as Default or self-increment), these fields can be omitted from either of these methods. The above two statements can be written in the following form, such as using the self-increment on the ID field:
INSERT into the users (name, age) VALUES (' Yao Ming ', 25);
INSERT into uses SET name = ' Yao Ming ', age = 25;
MySQL has also made some changes in values. If nothing is written in values, MySQL inserts the new record using the default values for each column in the table.
INSERT into Users () VALUES ();
If you write nothing after the table name, you assign a value to all the fields in the table. In this way, not only values are consistent with the number of columns, but the order cannot be reversed. INSERT into Users VALUES (123, ' Yao ', 25);
If you write the INSERT statement in the following form, MySQL will make an error.
INSERT into Users VALUES (' Yao Ming ', 25);
2. Insert multiple records using Insert
See this title maybe people will ask, this has nothing to say, call multiple INSERT statements can not be inserted multiple records it! But using this method increases the load on the server because every SQL Server performs the same analysis, optimization, and so on. Fortunately, MySQL offers another solution, which is to insert multiple records using an INSERT statement. This is not a standard SQL syntax, so it can only be used in MySQL.
INSERT into the users (name, age) VALUES (' Yao Ming ', 25), (' Bill Gates ', 50), (' Martians ', 600);
The INSERT statement above inserts 3 consecutive records into the users table. It is worth noting that the values in the INSERT statement above must have the value of each record put on a pair of (...) In, the middle uses "," split. Suppose there is a table table1
CREATE TABLE table1 (n INT);
If you want to insert 5 records into the Table1, the following is the wrong way to write:
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
And the right way to do that is:
INSERT into table1 (i) VALUES (1), (2), (3), (4), (5);
Of course, this type of writing can also omit the column name so that the number of values in each pair of parentheses must be the same, and that number must be consistent with the number of columns. Such as:
INSERT into table1 VALUES (1), (2), (3), (4), (5);
3. Replace statement
This may be the case when we use the database. If a table establishes a unique index on a field, when we insert a record into the table using the existing key value, it throws a primary key conflict error. Of course, we might want to overwrite the original record value with the value of the new record. If you use a traditional approach, you must first delete the original record by using the DELETE statement, and then insert the new record using insert. And in MySQL for us to provide a new solution, this is the replace statement. When you insert a record with replace, if you do not repeat, replace is the same as the Insert feature, and if there are duplicate records, replace replaces the original record value with the value of the new record.
The biggest advantage of using replace is that delete and insert can be combined to form an atomic operation. This eliminates the need to consider complex operations such as adding transactions while using both delete and insert.
When you use Replace, the table must have a unique index, and the field in which the index is located cannot allow null values, otherwise replace will be exactly the same as the insert.
After the replace is executed, the system returns the number of rows affected, and if you return 1, there is no duplicate record in the table, and if you return 2, there is a duplicate record, and the system automatically calls delete to delete the record, and then records the insertion with insert. If the returned value is greater than 2, then there are multiple unique indexes, and multiple records are deleted and inserted.
The syntax for replace is very similar to insert, as the following replace statement inserts or updates a record.
REPLACE into Users (id,name,age) VALUES (123, ' Zhao Benshan ', 50);
To insert more than one record:
REPLACE into the users (ID, name, age) VALUES (123, ' Zhao Benshan ', M), (134, ' Mary ', 15);
Replace can also use the SET statement
REPLACE into users SET ID = 123, name = ' Zhao Benshan ', age = 50;
The above mentioned that replace might affect more than 3 records because there are more than one unique index in the table. In this case, replace takes into account each unique index, deletes the duplicate records for each index, and inserts the new record. Suppose you have a Table1 table with 3 fields A, B, C. They all have a unique index.
CREATE TABLE table1 (a int not null UNIQUE,B int isn't null UNIQUE,C int not null UNIQUE);
Let's say there are 3 records in Table1.
A b C
1 1 1
2 2 2
3 1 Q
Below we use the Replace statement to insert a record into the Table1.
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 the Table1 are as follows
A b C
1 2 3
As we can see, replace deletes the original 3 records and then inserts (1, 2, 3)