Four Common MySQL Insert Statements and Their Usage

MySQL provides several ways to insert data beyond the basic INSERT statement. This guide covers the four most common insertion methods, explains their syntax, optional modifiers, and practical implications.

INSERT INTO

The standard INSERT INTO adds rows and fails with an error if a primary‑key conflict occurs. Optional modifiers include LOW_PRIORITY , DELAYED , HIGH_PRIORITY , and IGNORE . Example syntax:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    {VALUES | VALUE} (value_list) [, (value_list)] ...
    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT INTO SELECT

This form copies data from one table to another in a single transaction. When the source and target are the same table, MySQL creates an internal temporary table; the statement cannot reference a temporary table twice.

REPLACE INTO

REPLACE INTO works like INSERT but first deletes any existing row that matches a primary‑key or unique index, then inserts the new row. It requires INSERT, UPDATE, and DELETE privileges. Syntax:

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    {VALUES | VALUE} (value_list) [, (value_list)] ...

Note: The target table must have a primary key or unique index; otherwise duplicate rows may be inserted.

INSERT … ON DUPLICATE KEY UPDATE

If an insertion would cause a duplicate in a unique index or primary key, this clause updates the existing row instead. When both a primary key and a unique key exist, the primary key takes precedence. This method can be unsafe if multiple unique keys are defined.

Summary

Understanding the four insertion methods helps avoid performance pitfalls and data‑integrity issues.

In high‑throughput benchmarks, MySQL can achieve 10k–60k TPS per server, but INSERT performance has limits.

REPLACE INTO may cause costly delete‑insert cycles and index page splits.

INSERT … ON DUPLICATE KEY UPDATE can be unsafe with multiple unique keys.

INSERT SELECT is effective for bulk data migration but should use indexed columns and may become a large transaction.