MySQL INSERT: Complete Guide

Basic INSERT Syntax

The INSERT statement adds new rows to a table. Here's the most common form:

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

For example, inserting a user:

INSERT INTO users (name, email, created_at)
VALUES ('Alice', 'alice@example.com', NOW());

Inserting Multiple Rows

Don't run INSERT in a loop—batch your inserts for dramatically better performance:

INSERT INTO users (name, email)
VALUES
  ('Alice', 'alice@example.com'),
  ('Bob', 'bob@example.com'),
  ('Carol', 'carol@example.com');

This is much faster than three separate INSERT statements because it reduces network round trips and allows MySQL to optimize the operation.

Performance tip: For bulk loading thousands of rows, batch them in groups of 1,000-5,000. Too many values in a single statement can hit MySQL's max_allowed_packet limit.

Column Order and Omission

You can specify columns in any order:

INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice');

Omit columns with default values or that allow NULL:

-- Assuming 'id' is AUTO_INCREMENT and 'bio' allows NULL
INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com');

INSERT Without Column List

You can omit the column list entirely, but don't:

-- This works but is fragile
INSERT INTO users
VALUES (1, 'Alice', 'alice@example.com', NULL, NOW());

This breaks if you add, remove, or reorder columns. Always specify columns explicitly.

INSERT...SET Syntax

MySQL offers an alternative syntax that some find more readable:

INSERT INTO users
SET
  name = 'Alice',
  email = 'alice@example.com',
  created_at = NOW();

This only works for single-row inserts.

INSERT...SELECT

Copy data from one table to another:

INSERT INTO users_archive (id, name, email, archived_at)
SELECT id, name, email, NOW()
FROM users
WHERE created_at < '2023-01-01';

Create a backup table:

-- Create table with same structure
CREATE TABLE users_backup LIKE users;

-- Copy all data
INSERT INTO users_backup
SELECT * FROM users;

Handling Duplicates

ON DUPLICATE KEY UPDATE (Upsert)

When a unique key conflict occurs, update the existing row instead:

INSERT INTO page_views (page_id, view_count, last_viewed)
VALUES (1, 1, NOW())
ON DUPLICATE KEY UPDATE
  view_count = view_count + 1,
  last_viewed = NOW();

Use VALUES() to reference the value that would have been inserted:

INSERT INTO products (sku, name, price)
VALUES ('ABC123', 'Widget', 29.99)
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  price = VALUES(price);

INSERT IGNORE

Skip rows that would cause duplicate key errors:

INSERT IGNORE INTO users (id, email)
VALUES (1, 'alice@example.com');

Warning: INSERT IGNORE also ignores other errors like data truncation. Use with caution.

REPLACE INTO

Delete the existing row and insert the new one:

REPLACE INTO users (id, name, email)
VALUES (1, 'Alice Updated', 'alice@example.com');

Warning: REPLACE deletes then inserts, which triggers DELETE and INSERT triggers, resets AUTO_INCREMENT values, and breaks foreign key relationships. Prefer ON DUPLICATE KEY UPDATE.

Getting the Inserted ID

After inserting, get the auto-generated ID:

INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com');

SELECT LAST_INSERT_ID();

In application code, your MySQL driver will have a method for this (e.g., insertId in Node.js, lastrowid in Python).

Inserting Date and Time

INSERT INTO events (name, event_date, event_time, created_at)
VALUES (
  'Conference',
  '2024-06-15',           -- DATE: YYYY-MM-DD
  '14:30:00',             -- TIME: HH:MM:SS
  '2024-01-15 09:30:00'   -- DATETIME: YYYY-MM-DD HH:MM:SS
);

Use MySQL functions for current time:

INSERT INTO logs (message, created_at)
VALUES ('User logged in', NOW());

-- Or for just the date
INSERT INTO reports (report_date, data)
VALUES (CURDATE(), '...');

Inserting NULL

Explicitly insert NULL:

INSERT INTO users (name, email, phone)
VALUES ('Alice', 'alice@example.com', NULL);

Or omit the column if it allows NULL:

INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com');
-- phone will be NULL

Inserting JSON

MySQL 5.7+ supports native JSON:

INSERT INTO products (name, attributes)
VALUES ('Laptop', '{"color": "silver", "ram": "16GB", "storage": "512GB"}');

-- Or use JSON_OBJECT for cleaner syntax
INSERT INTO products (name, attributes)
VALUES ('Laptop', JSON_OBJECT('color', 'silver', 'ram', '16GB'));

Common Errors

Column Count Mismatch

-- Error: Column count doesn't match value count
INSERT INTO users (name, email)
VALUES ('Alice');

Fix: Ensure you have one value for each column listed.

Data Too Long

-- Error: Data too long for column 'name'
INSERT INTO users (name) VALUES ('A very long name that exceeds the column limit...');

Fix: Check your VARCHAR limits or truncate the data.

Duplicate Entry

-- Error: Duplicate entry 'alice@example.com' for key 'email'
INSERT INTO users (email) VALUES ('alice@example.com');

Fix: Use ON DUPLICATE KEY UPDATE, INSERT IGNORE, or check before inserting.

Quick Reference

| Pattern | Use Case | |---------|----------| | INSERT INTO t (cols) VALUES (vals) | Basic insert | | INSERT INTO t (cols) VALUES (...), (...), (...) | Bulk insert | | INSERT INTO t SET col = val | Alternative single-row syntax | | INSERT INTO t SELECT ... | Copy from another table | | INSERT ... ON DUPLICATE KEY UPDATE | Upsert (insert or update) | | INSERT IGNORE | Skip duplicates silently | | REPLACE INTO | Delete and reinsert (use sparingly) |

Summary

• Always specify column names explicitly
• Batch multiple rows into single INSERT statements for performance
• Use ON DUPLICATE KEY UPDATE for upserts instead of REPLACE
• Use LAST_INSERT_ID() to get auto-generated IDs
• Be careful with INSERT IGNORE—it hides more than just duplicate errors