Summary: In this tutorial, we will learn how to use MySQL INSERT INTO statement to insert records into a MySQL table along with the examples.

Introduction to MySQL INSERT INTO statement

INSERT INTO statement is used to insert one or more records into a table in MySQL.

The general syntax of MySQL INSERT INTO statement is:

Note: Syntax within square brackets is optional.

where

  • table_name is the name of the table into which rows should be inserted,
  • column_name is the name of the column (multiple columns is often referred to as a column list),
  • and value_list is the comma-separated row values corresponding to the order of column_name (if specified).

In the above statement, providing a parenthesized list of comma-separated column names is optional but when specified then a value for each named column must be provided by the values list in the same order.

Some of the important points to remember at the time of specifying the column and its values in the INSERT statement are:

  • It is not mandatory to specify every column in the column list.
  • In non-strict SQL mode, unspecified columns are set to their default (explicit or implicit) values.
  • In strict SQL mode,  INSERT statement generates an error if unspecified columns don’t have an explicit default value.
  • We can use the DEFAULT keyword to set a column explicitly to its default value or  DEFAULT(col_name) to produce the default value for the column col_name.

MySQL INSERT INTO Examples

Let’s first create a new table.

Recommended: MySQL CREATE TABLE

Query OK, 0 rows affected (1.20 sec)

MySQL users table
users table

The ‘users‘ table with three columns (i.e. id, name, and gender) has been created successfully.

Now, let’s insert some data into the table using the INSERT statement.

Example 1: INSERT statement with empty column list and the values list.

ERROR 1064 (42000)

Although MySQL has the implicit default value for int and varchar type, it didn’t insert the default record because MySQL is working in strict mode.

In non-strict mode, the above statement will force MySQL to set default values to each column of the table.

Example 2: INSERT statement with explicit column and values list.

Query OK, 1 row affected (0.14 sec)

MySQL INSERT Statement Result 1
users table with 1 record

This is the recommended way of inserting data into a table in MySQL.

Specifying both column and value list eliminates the order ambiguity.

Example 3: INSERT Statement with only values list.

Query OK, 1 row affected (0.11 sec)

MySQL INSERT Statement Result 2
users table with 2 records

The order of the values in the value list should correspond to the column order of the table otherwise some ambiguity or error will occur.

To know the order of the columns use DESCRIBE table_name; statement.

Example 4: INSERT statement with different ordered columns and values list.

ERROR 1366 (HY000): Incorrect integer value: ‘pencil’ for column ‘id’ at row 1

The values should be in same order as column list.

Example 5: INSERT Statement with few columns and values.

Query OK, 1 row affected (0.12 sec)

MySQL INSERT Statement Result 3
users table with 3 records

gender column’s value was not specified, hence explicit default value was set i.e. Female.

The same is not applicable to id and name because they don’t have an explicit default value.

Example 6: INSERT Statement with DEFAULT value.

Query OK, 1 row affected (0.14 sec)

MySQL INSERT Statement Result 4
users table with 4 records

We can use the DEFAULT keyword to set gender explicitly to its default value.

DEFAULT enables us to avoid writing an incomplete VALUES list that does not include a value for each column in the table.

dev.mysql.com

In this tutorial, we learned to insert records or rows into a table in MySQL using the MySQL INSERT INTO statement.

Leave a Reply