MySQL INSERT INTO Statement

The CREATE TABLE statement enables you to create columns, while the INSERT statement enables you to insert rows or records into a table.The INSERT statement is an example of the Data Manipulation Language (DML). As the name implies, data manipulation language deals with data manipulation.

image

MySQL INSERT INTO Syntax

Insert a column into the table

1
2
3

INSERT INTO table_name 
VALUES
(value_for_column1, value_for_column2, value_for_column3,... value_for_columnN);

Insert multiple columns in a table

1
2
3
4
5

INSERT INTO table_name
VALUES
(value_for_column1, value_for_column2, value_for_column3,... value_for_columnN),
(value_for_column1, value_for_column2, value_for_column3,... value_for_columnN),
…;

Insert values in specific columns only

1

INSERT INTO table_name(column1, column3) VALUES (value_for_column1 , value_for_column3);

The column name needs to be mentioned only when inserting values in a specific column. If the INSERT statement contains the values of all columns in the table, it can be left out.

Data insertion based on conditional judgments

When writing programs, we often encounter some logic based on conditional judgments, such as: determine whether the data already exists in the database, and if not, insert it.

Tip 1: use ignore keyword

If the primary key primary or unique index unique to distinguish the uniqueness of the record, to avoid duplicate insertion of records can be used: insert ignore into

When inserting data, if there is an error, such as duplicate data, it will not return an error, but only a warning. So use ignore to ensure that the statement itself is not a problem, otherwise it will also be ignored.

Example.

1

INSERT IGNORE INTO books (name) VALUES ('MySQL Manual')

Tip 2: Use REPLACE into

REPLACE works much like INSERT, but if the old record has the same value as the new record, the old record is deleted before the new record is inserted, i.e., it tries to insert the new row into the table, removes the conflicting row containing the duplicate keyword value from the table when the insert fails because of a duplicate keyword error for the primary key or unique keyword, and tries again to insert the new row into the table.

The criterion for determining that an old row has the same value as a new row is that the table has a PRIMARY KEY or UNIQUE index; otherwise, it makes no sense to use a REPLACE statement. The statement would be the same as INSERT because no index is used to determine if the new row copies the other rows.

Syntax format.

1
2
3

REPLACE INTO `table_name`(`col_name`, ...) VALUES (...);
REPLACE INTO `table_name` (`col_name`, ...) SELECT ...;
REPLACE INTO `table_name` SET `col_name`='value'

Example.

1

REPLACE INTO books SELECT 1, 'MySQL Manual' FROM books

Tip 3: ON DUPLICATE KEY UPDATE

Specific syntax.

1

INSERT INTO table(field1, field2, fieldn) SELECT 'field1', 'field2', 'fieldn' FROM DUAL WHERE NOT EXISTS(SELECT field FROM table WHERE field = ?)

where DUAL is a temporary table that does not need to be physically created.

Example.

1

INSERT INTO books (name) VALUES ('MySQL Manual') ON duplicate KEY UPDATE id = id

Tip 4: INSERT INTO IF EXISTS

The insertion can be judged not only by primary and unique but also by other conditions based on the select condition.

Example.

1

INSERT INTO books (name) SELECT 'MySQL Manual' FROM dual WHERE NOT EXISTS (SELECT id FROM books WHERE id = 1)