Use the MySQL INSERT INTO statements correctly

The following article mainly introduces the actual usage of MySQL INSERT INTO statement and the related statement of MySQL INSERT INTO statement. MySQL INSERT INTO statement is frequently used in the actual application, so it is better to learn more about the 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... Statement in VALUES form inserts rows based on explicitly specified values, MySQLINSERT INTO SELECT form inserts rows selected from other tables, INSERT INTO with multiple value tables... The VALUES form is supported in MySQL 3.22.5 or later, and the col_name=expression syntax is supported in MySQL 3.22.10 or later.

tbl_name is the table where the rows should be inserted. The column list or SET clause indicates that the statement specifies a value for that column.

If you work for INSERT... VALUES or INSERT... SELECT does not specify a list and all columns must have values in the VALUES() table or supplied by SELECT. If you don't know the order of the columns in the table, use DESCRIBE tbl_name to find out.

Any column that is not explicitly given a value is set to its default value. For example, if you specify a list and do not name all the columns in the table, unnamed columns are set to their default value. The default value assignment is described in 7.7ES58en TABLE syntax.

An expression can refer to any column previously set in a value table. For example, you can do this:

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

But not this:

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

If you specify the keyword LOW_PRIORITY, the execution of INSERT is delayed until no other customer is reading the table. In this case, the customer has to wait until the insert statement is complete, which can take a long time if the table is used frequently. This is the opposite of INSERT DELAYED asking customers to continue immediately.

If you specify the keyword IGNORE in an INSERT row with many values, any row in the table that replicates an existing PRIMARY or UNIQUE key is ignored and not inserted. If you do not specify IGNORE, insert if any rows that copy existing key values are discarded. 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 generates an error unless you specifically specify a value for all columns that require a non-NULL value. See 4.7.3 For typical configure options.

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

The query cannot contain 1 ORDER BY clause.

The destination table of the INSERT statement cannot appear in the FROM clause of the SELECT query because it is prohibited in ANSI SQL to remove SELECT from the table you are inserting. (The problem is that SELECT will likely find records that were previously inserted during the same run period. The situation can be very confusing when using subselection clauses)

The AUTO_INCREMENT column works as usual.

If you use INSERT... SELECT or INSERT... The VALUES statement has multiple lists of values. You can use the C API function MySQL_info() to get 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 duplicate the existing index value of only 1. Warnings indicates the number of times a column value was attempted when something went wrong. Errors can occur under any of the following conditions:

Insert NULL into the declared NOT NULL column, which is set to its default value.

Sets the out-of-range value to a numeric column, and the value is clipped to the appropriate endpoint value within the range.

Set the numeric column to a value such as '10.34 a', and the trailing garbage is stripped off and the numeric portion is inserted again. If the value is not a number at all, the column is set to 0.

Inserts a string into an 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 a value that is not valid for a column type into a date or time column. The column is set to the appropriate "zero" value for the column type.

The DELAYED option for INSERT statements is MySQL exclusive - useful if your client can't wait for INSERT to complete. This is a common problem when you use MySQL for diary logins, and you also periodically run SELECT statements that take a long time to complete. DELAYED is introduced in plane MySQL 3.22.15, which is an extension of MySQL to ANSI SQL92.

When you use INSERT DELAYED, the client is ready immediately and rows are inserted when the table is not being used by any other thread.

The other main benefit of using INSERT DELAYED is that many customer inserts are bundled in 1 and written into 1 block. This is faster than doing a lot of separate inserts.

The above is the introduction of MySQL INSERT INTO statement. I hope it will be helpful to your study.