MySQL INSERT: Syntax, Examples, and Common Errors

In MySQL, the INSERT statement is used to insert one or more rows into a table.

INSERT syntax

There are one or more rows in a INSERT statement which will be inserted into a table.

Here is the syntax of the INSERT statement used to insert one row:

INSERT INTO table_name (column_1, column_2, ...)
VALUES (value_1, value_2, ...);

Here is the syntax of the INSERT statement used to insert more rows:

INSERT INTO table_name (column_1, column_2, ...)
VALUES (value_11, value_12, ...),
       (value_21, value_22, ...)
       ...;

In the syntax:

• INSERT INTO and VALUES are keywords.
• table_name specifies the table name.
• (column_1, column_2, ...) specifies a list of column names.
• (value_11, value_12, ...) after the VALUES keyword specifies a list of values of one row.
• The INSERT statement returns the number of inserted rows.

This INSERT ... VALUES (...) syntax is different from a standalone VALUES ROW(...) table constructor, which returns a result table; see the MySQL VALUES statement guide. Error 3942 refers to an empty ROW() in that standalone form; see MySQL Error 3942: Empty ROW() in a VALUES Table Constructor.

INSERT examples

Let us use the CREATE TABLE statement to create a table named user as a demonstration:

CREATE TABLE user (
    id INT AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    age INT,
    birthday DATE,
    PRIMARY KEY (id)
);

There are four columns in the user table:

• The id column has a INT data type, and it is primary key column and has a auto increment value.
• The name column has a VARCHAR(255) data type, and it is NOT NULL.
• The age column has a INT data type.
• The birthday column has a DATE data type.

Insert one row

Let us use the following statment to insert one row into the user table:

INSERT INTO user (name, age)
VALUES ('Jim', 18);

Query OK, 1 row affected (0.00 sec)

Note: The output 1 row affected represents the row has been inserted into the user table.

We can also verify it by selectint rows from the table:

SELECT * FROM user;

+----+------+------+----------+
| id | name | age  | birthday |
+----+------+------+----------+
|  1 | Jim  |   18 | NULL     |
+----+------+------+----------+
1 row in set (0.00 sec)

Notice:

• The value of the id column is generated automatically as it is AUTO_INCREMENT a column.
• The birthday column values NULL, because we only inserted name and age columns.

Insert multiple rows

For detailed examples, column/value matching, and large-batch limits, see MySQL INSERT multiple rows.

Let us use the following statment to insert two rows into the user table:

INSERT INTO user (name, age)
VALUES ('Tim', 19), ('Lucy', 16);

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

Notice:

• The output 2 row affected representative of the two rows have been inserted into the user table.

We can also verify it by selectint rows from the table:

SELECT * FROM user;

+----+------+------+----------+
| id | name | age  | birthday |
+----+------+------+----------+
|  1 | Jim  |   18 | NULL     |
|  2 | Tim  |   19 | NULL     |
|  3 | Lucy |   16 | NULL     |
+----+------+------+----------+
3 rows in set (0.00 sec)

To insert rows returned by a query instead of literal values, see MySQL INSERT INTO SELECT.

Insert date column

To insert a date type column, you can use a text value with YYYY-MM-DD format. The following is a description of this date format:

• YYYY represents a four-digit year, for example 2020.
• MM represents a two-digit month, for example 01, 02 and 12.
• DD represents two-digit dates, for example 01, 02, 30, 31.

The following statement to insert a row into the user table with birthday column:

INSERT INTO user(name, age, birthday)
VALUES('Jack', 20, '2000-02-05');

Query OK, 1 row affected (0.00 sec)

Let us see the rows in the user table:

SELECT * FROM user;

+----+------+------+------------+
| id | name | age  | birthday   |
+----+------+------+------------+
|  1 | Jim  |   18 | NULL       |
|  2 | Tim  |   19 | NULL       |
|  3 | Lucy |   16 | NULL       |
|  4 | Jack |   20 | 2000-02-05 |
+----+------+------+------------+
4 rows in set (0.00 sec)

INSERT modifier

In MySQL, INSERT statements support 4 modifiers:

• LOW_PRIORITY : If you specify LOW_PRIORITY modifier, MySQL server will delay the execution of the INSERT operation until there are no clients who read on the table.

  LOW_PRIORITY modifier is supported by those storage engines which only has table-level locking, such as: MyISAM, MEMORY, and MERGE.

• HIGH_PRIORITY : If you specify HIGH_PRIORITY modifier, it will overwrite the server boot --low-priority-updates options.

  HIGH_PRIORITY modifier is supported by those storage engines which only has table-level locking, such as: MyISAM, MEMORY, and MERGE.

• IGNORE: INSERT IGNORE skips rows with ignorable errors, often duplicate keys, and reports warnings. It can also adjust invalid values, so inspect the warnings. See MySQL INSERT IGNORE for examples and cautions.

• DELAYED: Deprecated and ignored in MySQL 8.4; do not use it in new statements. See the MySQL 8.4 INSERT reference.

The usage of modifiers is as follows:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
INTO table_name
...

INSERT restrictions

The client and server each enforce a max_allowed_packet limit on messages. If an INSERT statement exceeds either limit, MySQL can return ER_NET_PACKET_TOO_LARGE and close the connection. See the MySQL Packet Too Large note for details.

The following statement shows max_allowed_packet on the current server:

SHOW VARIABLES LIKE 'max_allowed_packet';

+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 67108864 |
+--------------------+----------+
1 row in set (0.00 sec)

It is in bytes, and the value may be different on different servers.

For a CSV file, see Import CSV into MySQL.

Troubleshoot common INSERT errors

Summary

• INSERT adds one or more rows to a table.
• Use an explicit column list and provide the matching values; use defaults only when they fit the data model.
• For multi-row inserts, every VALUES tuple must have the same number of entries.
• Use INSERT IGNORE only when skipping ignorable row errors is intended, and inspect the warnings.