Correct MySQL insert into statement usage

The following articles mainly introduce the actual usage of MySQL insert into statements and related statements in MySQL insert into statements. MySQL insert into statements are frequently used in actual applications, therefore, it is better to know more about its related content.

 
 

  
  
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]  
  
  
[INTO] tbl_name [(col_name,...)]  
  
  
VALUES (expression,...),(...),... 
 
 

MySQLINSERT into select statement:

 
 

  
  
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]  
  
  
[INTO] tbl_name [(col_name,...)]  
  
  
SELECT ... 
 
 

Insert into statement:

 
 

  
  
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]  
  
  
[INTO] tbl_name  
  
  
SET col_name=expression, col_name=expression, ... 
 
 

INSERT inserts a new row into an existing table, INSERTINTO... VALUES statements INSERT rows based on specified VALUES, and MySQLINSERT into select statements INSERT rows selected from other tables with multiple VALUES... the VALUES format is supported in MySQL 3.22.5 or later versions, and the col_name = expression syntax is supported in MySQL 3.22.10 or later versions.

Tbl_name is the table in which rows should be inserted. The column name or SET clause indicates that the statement specifies the value for that column.

If you do not specify a list for INSERT... VALUES or INSERT... SELECT, the VALUES of all columns must be in the VALUES () table or provided by SELECT. If you do not know the order of the columns in the table, use DESCRIBE tbl_name to find the column.

Any column that does not explicitly give a value is set as its default value. For example, if you specify a list that does not name all columns in the table, unnamed columns are set as their default values. The default value is described in the 7.7 create table syntax.

An expression can reference any column previously set in a value table. For example, you can:

 
 

  
  
MySQL> INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2); 
 
 

But it cannot be like this:

 
 

  
  
MySQL> INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15); 
 
 

If you specify the keyword LOW_PRIORITY, INSERT execution is postponed until no other customers are reading the table. In this case, the user must wait until the insert statement is complete. If the table is frequently used, it may take a long time. This is the opposite of insert delayed to allow customers to continue immediately.

If you specify the keyword IGNORE in an INSERT with many value rows, any row in the table that copies an existing PRIMARY or UNIQUE key is ignored and not inserted. If you do not specify IGNORE, the insertion will be abandoned if any row that copies the existing key value. You can use the c api function MySQL_info () to check how many rows are inserted into the table.

If MySQL is configured with the DONT_USE_DEFAULT_FIELDS option, the INSERT statement produces an error unless you explicitly specify a value for all columns that require a non-NULL value. See 4.7.3 typical configure options.

The MySQLINSERT INTO... SELECT statement meets the following conditions:

A query cannot contain an order by clause.

The target table of the INSERT statement cannot appear in the FROM clause in the SELECT query section, because this is forbidden in ansi SQL to allow the SELECT statement FROM the table you are inserting. The problem is that SELECT may find records previously inserted during the same running period. When a sub-selection clause is used, the situation can be easily confused)

The AUTO_INCREMENT column works as usual.

If you use the INSERT... SELECT or INSERT... VALUES statement to have multiple value lists, you can use the c api function MySQL_info () to obtain the query information. The format of the information string is as follows:

Records: 100 Duplicates: 0 Warnings: 0

Duplicates indicates the number of rows that cannot be inserted because they are duplicate with the existing unique index value. Warnings indicates the number of times a column value is inserted in the event of some problems. Errors may occur under any of the following conditions:

Insert NULL to a column declared not null. The column is set as its default value.

Set the value that exceeds the column range to a numeric column, and the value is cut to an appropriate endpoint value within the range.

Set the number column to the value of '10. 34 A'. The trailing part of the spam is stripped and the number is still inserted. If the value is not a number at all, the column is set to 0.

Insert a string into a CHAR, VARCHAR, TEXT, or BLOB column that exceeds the maximum length of the column. The value is truncated to the maximum length of the column.

Inserts an invalid value of the column type into a date or time column. The column is set to the appropriate "zero" value of the column type.

The DELAYED option for the INSERT statement is exclusive to MySQL-it is useful if you cannot wait until the INSERT statement is completed. When you log on to MySQL for a log, and you periodically run the SELECT statement that takes a long time to complete, this is a common problem. DELAYED was introduced in MySQL 3.22.15, which is an extension of MySQL to ANSI SQL92.

When you use insert delayed, the customer will immediately prepare the table and INSERT it when it is not used by any other thread.

Another major benefit of using insert delayed is that inserts from many customers are bundled and written into one block. This is faster than doing a lot of separate inserts.

The above content is an introduction to the MySQL insert into statement. I hope you will find some gains.