Correct use of Mysql INSERT into statement _mysql

The following article mainly introduces the actual usage of the MySQL INSERT INTO statement and the introduction of the relevant statements in the MySQL INSERT INTO statement, the MySQL INSERT INTO statement is often used in the actual application of the statement, So the relevant content or a lot of mastery as well.

INSERT [Low_priority | Delayed] [IGNORE] 
[into] tbl_name [(Col_name,...)] 

Mysqlinsert into SELECT statement:

INSERT [Low_priority | Delayed] [IGNORE] 
[into] tbl_name [(Col_name,...)] 

INSERT into statement:

INSERT [Low_priority | Delayed] [IGNORE] 
[into] tbl_name 

Insert inserts a new row into an existing table, Insertinto ... The statements in the form of values insert rows based on the explicitly specified value, mysqlinsert into Select to insert rows selected from other tables, insert INTO ... The form of values is supported in MySQL 3.22.5 or later versions, col_name=expression syntax is supported in MySQL 3.22.10 or later versions.

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

If you are an insert ... Values or INSERT ... Select does not specify a list, and the values of all columns must be provided in the values () table or 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 does not explicitly give 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, the unnamed columns are set to their default values. The default value assignment is described in the 7.7 CREATE table syntax.

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

But not like this:

Mysql> INSERT into Tbl_name (col1,col2) VALUES (col2*2,15); 

If you specify a keyword Low_priority,insert execution is deferred until no other client is reading the table. In this case, the customer must wait for the INSERT statement to complete, and if the table is used frequently, it can take a long time. This is the exact opposite of the insert delayed that keeps the guest going right away.

If you specify a keyword ignore in an insert with many values, any rows in the table that replicate an existing primary or unique key are ignored and are not inserted. If you do not specify ignore, insert if any of the rows that replicate 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 explicitly specify a value for all columns that require a non-null value. See the 4.7.3 Typical Configure option.

Mysqlinsert into ... The SELECT statement satisfies the following criteria:

A query cannot contain an ORDER BY clause.

The destination table for the INSERT statement does not appear in the FROM clause in the SELECT query section because it is prohibited in ANSI SQL from having a select from the table you are inserting. (The problem is that a select might find records that were previously inserted during the same run period.) When you use a sub-selection clause, the situation can easily be confused.

The Auto_increment column worked as usual.

If you use Insert ... Select or Insert ... The values statement has multiple value lists, and you can use the C API function Mysql_info () to get the query information. The information string is formatted as follows:

records:100 duplicates:0 warnings:0

Duplicates indicates the number of rows that cannot be inserted because they are duplicated with an existing unique index value. Warnings indicates the number of attempts to insert a column value when some problem occurs. An error can occur under any of the following conditions:

Inserts NULL to a column that is declared not NULL, and the column is set to its default value.

Sets the value beyond the column range to a number column, and the value is clipped to the appropriate endpoint value in the range.

Set the number column to a value such as ' 10.34 a ', the trailing garbage is stripped and still the digital part is inserted. If the value is not a number at all, the column is set to 0.

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

The delayed option for the INSERT statement is a MySQL-exclusive option-it is useful if your customer has not been able to wait for the insert to complete. This is a common problem when you log in to use MySQL for journaling, and you periodically run a SELECT statement that takes a long time to complete. Delayed is introduced in the face MySQL 3.22.15, which is an extension of MySQL to ANSI SQL92.

When you use Insert delayed, the customer will be ready immediately, and when the table is not being used by any other thread, the row is inserted.

Another major benefit of using insert delayed is that many customer inserts are bundled and written into a block. It's faster than doing a lot of separate inserts.

The relevant content is the introduction of MySQL INSERT into the statement, I hope to help you learn.