Summary: in this tutorial, you will learn how to use the MySQL INSERT
statement to insert one or more rows into a table.
Introduction to the MySQL INSERT statement
The INSERT
statement allows you to insert one or more rows into a table. The following illustrates the syntax of the INSERT
statement:
INSERT INTO table_name(column1, column2,...)
VALUES (value1, value2,...);
In this syntax,
- First, specify the table name and a list of comma-separated columns inside parentheses after the
INSERT INTO
clause. - Then, put a comma-separated list of values of the corresponding columns inside the parentheses following the
VALUES
keyword.
When using the INSERT
statement, you need to ensure that the number of columns matches the number of values.
Additionally, you need to specify that the positions of columns correspond precisely to the positions of their corresponding values.
To insert multiple rows into a table using a single INSERT
statement, you use the following syntax:
INSERT INTO table(column1, column2,...)
VALUES
(value1, value2,...),
(value1, value2,...),
...
(value1, value2,...);
Code language: SQL (Structured Query Language) (sql)
In this syntax, rows are separated by commas in the VALUES
clause.
MySQL INSERT statement examples
Let’s create a new table called tasks
for practicing the INSERT
statement:
DROP TABLE IF EXISTS tasks;
CREATE TABLE tasks (
task_id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
start_date DATE,
due_date DATE,
priority TINYINT NOT NULL DEFAULT 3,
description TEXT
);
Code language: SQL (Structured Query Language) (sql)
1) Basic MySQL INSERT statement example
The following statement inserts a new row into the tasks
table:
INSERT INTO tasks(title, priority)
VALUES('Learn MySQL INSERT Statement', 1);
Code language: SQL (Structured Query Language) (sql)
Output:
1 row(s) affected
Code language: SQL (Structured Query Language) (sql)
The output indicates that the statement inserted one row into the tasks
table successfully.
In this example, we specified values for the title
and priority
columns only. For other columns, MySQL uses the default values.
The task_id
column is an AUTO_INCREMENT
column, meaning that MySQL generates a sequential integer whenever a row is inserted into the table.
The start_date
, due_date
, and description
columns use NULL
as the default value. Therefore, MySQL insert NULL
into these columns if you don’t specify their values in the INSERT
statement.
The following retrieves data from the tasks
table:
SELECT * FROM tasks;
Code language: SQL (Structured Query Language) (sql)
Output:
+---------+------------------------------+------------+----------+----------+-------------+
| task_id | title | start_date | due_date | priority | description |
+---------+------------------------------+------------+----------+----------+-------------+
| 1 | Learn MySQL INSERT Statement | NULL | NULL | 1 | NULL |
+---------+------------------------------+------------+----------+----------+-------------+
1 row in set (0.00 sec)
Code language: PHP (php)
2) Inserting rows using default value example
If you want to insert a default value into a column, you have two ways:
- Ignore both the column name and value in the
INSERT
statement. - Specify the column name in the
INSERT INTO
clause and use theDEFAULT
keyword in theVALUES
clause.
The following example demonstrates the second way:
INSERT INTO tasks(title, priority)
VALUES('Understanding DEFAULT keyword', DEFAULT);
Code language: SQL (Structured Query Language) (sql)
In this example, we specified the value for title
and priority
columns.
Because the default value for the column priority
is 3 as declared in the table definition, the statement inserts number 3 into the priority
column.
priority TINYINT NOT NULL DEFAULT 3
Code language: SQL (Structured Query Language) (sql)
The following statement returns the contents of the tasks
table after the insert:
SELECT * FROM tasks;
Code language: SQL (Structured Query Language) (sql)
Output:
+---------+-------------------------------+------------+----------+----------+-------------+
| task_id | title | start_date | due_date | priority | description |
+---------+-------------------------------+------------+----------+----------+-------------+
| 1 | Learn MySQL INSERT Statement | NULL | NULL | 1 | NULL |
| 2 | Understanding DEFAULT keyword | NULL | NULL | 3 | NULL |
+---------+-------------------------------+------------+----------+----------+-------------+
2 rows in set (0.00 sec)
Code language: PHP (php)
3) Inserting dates into the table example
To insert a literal date value into a column, you use the following format:
'YYYY-MM-DD'
Code language: SQL (Structured Query Language) (sql)
In this format:
YYYY
represents a four-digit year e.g., 2018.MM
represents a two-digit month e.g., 01, 02, and 12.DD
represents a two-digit day e.g., 01, 02, 30.
The following statement inserts a new row to the tasks
table with the start and due date values:
INSERT INTO tasks(title, start_date, due_date)
VALUES ('Insert date into table', '2018-01-09', '2018-09-15');
Code language: SQL (Structured Query Language) (sql)
Output:
+---------+-------------------------------+------------+------------+----------+-------------+
| task_id | title | start_date | due_date | priority | description |
+---------+-------------------------------+------------+------------+----------+-------------+
| 1 | Learn MySQL INSERT Statement | NULL | NULL | 1 | NULL |
| 2 | Understanding DEFAULT keyword | NULL | NULL | 3 | NULL |
| 3 | Insert date into table | 2018-01-09 | 2018-09-15 | 3 | NULL |
+---------+-------------------------------+------------+------------+----------+-------------+
3 rows in set (0.00 sec)
Code language: PHP (php)
It is possible to use expressions in the VALUES
clause. For example, the following statement adds a new task using the current date for the start date and due date columns:
INSERT INTO tasks(title, start_date, due_date)
VALUES
(
'Use current date for the task',
CURRENT_DATE(),
CURRENT_DATE()
);
Code language: SQL (Structured Query Language) (sql)
In this example, we used the CURRENT_DATE()
function as the values for the start_date
and due_date
columns. Note that the CURRENT_DATE()
function is a date function that returns the current system date.
Here are the contents of the tasks
table after insert:
+---------+-------------------------------+------------+------------+----------+-------------+
| task_id | title | start_date | due_date | priority | description |
+---------+-------------------------------+------------+------------+----------+-------------+
| 1 | Learn MySQL INSERT Statement | NULL | NULL | 1 | NULL |
| 2 | Understanding DEFAULT keyword | NULL | NULL | 3 | NULL |
| 3 | Insert date into table | 2018-01-09 | 2018-09-15 | 3 | NULL |
| 4 | Use current date for the task | 2023-12-28 | 2023-12-28 | 3 | NULL |
+---------+-------------------------------+------------+------------+----------+-------------+
4 rows in set (0.00 sec)
Code language: PHP (php)
4) Inserting multiple rows example
The following statement inserts three rows into the tasks
table:
INSERT INTO tasks(title, priority)
VALUES
('My first task', 1),
('It is the second task',2),
('This is the third task of the week',3);
Code language: SQL (Structured Query Language) (sql)
In this example, each row of data is specified as a list of values in the VALUES
clause.
MySQL returns the following message:
3 row(s) affected Records: 3 Duplicates: 0 Warnings: 0
Code language: SQL (Structured Query Language) (sql)
It means that the three rows have been inserted successfully with no duplicates or warnings.
SELECT * FROM tasks;
Code language: SQL (Structured Query Language) (sql)
The table tasks
has the following data:
+---------+------------------------------------+------------+------------+----------+-------------+
| task_id | title | start_date | due_date | priority | description |
+---------+------------------------------------+------------+------------+----------+-------------+
| 1 | Learn MySQL INSERT Statement | NULL | NULL | 1 | NULL |
| 2 | Understanding DEFAULT keyword | NULL | NULL | 3 | NULL |
| 3 | Insert date into table | 2018-01-09 | 2018-09-15 | 3 | NULL |
| 4 | Use current date for the task | 2023-12-28 | 2023-12-28 | 3 | NULL |
| 5 | My first task | NULL | NULL | 1 | NULL |
| 6 | It is the second task | NULL | NULL | 2 | NULL |
| 7 | This is the third task of the week | NULL | NULL | 3 | NULL |
+---------+------------------------------------+------------+------------+----------+-------------+
7 rows in set (0.00 sec)
Code language: PHP (php)
5) Dealing with auto-increment column
Suppose you have a table that has an auto-increment column:
CREATE TABLE t(
id INT AUTO_INCREMENT PRIMARY KEY
);
The following statement insert a new row into the t
table, which uses the generated value:
INSERT INTO t VALUES();
In this statement, we don’t specify any column after the table name and any values inside the VALUES() clause.
Here’s the contents of the t table:
SELECT * FROM t;
Output:
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
Code language: JavaScript (javascript)
Summary
- Use the
INSERT
statement to insert one or more rows into a table.