MySQL INSERT INTO: Add Rows to a Table with Examples

INSERT adds new rows to a table. This is the first of the data-changing statements (DML).

query.sql

SQL

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    city VARCHAR(50) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

query.sql

SQL

1
2

INSERT INTO customers (name, email, city)
VALUES ('Maya Shah', 'maya@example.com', 'Pune');

Output

OUTPUT

Query OK, 1 row affected

The message 1 row affected confirms one row was added. Look at it:

query.sql

SQL

SELECT id, name, email, city, created_at IS NOT NULL AS has_created_at FROM customers;

Output

OUTPUT

+----+-----------+------------------+------+----------------+
| id | name      | email            | city | has_created_at |
+----+-----------+------------------+------+----------------+
|  1 | Maya Shah | maya@example.com | Pune |              1 |
+----+-----------+------------------+------+----------------+
1 row in set

You did not give id or created_at. MySQL used AUTO_INCREMENT for the first and DEFAULT CURRENT_TIMESTAMP for the second.

The syntax

Text

PLAIN

1
2

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

The column list says which columns you supply.
The values are matched to the columns by position: the first value goes to the first column.
The number of values must equal the number of columns.
Text and dates go in single quotes. Numbers do not. NULL is written without quotes.

Always write the column list

MySQL also allows this form:

query.sql

SQL

INSERT INTO customers VALUES (NULL, 'Rohan Verma', 'rohan@example.com', 'Delhi', NOW());

It works only if you give a value for every column, in table order. If someone later adds a column or reorders them, your statement breaks or, worse, stores the wrong values in the wrong columns. Name the columns every time.

Columns you leave out

A column you leave out gets its default value. If it has no default and is NOT NULL, strict mode gives an error:

query.sql

SQL

INSERT INTO customers (name, city) VALUES ('No Email', 'Kochi');

Output

OUTPUT

ERROR 1364 (HY000): Field 'email' doesn't have a default value

You can request the default explicitly with the DEFAULT keyword, or set a column to NULL on purpose:

query.sql

SQL

1
2
3

INSERT INTO customers (name, email, city, created_at)
VALUES ('Sana Ali', 'sana@example.com', NULL, DEFAULT);
SELECT id, name, city FROM customers ORDER BY id;

Output

OUTPUT

+----+-------------+-------+
| id | name        | city  |
+----+-------------+-------+
|  1 | Maya Shah   | Pune  |
|  2 | Rohan Verma | Delhi |
|  3 | Sana Ali    | NULL  |
+----+-------------+-------+
3 rows in set

The `SET` form

MySQL also has an assignment-style syntax. It is MySQL-specific and not portable, but some find it readable for long rows:

query.sql

SQL

1
2

INSERT INTO customers SET name = 'Tara Nair', email = 'tara@example.com', city = 'Kochi';
SELECT COUNT(*) AS total_customers FROM customers;

Output

OUTPUT

+-----------------+
| total_customers |
+-----------------+
|               4 |
+-----------------+
1 row in set

Rules to remember

Match the order and the number of columns and values.
Provide values for every NOT NULL column that has no default.
Do not insert into AUTO_INCREMENT or generated columns unless you mean to (AUTO_INCREMENT).
When values come from a user, never paste them into the SQL text. Use prepared statements (Prepared Statements).

lightbulb

Dates and numbers

Write dates as '2026-07-15', never '15/07/2026'. Write decimals with a dot (19.99). Do not put thousands separators in numbers.

The syntax

Always write the column list

Columns you leave out

The SET form

Rules to remember

Dates and numbers

The `SET` form