INSERT — MySQL | LetCodes

INSERT in MySQL

The INSERT statement adds new rows to a table. MySQL provides several forms — from inserting a single row to bulk-loading thousands of rows efficiently — as well as special variants for handling duplicate keys. Choosing the right form can make a dramatic difference in both correctness and performance.

Always Specify Column Names

The most important rule for INSERT statements is to always list column names explicitly. Relying on implicit column order couples your SQL to the physical column order of the table, which can silently break when someone runs an ALTER TABLE to add or reorder a column.

SQL

-- GOOD: explicit column list — safe against schema changes
INSERT INTO employees (first_name, last_name, email, department_id)
VALUES ('Alice', 'Smith', 'alice@example.com', 3);

-- BAD: implicit column order — fragile, will break if columns are reordered
INSERT INTO employees
VALUES (DEFAULT, 'Bob', 'Jones', 'bob@example.com', 3, NOW());

Tip

Always list column names. If someone adds a column to the table later, an implicit INSERT will break or insert data into the wrong columns.

Inserting Expressions and Functions

Column values in an INSERT are not limited to literals — you can use any expression, function, or even a subquery. This lets you generate values at insert time rather than computing them in application code.

SQL

-- Use NOW() for created_at instead of passing from application
INSERT INTO audit_log (user_id, action, created_at)
VALUES (42, 'login', NOW());

-- Generate a UUID for a public identifier
INSERT INTO api_keys (user_id, key_value, created_at)
VALUES (42, UUID(), NOW());

-- Use DEFAULT keyword explicitly to trigger the column default
INSERT INTO articles (title, status, author_id)
VALUES ('My Post', DEFAULT, 7);

-- Computed expression
INSERT INTO pricing (product_id, base_price, tax_rate, final_price)
VALUES (101, 29.99, 0.08, 29.99 * 1.08);

INSERT INTO ... SET

The SET syntax assigns values by name, which reads more like an UPDATE statement. Useful for single-row inserts where readability matters.

SQL

INSERT INTO employees
SET first_name   = 'Carol',
    last_name    = 'White',
    email        = 'carol@example.com',
    department_id = 2;

Note

The SET form cannot be used for multi-row inserts. Use VALUES or SELECT when inserting multiple rows at once.

Multi-Row INSERT — Performance Impact

Inserting multiple rows in a single statement is far more efficient than separate INSERT calls. A single multi-row INSERT reduces round-trips, allows MySQL to optimise locking and index maintenance, and significantly cuts overhead. In practice, one INSERT with 1000 rows is often 10–100x faster than 1000 separate INSERT statements.

SQL

-- One round-trip, one index rebuild pass: very fast
INSERT INTO products (sku, name, price, stock) VALUES
  ('A001', 'Widget Pro',    19.99, 100),
  ('A002', 'Gadget Lite',    9.99, 250),
  ('A003', 'SuperGadget',   49.99,  50),
  ('A004', 'Budget Widget',  4.99, 500);

-- The same data as separate inserts: 4x the round-trips, 4x the overhead
INSERT INTO products (sku, name, price, stock) VALUES ('A001', 'Widget Pro',    19.99, 100);
INSERT INTO products (sku, name, price, stock) VALUES ('A002', 'Gadget Lite',    9.99, 250);
-- ... and so on

Tip

Batch inserts in groups of 100–1000 rows. Very large batches (tens of thousands) can cause memory pressure. Use a transaction to wrap batches for atomicity and performance.

INSERT INTO ... SELECT

Copy data from one table to another, optionally transforming it, using a SELECT subquery. This is one of the most useful patterns for archival, migration, and data transformation.

SQL

-- Archive old orders
INSERT INTO orders_archive (id, customer_id, total, created_at)
SELECT id, customer_id, total, created_at
FROM orders
WHERE created_at < '2023-01-01';

-- Copy a table structure + data
CREATE TABLE products_backup LIKE products;
INSERT INTO products_backup SELECT * FROM products;

-- Transform on the fly: normalise email case on migration
INSERT INTO users_new (id, email, name)
SELECT id, LOWER(email), TRIM(name)
FROM users_legacy;

Note

INSERT INTO ... SELECT acquires a read lock (shared) on the source table rows it scans. On large tables, run it during low-traffic periods or use LIMIT + loop to process in chunks.

INSERT IGNORE

