INSERT INTO – Insert Record Into MySQL Table

Summary: In this tutorial, we will learn how to use MySQL INSERT INTO statement to insert records into a MySQL table along with 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:

INSERT [INTO] table_name
    [(column_name [, column_name] ...)]
    VALUES (value_list_1)
        [, (value_list_2)
         ...
         , (value_list_n)
        ];

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

CREATE TABLE users (
id int PRIMARY KEY, 
name varchar(20),
gender varchar(20) DEFAULT 'Female'
);

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.

INSERT INTO () VALUES ();

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.

INSERT INTO users (id, name, gender) VALUES (1 , 'adarsh', 'Male');

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

Specifying both the column and value lists eliminates the ordering ambiguity.

Example 3: INSERT Statement with only values list.

INSERT INTO users VALUES (2 , 'kumar', 'Male');

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.

INSERT INTO users (id, name, gender) VALUES ('pencil', 3, 'Male');

The values should be in the same order as the column list.

Example 5: INSERT Statement with few columns and values.

INSERT INTO users (id, name) VALUES (3 , 'pencil');

Here, we didn’t specify the value for the gender column, so MySQL uses the default value i.e., Female.

The same is not applicable to id and name columns because we didn’t mention the default values for them while creating the table.

Example 6: INSERT Statement with DEFAULT value.

INSERT INTO users (id, name, gender) VALUES (4 , 'programmer', DEFAULT);

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.