MySQL INSERT usages and examples

In this article, we will use MySQL INSERT statement to insert one or more rows into a table.

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.

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

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)

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 : If you specify IGNORE modifier, MySQL server will perform ignore those errors can be ignored during the INSERT operation. These errors return as WARNING.

  • DELAYED : This modifier has been deprecated in MySQL 5.6 and will be removed in the future. In MySQL 8.0, this modifier is available but will be ignored.

The usage of modifiers is as follows:

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

INSERT restrictions

In MySQL, the upper limit of the size of any single message on the server and client is configured by max_allowed_packet. When a SELECT size of the statement exceeds max_allowed_packet value, the server will give an error.

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.

Conclusion

In this article, you learned MySQL INSERT syntax and usages by examples. The following are the main points of the INSERT statement:

  • The INSERT statement is used to insert one or more rows into table.
  • If you want to ignore errors during inserting, you can use the IGNORE modifier.