MySQL database statements INSERT and REPLACE

The following article describes the update statements of the MySQL database in detail. We will introduce the INSERT and REPLACE statements to you today, we all know that the syntax of these two statements is very similar. The main difference between them is how to process repeated data. The SQL statements used to operate MySQL databases are generally divided into two types: Query statements,

The following article describes the update statements of the MySQL database in detail. We will introduce the INSERT and REPLACE statements to you today, we all know that the syntax of these two statements is very similar. The main difference between them is how to process repeated data. The SQL statements used to operate MySQL databases are generally divided into two types: Query statements,

The following article describes the update statements of the MySQL database in detail. We will introduce the INSERT and REPLACE statements to you today, we all know that the syntax of these two statements is very similar. The main difference between them is how to process repeated data.

The SQL statements used to operate MySQL databases are generally divided into two types: Query statements, SELECT statements, and update statements, which are also called data operation statements. The implication is to modify the data. There are three statements in standard SQL: INSERT, UPDATE, and DELETE.

There is another REPLACE statement in MySQL (the best combination with PHP). Therefore, this article uses MySQL (the best combination with PHP) background to discuss how to enable SQL update statements.

I. INSERT and REPLACE

Both INSERT and REPLACE statements INSERT new data into the table.

1. General INSERT usage

The INSERT statement in MySQL (the best combination with PHP) is not the same as the standard INSERT statement. In the standard SQL statement, the INSERT statement that inserts a record at a time has only one form.

 
 

  
  
Insert into tablename (column name ...) VALUES (column value );
 
 

There is another form in MySQL (the best combination with PHP.

 
 

  
  
INSERT INTO tablename SET column_name1 = value1, column_name2 = value2，…; 
 
 

The first method separates the column name from the column value. during use, the column name must be consistent with the number of column values. 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 the column names and column values to appear and use in pairs. The following statement will produce the same effect.

 
 

  
  
Insert into users SET id = 123, name = 'Yao Ming ', age = 25;
 
 

If the SET method is used, a value must be assigned to at least one column. If a field uses a missing value (such as default value or auto-increment value), you can omit these fields in either of the two methods. If auto-increment is used in the id field, the preceding 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 (the best combination with PHP) has also made some changes in VALUES. If VALUES does not write anything, the MySQL database (the best combination with PHP) will use the default VALUES of each column in the table to insert new records.

Insert into users () VALUES ();

If nothing is written after the table name, it indicates that all fields in the table are assigned a value. In this way, not only must the value in VALUES be consistent with the number of columns, but the order cannot be reversed. Insert into users VALUES (123, 'Yao ming', 25 );

If you write the INSERT statement as follows (the best combination of MySQL and PHP), an error is returned.

Insert into users VALUES ('Yao Ming ', 25 );

2. INSERT multiple records

If you see this title, you may ask, is there anything you can say about it? Won't you be able to INSERT multiple records after multiple INSERT statements are called! However, using this method increases the load on the server, because every SQL Server executes the same SQL Analysis and Optimization operations. Fortunately, MySQL (the best combination with PHP) provides another solution, that is, using an INSERT statement to INSERT multiple records. This is not a standard SQL syntax, so it can only be used in MySQL (the best combination with PHP.

Insert into users (name, age)
VALUES ('Yao Ming ', 25), ('bill Gates', 50), ('marker', 600 );

The preceding INSERT statement inserts three records into the users table consecutively. It is worth noting that the VALUES of each record must be placed in a pair (…) after VALUES in the preceding INSERT statement (...) "," Is used in the middle. Suppose there is a table table1

 
 

  
  
CREATE TABLE table1(n INT)； 
 
 

If you want to insert five records into Table 1, the following statement is incorrect:

 
 

  
  
INSERT INTO table1 (i) VALUES(1,2,3,4,5); 
 
 

MySQL (the best combination with PHP) will throw the following error

 
 

  
  
ERROR 1136: Column count doesn't match value count at row 1 
 
 

The correct statement is as follows:

 
 

  
  
INSERT INTO t able1(i) VALUES(1),(2),(3),(4),(5); 
 
 

Of course, this statement can also omit the column name, so that the number of values in each pair of brackets must be consistent, and this number must be consistent with the number of columns. For example:

 
 

  
  
INSERT INTO t able1 VALUES(1),(2),(3),(4),(5); 
 
 

3. REPLACE statement

We may often encounter this situation when using the MySQL database. If a table creates a unique index on a field and inserts a record into the table using an existing key value, a primary key conflict error is thrown. Of course, we may want to overwrite the original record value with the new record value. If you use the traditional method, you must first use the DELETE statement to DELETE the original record, and then use INSERT to INSERT a new record. MySQL (the best combination with PHP) provides us with a new solution, which is the REPLACE statement. When you use REPLACE to INSERT a record, if there is no duplicate record, REPLACE is the same as the INSERT function. If there is a duplicate record, REPLACE replaces the original record value with the value of the new record.

The biggest advantage of using REPLACE is that you can combine DELETE and INSERT into one to form an atomic operation. In this way, you do not have to consider complicated operations such as adding transactions when using both DELETE and INSERT.

When using REPLACE, the table must have a unique index, and the field where the index is located cannot allow null values. Otherwise, REPLACE is exactly the same as INSERT.

After REPLACE is executed, the system returns the affected number of rows. If 1 is returned, no duplicate records exist in the table. If 2 is returned, a duplicate record exists, the system automatically calls DELETE to DELETE this record, and then inserts this record with INSERT. If the returned value is greater than 2, multiple unique indexes exist, and multiple records are deleted and inserted.

The REPLACE syntax 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 SET id = 123, name = 'zhao Benshan ', age = 50;
 
 

As mentioned above, REPLACE may affect more than three records because there is more than one unique index in the table. In this case, REPLACE considers each unique index, deletes the duplicate record corresponding to each index, and inserts this new record. Assume that there is a table 1 with three fields a, B, and c. They all have a unique index.

Create table table1 (a int not null unique, B INT NOT NULL UNIQUE, c INT NOT NULL UNIQUE );

Assume that three records exist in table 1.

A B c
1 1 1
2 2 2
3 3 3

Next, we use the REPLACE statement to insert a record to table 1.

 
 

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

The returned result is as follows:

 
 

  
  
Query OK, 4 rows affected (0.00 sec) 
 
 

The record in table 1 is as follows:

 
 

  
  
a b c 
  
  
1 2 3 
 
 

As we can see, REPLACE deletes all the three original records and inserts (1, 2, 3.

The above content is an introduction to the update statements of the MySQL database. I hope you will gain some benefits.