MySQL INSERT

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

Every lesson so far has read data. INSERT writes it. From here on a statement changes what is in the table, so the rest of the tutorial works in a database of its own rather than in the sample data.

A practice database

Sakila is the reference every reading lesson describes, so leave it alone. Make a small database beside it instead, and run this again whenever you want to start over.

DROP DATABASE IF EXISTS sakila_practice;
CREATE DATABASE sakila_practice;
USE sakila_practice;

CREATE TABLE member (
  member_id  INT UNSIGNED NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(45) NOT NULL,
  last_name  VARCHAR(45) NOT NULL,
  email      VARCHAR(100) DEFAULT NULL,
  joined     DATE NOT NULL,
  active     TINYINT NOT NULL DEFAULT 1,
  PRIMARY KEY (member_id),
  UNIQUE KEY uq_member_email (email)
);

INSERT INTO member (first_name, last_name, email, joined, active) VALUES
  ('Mary',     'Smith',    'mary.smith@example.com',       '2026-01-04', 1),
  ('Patricia', 'Johnson',  'patricia.johnson@example.com', '2026-02-11', 1),
  ('Linda',    'Williams', 'linda.williams@example.com',   '2026-03-02', 0);

CREATE TABLE rating_count (
  rating VARCHAR(10) NOT NULL,
  films  INT UNSIGNED NOT NULL,
  PRIMARY KEY (rating)
);

CREATE TABLE account (
  account_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  owner      VARCHAR(45) NOT NULL,
  balance    DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (account_id)
);

INSERT INTO account (owner, balance) VALUES
  ('Mary Smith',       500.00),
  ('Patricia Johnson', 250.00);

Every lesson in this section starts from that state. Open the client with mysql -u root -p sakila_practice before you start, and run the block again between lessons.

How INSERT works

INSERT INTO TABLE_NAME (COLUMN_LIST)
VALUES (VALUE_LIST);

Replace TABLE_NAME with the table you are writing to, COLUMN_LIST with the columns you are supplying, and VALUE_LIST with 1 value for each of those columns, in the same order. The server adds a row and reports how many it wrote. It prints no rows back, so a SELECT afterwards is how you see the result.

Examples

1) Add a row

INSERT INTO member (first_name, last_name, email, joined)
VALUES ('Jennifer', 'Davis', 'jennifer.davis@example.com', '2026-03-18');

Query OK, 1 row affected (0.00 sec)

Query OK and 1 row affected is the whole report. Ask for the table to see what landed.

SELECT * FROM member;

+-----------+------------+-----------+------------------------------+------------+--------+
| member_id | first_name | last_name | email                        | joined     | active |
+-----------+------------+-----------+------------------------------+------------+--------+
|         1 | Mary       | Smith     | mary.smith@example.com       | 2026-01-04 |      1 |
|         2 | Patricia   | Johnson   | patricia.johnson@example.com | 2026-02-11 |      1 |
|         3 | Linda      | Williams  | linda.williams@example.com   | 2026-03-02 |      0 |
|         4 | Jennifer   | Davis     | jennifer.davis@example.com   | 2026-03-18 |      1 |
+-----------+------------+-----------+------------------------------+------------+--------+
4 rows in set (0.00 sec)

The statement named 4 columns and the row has 6. member_id counted itself up because the column is AUTO_INCREMENT, and active took the DEFAULT 1 the table declares.

2) Read back the id the server chose

An AUTO_INCREMENT value is decided during the insert, so the only way to learn it is to ask. Ask in the same session that did the insert.

INSERT INTO member (first_name, last_name, email, joined)
VALUES ('Barbara', 'Jones', 'barbara.jones@example.com', '2026-03-20');
SELECT LAST_INSERT_ID() AS new_id;

Query OK, 1 row affected (0.00 sec)

+--------+
| new_id |
+--------+
|      5 |
+--------+
1 row in set (0.00 sec)

LAST_INSERT_ID() answers for your connection alone, so a busy server cannot hand you another session’s id. A fresh connection has no answer to give and returns 0.

ERROR 1062 means the row is already there

A UNIQUE column refuses a second row carrying a value it already holds. Here email is unique, and Mary Smith’s address is taken.

INSERT INTO member (first_name, last_name, email, joined)
VALUES ('Mary', 'Smith', 'mary.smith@example.com', '2026-05-01');

ERROR 1062 (23000): Duplicate entry 'mary.smith@example.com' for key 'member.uq_member_email'

The message names the value and the index that rejected it, which is usually enough to tell a genuine duplicate from a column you meant to make unique and did not. Upsert is the statement that expects this and updates the existing row instead.

ERROR 1364 means a required column is missing

Leave out a column that is NOT NULL and has no default, and the server has nothing to write.

INSERT INTO member (first_name) VALUES ('Sam');

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

It names 1 column at a time, so a statement missing 3 of them takes 3 goes. Read the table with DESCRIBE member; and supply everything marked NO under Null that has no default. A column that accepts NULL is a different case. Leave it out and the row gets NULL.

INSERT INTO member (first_name, last_name, joined)
VALUES ('George', 'Miller', '2026-04-01');

Query OK, 1 row affected (0.00 sec)

A duplicate key still uses up an id

Ask for the ids now and 6 is missing.

SELECT member_id, first_name, last_name, email, active FROM member;

+-----------+------------+-----------+------------------------------+--------+
| member_id | first_name | last_name | email                        | active |
+-----------+------------+-----------+------------------------------+--------+
|         1 | Mary       | Smith     | mary.smith@example.com       |      1 |
|         2 | Patricia   | Johnson   | patricia.johnson@example.com |      1 |
|         3 | Linda      | Williams  | linda.williams@example.com   |      0 |
|         4 | Jennifer   | Davis     | jennifer.davis@example.com   |      1 |
|         5 | Barbara    | Jones     | barbara.jones@example.com    |      1 |
|         7 | George     | Miller    | NULL                         |      1 |
+-----------+------------+-----------+------------------------------+--------+
6 rows in set (0.00 sec)

The duplicate-key attempt took 6 before it failed, and InnoDB does not hand a number back. The ERROR 1364 attempt took nothing, because it was refused before it reached the table. Gaps like this are normal and mean nothing is lost. Treat the column as an identifier, never as a count of the rows or as a promise that the numbers run without a break.

Summary

Write INSERT INTO table (columns) VALUES (values) to add a row.
Expect a count of rows written rather than the row itself.
Leave out a column with a default or AUTO_INCREMENT and let the server fill it.
Read ERROR 1062 as a value a UNIQUE column already holds.
Read ERROR 1364 as a required column you did not supply.
Expect gaps in an AUTO_INCREMENT column, because a duplicate-key failure keeps its number.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

A practice database

How INSERT works

Examples

1) Add a row

2) Read back the id the server chose

ERROR 1062 means the row is already there

ERROR 1364 means a required column is missing

A duplicate key still uses up an id

Summary

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​A practice database

​How INSERT works

​Examples

​1) Add a row

​2) Read back the id the server chose

​ERROR 1062 means the row is already there

​ERROR 1364 means a required column is missing

​A duplicate key still uses up an id

​Summary

​See also

A practice database

How INSERT works

Examples

1) Add a row

2) Read back the id the server chose

ERROR 1062 means the row is already there

ERROR 1364 means a required column is missing

A duplicate key still uses up an id

Summary

See also