VMware ESXi and vSphere Cluster Management

How to Insert New Records in MySQL

Learn how to add single or multiple rows to an existing MySQL table with INSERT INTO, including column lists, data types, constraints, verification, and troubleshooting.

The MySQL INSERT INTO statement adds one or more new rows to an existing table. A row is one record, and a column is a named field that stores one attribute for each record.

Use INSERT INTO when you want to store new data. Use SELECT when you want to retrieve and view data that is already stored.

Basic INSERT INTO syntax

The clearest form names the destination columns explicitly:

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

INSERT INTO table_name — identifies the existing table that will receive the new row.

(column1, column2, ...) — the column list identifies the destination fields.

VALUES — introduces the data to store.

(value1, value2, ...) — supplies one value for each listed column.

Values correspond to columns by position. In the following example, the first value goes into name, the second goes into surname, and the third goes into year:

INSERT INTO testtable (name, surname, year)
VALUES ('Amy', 'Goodridge', 1991);

The number and order of values must match the listed columns. This statement supplies three values for the three columns named in the same order.

Insert one row

Assume that testtable has these columns:

testtable(name, surname, year)

Insert one person's record with:

INSERT INTO testtable (name, surname, year)
VALUES ('Amy', 'Goodridge', 1991);

After successful execution, the MySQL client commonly reports a result similar to:

Query OK, 1 row affected

Affected rows is the number of rows changed by a statement. Here, it indicates that one new row was inserted.

Verify the inserted row

Use SELECT to read the table and confirm that the new record is present:

SELECT * FROM testtable;

The asterisk requests every column. The result should include a row where name is Amy, surname is Goodridge, and year is 1991. The displayed values confirm where the inserted values were stored.

Data types and value formatting

Text values are written as quoted string literals, normally using single quotation marks, such as 'Amy'.
Numeric values are normally written without quotes, such as 1991.
MySQL can coerce some quoted numeric strings, but supplying values in their natural type is clearer and helps expose mistakes.
Every value must be compatible with the target column's data type, length, range, and other rules.
NULL represents an unknown or absent value. It can be inserted only into a column that permits NULL or has an applicable default behavior.

For example, this uses a text value and a number:

INSERT INTO testtable (name, surname, year)
VALUES ('Luis', 'Chen', 1988);

Omitting the column list

You can omit the column list when you provide a value for every table column:

INSERT INTO testtable
VALUES ('Mark', 'Smith', 1955);

Without a column list, MySQL matches values against the table's defined column order. If the table order is name, surname, year, then 'Mark' goes to name, 'Smith' goes to surname, and 1955 goes to year.

Explicit column list — values follow the order of the columns written in the statement; it works when the listed columns exist and each required omitted column has a default or otherwise permits omission; generally recommended.

Omitted column list — values must follow the table's complete defined column order; it works only when a value is supplied for every table column; more fragile if the schema changes.

An explicit column list is generally clearer and safer. It documents the intended destinations and avoids depending on the table's physical definition order. It is especially useful when a table gains a new column, has an automatically generated identifier, or contains optional columns.

Insert multiple rows

One INSERT statement can add several rows. Put each row in its own parenthesized value group and separate the groups with commas:

INSERT INTO testtable (name, surname, year)
VALUES
    ('Amy', 'Goodridge', 1991),
    ('Mark', 'Smith', 1955),
    ('Luis', 'Chen', 1988);

Every value group must contain values corresponding to the same listed columns. A successful execution should report that three rows were affected, subject to the client and MySQL configuration.

Common INSERT failures

Column count does not match value count — the statement has too many or too few values for the listed columns, or for all columns when the list is omitted. Make the counts match exactly.

Values appear in the wrong columns — the column list was omitted and the values do not follow the table definition order. Use an explicit column list or reorder the values.

Incorrect value or data out of range — a value is incompatible with the target type, length, range, or format. Inspect the table definition and provide a compatible value.

Column cannot be null — a required NOT NULL column was omitted or assigned NULL without a usable default. Supply a valid value.

Duplicate entry — a value conflicts with a PRIMARY KEY or UNIQUE constraint. Choose a unique value, omit an automatically generated identifier when appropriate, or determine whether an UPDATE is needed instead.

Foreign key constraint failure — an inserted foreign-key value does not match an existing key in the referenced parent table. Insert the parent record first or use an existing referenced key.

Understanding important constraints

NOT NULL requires a value for every row in that column.
A PRIMARY KEY uniquely and non-nullably identifies each row. Two rows cannot have the same primary-key value.
UNIQUE prevents duplicate values, or duplicate combinations of values, according to its definition.
A foreign key links a value in one table to a key in another table. The referenced parent row usually must exist before the child row can be inserted.

Practical troubleshooting examples

Too few values

This statement names three columns but supplies only two values:

INSERT INTO testtable (name, surname, year)
VALUES ('Amy', 'Goodridge');

Correct it by supplying the missing value, or by changing the target column list if omitting that column is valid:

INSERT INTO testtable (name, surname, year)
VALUES ('Amy', 'Goodridge', 1991);

Duplicate primary-key value

Suppose people has a primary key named id. If an existing row already uses id value 7, this insert can fail:

INSERT INTO people (id, name, surname, year)
VALUES (7, 'Amy', 'Goodridge', 1991);

Use an unused identifier, or omit an AUTO_INCREMENT identifier when MySQL is configured to generate it:

INSERT INTO people (name, surname, year)
VALUES ('Amy', 'Goodridge', 1991);

Foreign-key failure

If a row contains a foreign-key value that does not exist in its parent table, MySQL may report an error such as Cannot add or update a child row: a foreign key constraint fails. Insert the referenced parent row first or use a valid existing parent key.

INSERT checklist

Confirm that the target table already exists and that you are using the intended database.
Prefer an explicit column list.
Check that every value matches the corresponding column by position.
Use quoted strings for text and normally unquoted values for numbers.
Provide values for required NOT NULL columns.
Check primary-key, unique, and foreign-key requirements.
Run SELECT * FROM table_name; or a more specific SELECT query to verify the result.

Exam note: The safest general pattern is INSERT INTO table_name (column1, column2) VALUES (value1, value2);. Remember that VALUES supplies the row data, while SELECT retrieves data.