INSERT IGNORE silently skips rows that would violate a unique constraint or other error conditions, rather than raising an error. This is useful when idempotency matters — for example, re-running an import script that may partially succeed.

SQL

CREATE TABLE subscribers (
  email VARCHAR(255) PRIMARY KEY
);

INSERT IGNORE INTO subscribers (email) VALUES ('a@example.com');
INSERT IGNORE INTO subscribers (email) VALUES ('a@example.com'); -- silently skipped
INSERT IGNORE INTO subscribers (email) VALUES ('b@example.com');

-- Check how many rows were actually inserted (0 for duplicates)
SELECT ROW_COUNT();

Warning

INSERT IGNORE suppresses ALL errors for that row, including data truncation, out-of-range values, and other integrity violations — not just duplicate-key errors. Use it carefully; silent data loss can be worse than a visible error.

INSERT ... ON DUPLICATE KEY UPDATE (Upsert)

This is MySQL's upsert mechanism. If the inserted row would cause a duplicate key violation, MySQL runs the UPDATE clause instead of failing. This is the recommended way to do insert-or-update operations.

ROW_COUNT() behaviour:

Returns 1 when the row is inserted
Returns 2 when the row is updated with a different value
Returns 0 when the row exists and the UPDATE would not change any value

SQL

-- Insert a page-view counter; increment if the record already exists
INSERT INTO page_views (page, views)
VALUES ('/home', 1)
ON DUPLICATE KEY UPDATE
  views = views + 1;

-- Check outcome
SELECT ROW_COUNT();  -- 1=inserted, 2=updated, 0=no change

-- Upsert product stock levels
INSERT INTO inventory (sku, quantity)
VALUES ('A001', 50)
ON DUPLICATE KEY UPDATE
  quantity = quantity + VALUES(quantity);

-- Full upsert: update all non-key columns
INSERT INTO employees (id, first_name, last_name, email)
VALUES (42, 'Dave', 'Brown', 'dave@example.com')
ON DUPLICATE KEY UPDATE
  first_name = VALUES(first_name),
  last_name  = VALUES(last_name),
  email      = VALUES(email);

Note

In MySQL 8.0.20+ the VALUES(col) function inside ON DUPLICATE KEY UPDATE is deprecated. Use an alias instead.

SQL

-- MySQL 8.0.20+ preferred syntax using row alias
INSERT INTO inventory (sku, quantity)
VALUES ('A001', 50) AS new_row
ON DUPLICATE KEY UPDATE
  quantity = quantity + new_row.quantity;

REPLACE INTO

REPLACE INTO deletes the existing row (if a duplicate key exists) and inserts a fresh row. It is functionally equivalent to DELETE + INSERT, which means:

A new AUTO_INCREMENT ID is generated.
All columns not specified get their DEFAULT values.
DELETE triggers fire first, then INSERT triggers fire.
Foreign key ON DELETE rules execute.

SQL

REPLACE INTO settings (key_name, value)
VALUES ('theme', 'dark');

Warning

Prefer INSERT ... ON DUPLICATE KEY UPDATE over REPLACE INTO. REPLACE silently deletes rows, which fires DELETE triggers, resets AUTO_INCREMENT, orphans FK children, and can break foreign keys pointing at the row.

LOAD DATA INFILE — Bulk CSV Import

For importing millions of rows from a CSV file, LOAD DATA INFILE is the fastest method — typically 10–20x faster than batched INSERT statements because it bypasses much of the SQL parsing and row-by-row overhead.

SQL

-- Basic CSV import
LOAD DATA INFILE '/var/lib/mysql-files/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '
'
IGNORE 1 ROWS  -- skip header line
(first_name, last_name, email, created_at);

-- Handle NULLs and transform on load
LOAD DATA INFILE '/var/lib/mysql-files/orders.csv'
INTO TABLE orders
FIELDS TERMINATED BY ','
LINES TERMINATED BY '
'
IGNORE 1 ROWS
(customer_id, @total_raw, @date_raw)
SET total      = CAST(@total_raw AS DECIMAL(10,2)),
    created_at = STR_TO_DATE(@date_raw, '%m/%d/%Y');

Note

The file path must be accessible to the MySQL server process. Use LOAD DATA LOCAL INFILE to load from the client machine — but this requires local_infile=ON on both server and client.

INSERT and Triggers

