MySQL insert into statement

Insert into statement:

Insert [low_priority | delayed] [ignore]
[Into] tbl_name [(col_name,...)]
Values (expression ,...),(...),...

Insert 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... the statements in the form of values Insert rows based on the specified values, insert into Select Insert rows selected from other tables, there are multiple value tables insert... 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 insert 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.

Note that the currently queued rows are only stored in the memory until they are inserted into the table. This means that if you want to kill mysqld (kill-9) or if mysqld unexpectedly dies, any lines that are not written into the disk will be lost!

The following describes in detail what happens when you use the delayed option for insert or replace. In this description, the "Thread" is the thread that receives the insert delayed command and the "processor" is used to process all the insert delayed statements for a specific table.

When a thread executes a delayed statement on a table, if such a handler does not exist, a processor thread is created to process all the delayed statements on the table.
The thread checks whether the Handler has obtained a delayed lock; if not, it tells the handler to get the lock. The delayed lock can be obtained even if other threads have a read or write lock on the table. However, the handler will wait for all alter table locks or flush tables to ensure that the table structure is up-to-date.
The thread executes the insert statement, but does not write rows into the table. It puts copies of the last row into a queue managed by the processor thread. Any syntax error can be detected by the thread and reported to the client program.
The customer cannot report the number of repeat or auto_increment values of the result rows; it cannot obtain them from the server because the insert statement returns the result before the insert operation is complete. If you use c api, for the same reason, the mysql_info () function does not return anything meaningful.
When a row is inserted into a table, the update log is updated by a processor thread. When multiple rows are inserted, the update log is updated when the first row is inserted.
After each write to the delayed_insert_limit row, the processor checks whether any SELECT statement is still unfinished. If so, these statements can be executed before continuing.
When the processor does not have more rows in its queue, the table is unlocked. If the new insert delayed command is not received within seconds of delayed_insert_timeout, the processor is terminated.
If more than delayed_queue_size rows have not been resolved in a specific processor queue, the thread waits until the queue has space. This helps ensure that the mysqld server does not use all memory for the delayed memory queue.
The processor thread will display delayed_insert In the MySQL table in the Command column. If you execute a flush tables command or kill thread_id to kill it, it will be killed. However, before exiting, it will first store all queued rows in the table. During this period, this time it will not accept any new insert commands from other threads. If you execute insert delayed after it, a new processor thread will be created.
NOTE: If an insert delayed processor is running, the insert delayed command has a higher priority than normal insert! Other update commands must wait until the insert delay queue becomes empty, kill the processor thread (using kill thread_id), or execute flush tables.
The following status variables provide information about the insert delayed command: Number of delayed_insert_threads processor threads
Number of rows written by delayed_writes Using Insert delayed
Not_flushed_delayed_rows row number to be written

You can view these variables by issuing a show status statement or executing a mysqladmin extended-STATUS Command.

Note that if the table is not used, insert delayed is slower than normal insert. There is also an additional overhead on the server to process a separate thread for each table on which you use insert delayed. This means that you should use insert delayed only when you are sure to need it!