MySQL INSERT INTO

Once a table is created, data can be added to it using the INSERT INTO statement. Each row of data entered into the table is called a record. The INSERT INTO statement specifies which table to insert into, which columns to fill, and what values to store.

Syntax

Method 1: Specifying Column Names

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

This is the recommended method — it explicitly names the columns being filled, making the query readable and safe even if the table structure changes later.

Method 2: Without Specifying Column Names

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

This works only when a value is provided for every column in the table, in the exact order the columns are defined. If any column is skipped, an error will occur.

Setting Up an Example Table

CREATE TABLE students (
    student_id  INT PRIMARY KEY AUTO_INCREMENT,
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50) NOT NULL,
    age         TINYINT,
    email       VARCHAR(100) UNIQUE
);

Inserting a Single Row

INSERT INTO students (first_name, last_name, age, email)
VALUES ('Alice', 'Johnson', 20, 'alice@example.com');

Note that student_id is omitted because it is set to AUTO_INCREMENT — MySQL assigns the value automatically.

Inserting Multiple Rows at Once

Multiple rows can be inserted in a single INSERT INTO statement by listing each row's values in parentheses, separated by commas:

INSERT INTO students (first_name, last_name, age, email)
VALUES
    ('Bob',   'Smith',   22, 'bob@example.com'),
    ('Carol', 'White',   19, 'carol@example.com'),
    ('David', 'Brown',   21, 'david@example.com');

This inserts three rows in one operation, which is faster than running three separate insert statements.

Inserting Data with NULL

If a column is optional (allows NULL), its value can be omitted from the column list, or explicitly set to NULL:

-- Omitting the age column (it will be NULL)
INSERT INTO students (first_name, last_name, email)
VALUES ('Emma', 'Davis', 'emma@example.com');

-- Explicitly inserting NULL
INSERT INTO students (first_name, last_name, age, email)
VALUES ('Frank', 'Wilson', NULL, 'frank@example.com');

Inserting Data with Default Values

If a column has a default value defined, it is applied automatically when that column is not mentioned in the insert statement. The keyword DEFAULT can also be used explicitly:

INSERT INTO students (first_name, last_name, age, email)
VALUES ('Grace', 'Lee', DEFAULT, 'grace@example.com');

Verifying the Inserted Data

After inserting records, use SELECT to view them:

SELECT * FROM students;

Sample output:

+------------+------------+-----------+------+-------------------+
| student_id | first_name | last_name | age  | email             |
+------------+------------+-----------+------+-------------------+
|          1 | Alice      | Johnson   |   20 | alice@example.com |
|          2 | Bob        | Smith     |   22 | bob@example.com   |
|          3 | Carol      | White     |   19 | carol@example.com |
|          4 | David      | Brown     |   21 | david@example.com |
+------------+------------+-----------+------+-------------------+

Common Errors and How to Avoid Them

Key Points

• The INSERT INTO statement adds one or more rows of data to a table.
• Always specify column names to make inserts clear and resistant to table changes.
• Multiple rows can be inserted in a single statement for better performance.
• Columns with AUTO_INCREMENT or default values do not need to be specified.
• Data types of the values must match the column definitions.