> ## Documentation Index > Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # MySQL INSERT > How the MySQL INSERT statement adds a row, what the server puts in the columns you leave out, and the 2 errors a first INSERT meets most often. 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. ```sql theme={null} 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 ```sql theme={null} 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 ```sql theme={null} INSERT INTO member (first_name, last_name, email, joined) VALUES ('Jennifer', 'Davis', 'jennifer.davis@example.com', '2026-03-18'); ``` ```text theme={null} 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. ```sql theme={null} SELECT * FROM member; ``` ```text theme={null} +-----------+------------+-----------+------------------------------+------------+--------+ | 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. ```sql theme={null} 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; ``` ```text theme={null} 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. ```sql theme={null} INSERT INTO member (first_name, last_name, email, joined) VALUES ('Mary', 'Smith', 'mary.smith@example.com', '2026-05-01'); ``` ```text theme={null} 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](/docs/tutorial/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. ```sql theme={null} INSERT INTO member (first_name) VALUES ('Sam'); ``` ```text theme={null} 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. ```sql theme={null} INSERT INTO member (first_name, last_name, joined) VALUES ('George', 'Miller', '2026-04-01'); ``` ```text theme={null} 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. ```sql theme={null} SELECT member_id, first_name, last_name, email, active FROM member; ``` ```text theme={null} +-----------+------------+-----------+------------------------------+--------+ | 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. ## See also * [Insert multiple rows](/docs/tutorial/insert-multiple-rows) — 1 statement, many rows * [INSERT SELECT](/docs/tutorial/insert-select) — rows from a query rather than typed out * [Upsert](/docs/tutorial/upsert) — insert, or update the row that is in the way * [UPDATE](/docs/tutorial/update) — changing a row that is already there