MySQL INSERT statement parsing

1.INSERT into

The most commonly used simple INSERT statement, can have the following two kinds of usage

1> INSERT into Tb_user (ID, name, age) VALUES (100022, ' Tom ', 25); Assign a value to a specified column only

2> INSERT into Tb_user VALUES (100022, ' Tom ', 25); You must assign values to all columns

Note: The INTO keyword can be omitted

2. INSERT into ... SELECT

You can insert query results into a table

Insert into T2same (name,age) Select name, age from T2;

INSERT into T2same Select ID, name, age from T2;

Note:1> A query cannot contain an ORDER BY clause

The destination table of the 2> INSERT statement cannot appear in the FROM clause in the SELECT query section

3. INSERT IGNORE into

When data is inserted, such as when an error occurs (such as repeating data), no error is returned, only as a warning. Usually this is also the purpose of our use of ignore, to avoid inserting duplicate data.

However, when there is an SQL statement syntax error, it does not return an error, so use ignore make sure the statement itself is not a problem, otherwise it will be ignored.

Duplicate data is judged by the same primary key column or a unique index column.

Cases:

CREATE TABLETb_user (IDint(Ten) not NULL PRIMARY KEYauto_increment, nameVARCHAR( -) not NULL, Id_noVARCHAR( -) not NULL UNIQUE, Ageint(3))INSERT  intoTb_userVALUES(1,'Tom', -);
--the bottom two sentences will not insert data or errorINSERTIGNORE intoTb_userVALUES(1,'Lucy', -);INSERTIGNORE intoTb_userVALUES(2,'Tom', -);

4. REPLACE into

Represents inserting alternate data, the destination table has a primary Key, or a unique index, and if the data already exists in the table, it is replaced with the new data, and the effect is the same as insert into if there is no data.

REPLACE into and insert IGNORE into both have the opposite effect when the table already exists and you want to insert the same data.

5. On DUPLICATE KEY UPDATE

When primary or unique repeats, the UPDATE statement is executed, and the contents of the specified field are updated on the basis of the original record, and the contents of the other fields are retained. If the update is a useless statement, such as Id=id, then the same 3 function.

For example, to implement name duplication of data insertion without an error, use the following statement:

INSERT into Tb_user (name) VALUES (' Tom ') on DUPLICATE KEY UPDATE id = ID

MySQL INSERT statement parsing