Inserting Data

Introduction

INSERT adds rows to a table. This chapter covers single-row and multi-row inserts, bulk loading habits, and MySQL-specific options—INSERT IGNORE, ON DUPLICATE KEY UPDATE, and REPLACE INTO—using the blog_dev schema from the previous chapter.

Prerequisites

Creating Tables and Data Types
Tables users and posts created in blog_dev
Connected with mysql -u root -p and USE blog_dev;

Basic INSERT

Insert one row with explicit columns:

sql

INSERT INTO users (email, display_name)
VALUES ('ada@example.com', 'Ada Lo');

Code explanation:

Column list order matches VALUES order
Omitted columns use DEFAULT or NULL if allowed
id auto-increments when omitted

Insert with all columns except auto-increment:

sql

INSERT INTO users (email, display_name, is_active)
VALUES ('bob@example.com', 'Bob Chen', 1);

Verify:

sql

SELECT * FROM users;

Multi-Row INSERT

One statement, many rows—faster than repeated single inserts:

sql

INSERT INTO users (email, display_name) VALUES
  ('carol@example.com', 'Carol Wu'),
  ('dan@example.com', 'Dan Park'),
  ('eve@example.com', 'Eve Kim');

Code explanation:

Network and parse overhead reduced vs three separate INSERTs
Common for seed scripts and migrations

INSERT From SELECT

Copy rows from a query:

sql

CREATE TABLE users_archive LIKE users;
 
INSERT INTO users_archive (email, display_name, is_active, created_at)
SELECT email, display_name, is_active, created_at
FROM users
WHERE is_active = 0;

Useful for backups, reporting snapshots, or bulk moves.

Sample Posts Data

Assume users.id 1 = Ada, 2 = Bob from earlier inserts (adjust ids if yours differ):

sql

INSERT INTO posts (user_id, title, body, published_at) VALUES
  (1, 'Hello MySQL', 'First post about learning SQL.', NOW()),
  (1, 'Draft ideas', 'Unpublished notes.', NULL),
  (2, 'Bob reviews tools', 'Workbench vs CLI.', '2026-05-01 10:00:00');

Check:

sql

SELECT id, user_id, title, published_at FROM posts;

INSERT IGNORE

Skip row on duplicate key or other ignorable errors:

sql

INSERT IGNORE INTO users (email, display_name)
VALUES ('ada@example.com', 'Ada Duplicate');

Code explanation:

email has UNIQUE constraint—duplicate skipped, warning not error
No row updated; existing Ada row unchanged

Check warnings:

sql

SHOW WARNINGS;

ON DUPLICATE KEY UPDATE

Upsert—insert or update if unique key conflicts:

sql

INSERT INTO users (email, display_name)
VALUES ('ada@example.com', 'Ada Lo Updated')
ON DUPLICATE KEY UPDATE
  display_name = VALUES(display_name);

Code explanation:

On duplicate email, runs UPDATE clause instead of failing
VALUES(column) refers to value that would have been inserted

Modern alias form (MySQL 8.0.19+):

sql

INSERT INTO users (email, display_name) AS new
VALUES ('bob@example.com', 'Bob Updated')
ON DUPLICATE KEY UPDATE
  display_name = new.display_name;

REPLACE INTO

Delete existing row with same primary/unique key, then insert:

sql

REPLACE INTO users (email, display_name)
VALUES ('eve@example.com', 'Eve K.');

Code explanation:

REPLACE = delete + insert—id may change if auto-increment
Prefer ON DUPLICATE KEY UPDATE when you need stable primary keys

Bulk Insert Tips

Tip	Why
Multi-row `INSERT` batches (~1000 rows)	Balance speed and packet size
Wrap in transaction (later chapter)	Faster commit, atomic rollback
Disable unique checks only for trusted imports	Advanced—risky if data dirty
Load CSV with `LOAD DATA`	Fastest for huge files—separate topic

Warning

Never INSERT Secrets in Tutorials

Production passwords must be hashed in app code—not stored plain in SQL examples.

FAQ

Insert failed: duplicate entry?

Unique constraint violated—use IGNORE, upsert, or different key value.

Which columns required?

NOT NULL columns without DEFAULT must appear in INSERT.

Insert NULL into id?

Auto-increment assigns next value—omit column instead.

Insert vs LOAD DATA?

LOAD DATA INFILE for massive CSV—needs file privileges.

Return generated id?

SELECT LAST_INSERT_ID(); after insert—apps use this or ORM.

Insert into multiple tables atomically?

Use transaction—chapter 17.