MySQL INSERT INTO

The INSERT INTO statement is how you add new rows of data to a table. It is the C in CRUD — Create. Before any data can be read, updated, or deleted, it first has to be inserted. Understanding INSERT INTO properly is one of the most essential skills in working with MySQL.

---

Syntax

There are two forms of the INSERT INTO statement:

-- Form 1: Specify column names (recommended)
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

-- Form 2: Provide values for every column in order
INSERT INTO table_name
VALUES (value1, value2, value3);

---

Inserting a Single Row

Assume you have the following table:

CREATE TABLE students (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    city VARCHAR(100),
    age INT,
    PRIMARY KEY (id)
);

To insert one student:

INSERT INTO students (name, city, age)
VALUES ('Rahim Uddin', 'Dhaka', 22);

The id column is omitted because it is AUTO_INCREMENT — MySQL fills it in automatically. MySQL responds with:

Query OK, 1 row affected (0.01 sec)

---

Inserting Multiple Rows at Once

You can insert several rows in a single statement by separating each set of values with a comma. This is much faster than running individual inserts in a loop:

INSERT INTO students (name, city, age)
VALUES
    ('Sara Begum', 'Chittagong', 20),
    ('Karim Ali', 'Sylhet', 23),
    ('Nila Akter', 'Dhaka', 21),
    ('Rafi Hossain', 'Rajshahi', 24);

MySQL confirms with the number of rows inserted:

Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

---

Inserting NULL and DEFAULT Values

For columns that allow NULL, you can explicitly insert NULL. For columns with a DEFAULT value, you can use the DEFAULT keyword:

-- Insert with NULL for city (optional column)
INSERT INTO students (name, city, age)
VALUES ('Tanvir Islam', NULL, 25);

-- Using DEFAULT keyword
INSERT INTO students (name, city, age)
VALUES ('Mitu Khanam', DEFAULT, DEFAULT);

---

INSERT IGNORE

By default, if an INSERT violates a UNIQUE or PRIMARY KEY constraint, MySQL throws an error and stops. INSERT IGNORE changes this behavior — it silently skips the duplicate row instead of failing:

INSERT IGNORE INTO students (id, name, city, age)
VALUES (1, 'Duplicate Row', 'Dhaka', 30);

If a student with id = 1 already exists, MySQL skips the insert and issues a warning instead of an error. The rest of the statement continues normally.

---

INSERT ... ON DUPLICATE KEY UPDATE

This powerful variant says: "Insert this row — but if it already exists (based on a unique key), update it instead." It is sometimes called an upsert.

INSERT INTO students (id, name, city, age)
VALUES (1, 'Rahim Uddin', 'Dhaka', 23)
ON DUPLICATE KEY UPDATE
    city = VALUES(city),
    age  = VALUES(age);

If id = 1 does not exist, a new row is inserted. If it does exist, only city and age are updated — the name and id stay the same.

---

Key Points to Remember

• Always list column names in your INSERT INTO statement — never rely on column order alone.
• Omit AUTO_INCREMENT columns from your insert; MySQL assigns them automatically.
• Insert multiple rows in one statement for better performance compared to many single-row inserts.
• INSERT IGNORE skips rows that violate unique constraints instead of throwing an error.
• ON DUPLICATE KEY UPDATE performs an insert or an update in one step — useful for syncing data.
• String values must be wrapped in single quotes: 'Dhaka'. Numbers do not need quotes.