MySQL Data Insertion

What is the INSERT Statement in MySQL?

The INSERT statement in MySQL is used to add new rows of data into a table. Each row represents a unique record, and the statement can insert data into one or more columns at a time. This operation is crucial for populating your database with the information your applications need to function.

Basic Syntax:

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

• table_name – The name of the table where data will be inserted.
• column1, column2, ... – The columns that will receive new values.
• value1, value2, ... – The actual data to insert into the specified columns.

Step-by-Step Example: Inserting a Single Row

Let’s say we have a table called employees:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    hire_date DATE
);

To insert a new employee record:

INSERT INTO employees (first_name, last_name, email, hire_date)
VALUES ('John', 'Doe', '[email protected]', '2026-04-24');

Explanation of the Code:

1. Target Table: employees is specified as the table to insert into.
2. Column Selection: We explicitly mention the columns first_name, last_name, email, and hire_date. This ensures that data is mapped correctly.
3. Values Provided: The values 'John', 'Doe', '[email protected]', and '2026-04-24' correspond to the columns listed.
4. Primary Key Handling: The id column is an auto-increment field, so we do not need to provide a value—it is generated automatically.

Inserting Multiple Rows at Once

MySQL also allows inserting multiple records in a single query, which is efficient and reduces the number of database calls.

INSERT INTO employees (first_name, last_name, email, hire_date)
VALUES 
    ('Alice', 'Smith', '[email protected]', '2026-04-20'),
    ('Bob', 'Johnson', '[email protected]', '2026-04-21'),
    ('Carol', 'White', '[email protected]', '2026-04-22');

How it Works:

• Each set of parentheses represents one row of data.
• The order of values must match the column order.
• This method improves performance compared to inserting rows one by one.

Inserting Data Without Specifying Columns

If you are inserting values for all columns in the table and in the exact order they were created, you can skip the column list:

INSERT INTO employees
VALUES (NULL, 'David', 'Brown', '[email protected]', '2026-04-23');

Notes:

• NULL is used for the id column because it is auto-incremented.
• Omitting columns is convenient but can lead to errors if the table structure changes. It is best practice to always specify columns explicitly.

Using INSERT IGNORE and ON DUPLICATE KEY UPDATE

INSERT IGNORE

This variant prevents errors when inserting duplicate keys. If a conflict occurs, the row is ignored rather than causing the query to fail.

INSERT IGNORE INTO employees (first_name, last_name, email)
VALUES ('John', 'Doe', '[email protected]');

ON DUPLICATE KEY UPDATE

This is useful for updating existing records if a duplicate key is found:

INSERT INTO employees (id, first_name, last_name, email)
VALUES (1, 'John', 'Doe', '[email protected]')
ON DUPLICATE KEY UPDATE email = VALUES(email);

Explanation:

• If a record with id = 1 already exists, only the email column is updated.
• This approach is great for upsert operations (insert or update).

Best Practices for Using INSERT

1. Always Specify Columns: Avoid errors if table structure changes.
2. Use Bulk Inserts When Possible: Reduces server load and improves speed.
3. Handle Duplicates Gracefully: Use INSERT IGNORE or ON DUPLICATE KEY UPDATE when necessary.
4. Validate Data: Ensure data types and constraints are respected to avoid insertion errors.