MySQL INSERT INTO Guide with Practical Examples

You created the table and it holds no rows yet. INSERT puts them there: you name the table, list the columns you are filling, and give one value for each of them. The examples below run on MySQL 8.4 against this table:

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        VARCHAR(50) NOT NULL,
  city        VARCHAR(50) DEFAULT 'unknown'
);

If the database that holds it does not exist yet, MySQL CREATE DATABASE covers that first step.

Insert a row into the table

Reach the mysql> prompt and select the database before anything else, because INSERT writes into whichever database is currently selected:

mysql -u username -p

USE dbname;

Then write the statement. The column list says which columns you are filling, and the VALUES list gives them in the same order:

INSERT INTO customers (customer_id, name, city) VALUES (1, 'Ada', 'Lisbon');

Query OK, 1 row affected (0.01 sec)

The account you logged in with needs the INSERT privilege for the table, which the MySQL manual requires for every insert. An account created with CREATE USER and nothing else does not have it until someone grants it.

Read the row back with a SELECT, so that a statement MySQL accepted is also a row you can see:

SELECT * FROM customers;

Insert several rows in one statement

One INSERT takes as many value lists as you want to give it, separated by commas. Three rows in one statement cost one round trip to the server instead of three:

INSERT INTO customers (customer_id, name, city) VALUES
  (2, 'Grace', 'Porto'),
  (3, 'Linus', 'Braga'),
  (4, 'Rosa',  'Lisbon');

Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

The second line is the information string MySQL returns for a multi-row insert. Records counts the rows the statement processed, Duplicates counts the rows dropped because they would have duplicated a unique index value, and Warnings counts the values MySQL had to complain about. Read all three. A statement that reports three records and one duplicate wrote two rows, and the word "Duplicates" is the only place it says so.

SELECT * FROM customers ORDER BY customer_id;

What happens to the columns you leave out

The column list is optional, and leaving a column out of it is a decision rather than an omission. Insert a customer without a city:

INSERT INTO customers (customer_id, name) VALUES (5, 'Alan');

SELECT customer_id, name, city FROM customers WHERE customer_id = 5;

The city column was declared with DEFAULT 'unknown', so that is the value it gets. A column that has no default and accepts NULL gets NULL. For a NOT NULL column with no default, such as name, the SQL mode decides. Strict SQL mode, which the manual lists among the modes enabled by default in MySQL 8.4, rejects the statement with error 1364:

INSERT INTO customers (customer_id) VALUES (7);

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

Turn strict mode off for the session and the same statement writes the row, with a warning and the implicit default for the column type, which for a string column is the empty string:

SET SESSION sql_mode = '';
INSERT INTO customers (customer_id) VALUES (7);
SELECT customer_id, name, city FROM customers WHERE customer_id = 7;

That is how a name you meant to supply becomes an empty string. The setting belongs to the session, so the next connection is back in strict mode.

Drop the column list entirely and you must supply a value for every column, in the order the table declares them:

INSERT INTO customers VALUES (6, 'Edsger', 'Faro');

That form is shorter to type and breaks the moment somebody adds a column. Write the column list in anything you intend to keep.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Insert and import data in DbSchema

DbSchema is a MySQL client and visual designer, and it offers three routes to the same rows: type them into a grid, write the SQL, or load a file. All three write to the connected MySQL database rather than to the design model file, so the rows are in MySQL as soon as the change is committed.

Type the rows into the Relational Data Editor

Choose New Relational Data Editor from the Editors menu to open a blank editor, or right-click a table header in the diagram and choose Open in Relational Data Editor. Click the Insert button in the table footer, fill in the fields, and click Commit to persist the row to MySQL. Rollback discards a pending change instead.

Write the statement in the SQL Editor

Open the SQL Editor from the Editors menu and write the same INSERT you would type at the mysql> prompt. Execute Query runs the statement at the cursor. An INSERT needs an explicit COMMIT to become permanent, which the Commit button in the toolbar sends.

Load a file with the Data Importer

Choose Data Tools → Import Data From File to open the Data Importer, which reads delimited text (CSV, TSV and custom separators), XML, and Excel XLS and XLSX. Point it at the file, pick the target schema, and choose whether to import into an existing table or create a new one.

Map the file columns onto the table

Each file column is mapped to a database column, and a column you do not want is left out of the mapping. Under Settings, First Line is Header takes the column names from the first row instead of importing it as data, and the separator, quote and escape characters tell the importer how to split each line.

Adjust the types before the rows land

When the Data Importer creates a new table, it derives the column names and types from the file header and the content preview, and you can change a type or a length before the import starts. When you import into a table that already exists and DbSchema is connected, a column change you make here is applied to MySQL immediately. Errors and their line numbers appear in the Error pane at the bottom of the dialog while the import runs.

Download DbSchema at https://dbschema.com/download.html, connect it to your MySQL server, and put the first rows in from whichever of the three routes suits the data you have. The SQL Editor is in the free Community Edition; the Relational Data Editor and the Data Importer are in the Pro edition.