BEFORE INSERT and AFTER INSERT triggers fire automatically whenever an INSERT executes. They let you validate data, compute derived values, or maintain audit logs without changing application code.

SQL

-- BEFORE INSERT: normalise email case and generate a slug
DELIMITER $$
CREATE TRIGGER trg_users_before_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  SET NEW.email = LOWER(TRIM(NEW.email));
  SET NEW.username = LOWER(TRIM(NEW.username));
END$$
DELIMITER ;

-- AFTER INSERT: write to audit log
DELIMITER $$
CREATE TRIGGER trg_orders_after_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (table_name, row_id, action, changed_at)
  VALUES ('orders', NEW.id, 'INSERT', NOW());
END$$
DELIMITER ;

Transaction-Wrapped Multi-Table Insert

When an operation spans multiple tables, wrap it in a transaction. If any INSERT fails, all preceding inserts in the transaction roll back — leaving the database consistent.

SQL

START TRANSACTION;

-- Insert the parent order
INSERT INTO orders (customer_id, total, status)
VALUES (101, 249.97, 'pending');
SET @order_id = LAST_INSERT_ID();

-- Insert line items
INSERT INTO order_items (order_id, product_id, qty, unit_price) VALUES
  (@order_id, 5,  2, 49.99),
  (@order_id, 12, 1, 149.99);

-- Decrement inventory
UPDATE inventory SET qty = qty - 2 WHERE product_id = 5;
UPDATE inventory SET qty = qty - 1 WHERE product_id = 12;

-- Only commit if all statements succeeded
COMMIT;

Warning

If any statement inside the transaction fails and you do not catch the error and ROLLBACK, the transaction remains open and holds locks. Always pair START TRANSACTION with error handling that calls ROLLBACK on failure.

Performance Tips for Bulk Inserts

SQL

-- 1. Wrap multiple rows in a single INSERT (shown above)

-- 2. Disable auto-commit and batch in transactions
SET autocommit = 0;
INSERT INTO logs (...) VALUES (...);
INSERT INTO logs (...) VALUES (...);
-- ... thousands of rows ...
COMMIT;
SET autocommit = 1;

-- 3. Temporarily disable unique index checks for bulk loads (use carefully)
SET unique_checks = 0;
SET foreign_key_checks = 0;
-- ... bulk INSERT ...
SET unique_checks = 1;
SET foreign_key_checks = 1;

-- 4. Use LOAD DATA INFILE for CSV imports (fastest method)
LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '
'
IGNORE 1 ROWS
(first_name, last_name, email);

Returning Inserted Data

Unlike PostgreSQL, MySQL does not have a RETURNING clause. To get inserted data back, use LAST_INSERT_ID() for the auto-increment value, or run a SELECT immediately after.

SQL

INSERT INTO orders (customer_id, total)
VALUES (101, 250.00);

-- Get the new order ID
SELECT LAST_INSERT_ID() AS new_order_id;

-- Or use it immediately in a follow-up insert
INSERT INTO order_items (order_id, product_id, qty)
VALUES (LAST_INSERT_ID(), 5, 2);

Comparison: INSERT Variants

Variant	Duplicate Key Behaviour	Triggers	Use Case
INSERT	Error (ER_DUP_ENTRY)	BEFORE + AFTER INSERT	Normal inserts
INSERT IGNORE	Silently skip row	BEFORE + AFTER INSERT (if inserted)	Skip duplicates, idempotent imports
INSERT ON DUPLICATE KEY UPDATE	Run UPDATE clause	INSERT or UPDATE triggers	Upsert / counters
REPLACE INTO	DELETE old + INSERT new	DELETE + INSERT triggers	Full row replacement (use sparingly)
LOAD DATA INFILE	Error (or IGNORE)	BEFORE + AFTER INSERT per row	Bulk CSV import

Best Practices

Always specify column names in INSERT statements to protect against schema changes.
Use multi-row INSERT for batches — avoid individual INSERT statements in a loop.
Use INSERT ON DUPLICATE KEY UPDATE (not REPLACE) for upsert operations.
Wrap large bulk loads in a single transaction to improve speed and atomicity.
Use LOAD DATA INFILE for CSV imports of millions of rows.
Validate and sanitise data before inserting — rely on parameterised queries in your application layer to prevent SQL injection.
Check innodb_buffer_pool_size and innodb_log_file_size before large imports; too-small log files slow bulk inserts significantly.