Shulou(Shulou.com)05/31 Report--

mysql insert how to use, I believe that many people without experience at a loss about this, this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

insert syntax

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,...)]

VALUES ({expr | DEFAULT},...), (...),...

[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

Or:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name

SET col_name={expr | DEFAULT}, ...

[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

Or:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,...)]

SELECT ...

[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

If both the column list and VALUES list are empty lists, INSERT creates a row with each column set to default:

INSERT INTO tbl_name () VALUES();

Assuming that the worker table has only name and email, insert a piece of data:

insert into worker values("tom","tom@yahoo.com");

Insert multiple pieces of data in batches:

insert into worker values('tom','tom@yahoo.com'),('paul','paul@yahoo.com');

Given the column to assign, then list the interpolated data for the values:

insert into worker (name) values ('tom');

insert into worker (name) values ('tom'), ('paul');

Insert data using set:

insert into worker set name='tom';

Unnamed rows in the SET clause are assigned a default value, and multiple rows cannot be inserted using this form of INSERT statement.

An expression can refer to any column previously set in a table of values, for example

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

- But not like this:

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

Insert rows selected from other tables using the INSERT…SELECT statement.

insert into tbl_name1(col1,col2) select col3,col4 from tbl_name2;

--If each column has data:

insert into tbl_name1 select col3,col4 from tbl_name2;

A query cannot contain an ORDER BY clause, and the destination table of an INSERT statement cannot appear in the FROM clause of the SELECT query section.

ON DUPLICATE KEY UPDATE

If you specify ON DUPLICATE KEY UPDATE, and inserting rows results in duplicate values in a UNIQUE index or PRIMARY KEY, perform the old row UPDATE.

--Suppose a,b are unique indexes, table table has no rows such as 1, 2 is normal insertion data, conflict, update the value of column c.

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=3;

- Or is it?

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=values(c);

--Update conflicting rows by referencing other columns

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

Inserts NULL into a column that has been defined as NOT NULL. For a multiline INSERT statement or INSERT INTO... SELECT statement, columns are set to implicit defaults based on the type of column data. The default value is 0 for numeric types, an empty string ('') for string types, and a value of 'zero' for date and time types.

INSERT INTO... SELECT ON DUPLICATE KEY UPDATE

insert into tbl_name1(a,b,c)

select col1,col2,col3 from tbl_name2

ON DUPLICATE KEY UPDATE c=values(c);

INSERT DELAYED

This option is useful if your client cannot wait for INSERT to complete, and when a client uses INSERT DELAYED, it immediately gets a confirmation from the server. And rows are queued and inserted when the table is not in use by other threads.

Another important benefit of using INSERT DELAYED is that inserts from many clients are grouped together and coded into a block. This is much faster than performing many independent insertions.

INSERT DELAYED INTO worker (name) values ('tom'), ('paul');

There are some limitations to using DELAYED:

INSERT DELAYED applies only to MyISAM, MEMORY, and ARCHIVE tables. For MyISAM tables, both SELECT and INSERT statements are supported if there are no free blocks in the middle of the data file. In these cases, it is not necessary to use INSERT DELAYED on MyISAM.

INSERT DELAYED should only be used for INSERT statements that specify a list of values. The server ignores the command for INSERT DELAYED... DELAYED and INSERT DELAYED of SELECT statements... DELAYED ON DUPLICATE UPDATE statement.

Because the statement returns immediately before the row is inserted, you cannot use LAST_INSERT_ID() to get the AUTO_INCREMENT value. AUTO_INCREMENT values may be generated by statements.

For SELECT statements, the DELAYED rows are invisible until they are actually inserted.

DELAYED is ignored on the slave replication server because DELAYED does not produce data on the slave server that is different from the data on the master server.

After reading the above content, do you know how to use insert in mysql? If you still want to learn more skills or want to know more related content, welcome to pay attention to the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.