Data Manipulation

INSERT

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)] ... }
[AS row_alias[(col_alias [, col_alias] ...)]]
[ON DUPLICATE KEY UPDATE assignment_list]

Inserts one or more new rows by specifying values for the given columns. A second form, INSERT...SET, inserts a row by specifying values for specific columns via an assignment list. A third form, INSERT...SELECT, inserts rows obtained from another table or tables.

If no column name is specified, a value for every column must be provided, in order. If multiple value lists are inserted, MySQL returns a status string in the format Records: 100 Duplicates: 0 Warnings: 0, where records is the number of processed rows, duplicates is the number of rows that could not be inserted because of a duplicate value in a unique index, and warnings is the number of problematic attempts. If the type of an inserted value does not match the column's defined type, the value may be clipped, truncated, or converted.

LOW_PRIORITY delays the insertion until the table is not being read; HIGH_PRIORITY prevents concurrent insertions. Both apply only to storage engines that use table-level locking (such as MyISAM, MEMORY, and MERGE). IGNORE causes errors to be ignored.

If ON DUPLICATE KEY UPDATE is specified and a duplicate is found in a UNIQUE index or PRIMARY KEY, an update of the old row is performed instead of an insert:

INSERT INTO table_name (id, name, age) VALUES (1, 'John', 25)
ON DUPLICATE KEY UPDATE age = age + 1;

INSERT INTO t SET a=9,b=5 AS new
ON DUPLICATE KEY UPDATE a=new.a+new.b;

INSERT INTO t VALUES(9,5) AS new(m,n)
ON DUPLICATE KEY UPDATE a=m+n;

The row alias (and optional column aliases) let the new row's values be referenced inside the UPDATE clause.

To insert special characters such as the apostrophe, precede the character with a backslash, e.g. \'.

This inserts into a table using values selected from another table:

INSERT INTO A(a) (SELECT b FROM B);

This performs an "upsert", inserting a new record only if it does not already exist:

INSERT INTO Item(item_name, items_in_stock)
VALUES( 'A', 27)
ON DUPLICATE KEY UPDATE
items_in_stock = 27

CREATE TABLE Item (
  item_name VARCHAR(64) PRIMARY KEY,
  items_in_stock INT
);

INSERT INTO Item(item_name, items_in_stock)
VALUES ('A', 27)
ON DUPLICATE KEY UPDATE
   items_in_stock = 27;

SELECT * FROM Item;

CREATE TABLE Item (
  item_name VARCHAR(64) PRIMARY KEY,
  items_in_stock INT
);

INSERT INTO Item(item_name, items_in_stock)
VALUES ('A', 27)
ON DUPLICATE KEY UPDATE
   items_in_stock = 27;

SELECT * FROM Item;

Query OK, 1 row affected

item_name	items_in_stock
A	27

REPLACE

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

REPLACE also supports REPLACE...SET assignment_list and REPLACE...SELECT / REPLACE...TABLE forms, mirroring INSERT.

REPLACE behaves like INSERT, except that when a duplicate value for a PRIMARY KEY or a UNIQUE index is found, the old row is deleted before the new row is inserted. A REPLACE statement returns the sum of the rows deleted and inserted.

REPLACE INTO Item(item_name, items_in_stock) VALUES ('A', 30);

SELECT * FROM Item;

REPLACE INTO Item(item_name, items_in_stock) VALUES ('A', 30);

SELECT * FROM Item;

Query OK, 2 rows affected (the sum of 1 row deleted and 1 row inserted)

item_name	items_in_stock
A	30

UPDATE

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET assignment_list
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

A multiple-table form is also available: UPDATE [LOW_PRIORITY] [IGNORE] table_references SET assignment_list [WHERE where_condition] (no ORDER BY / LIMIT).

With the ORDER BY clause, rows are updated in the order specified. LIMIT restricts the number of rows that can be updated. LOW_PRIORITY delays execution until the table is not being read (affects table-level-locking engines only). IGNORE suppresses errors. An UPDATE returns the number of rows that were changed.

This copies values from another table when updating:

UPDATE A, B
SET A.a = B.b
WHERE A.id = B.id;

CREATE TABLE A (id INT, a INT);
CREATE TABLE B (id INT, b INT);
INSERT INTO A VALUES (1, NULL);
INSERT INTO B VALUES (1, 99);

UPDATE A, B
SET A.a = B.b
WHERE A.id = B.id;

SELECT * FROM A;

CREATE TABLE A (id INT, a INT);
CREATE TABLE B (id INT, b INT);
INSERT INTO A VALUES (1, NULL);
INSERT INTO B VALUES (1, 99);

UPDATE A, B
SET A.a = B.b
WHERE A.id = B.id;

SELECT * FROM A;

Query OK, 1 row affected

id	a
1	99

DELETE

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
[PARTITION (partition_name [, partition_name] ...)]
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

Multiple-table forms are also available:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] tbl_name[.*] [, tbl_name[.*]] ... FROM table_references [WHERE where_condition]
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name[.*] [, tbl_name[.*]] ... USING table_references [WHERE where_condition]

A DELETE statement returns the number of deleted rows. LOW_PRIORITY, IGNORE, ORDER BY, and LIMIT behave the same as for UPDATE. QUICK tells the storage engine not to merge index leaves during the delete, which may speed things up.

DELETE FROM A WHERE a>3;

Multiple tables can be named in a single DELETE statement to remove rows from more than one table based on the WHERE condition. ORDER BY and LIMIT cannot be used in a multi-table delete:

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id = t2.id AND t2.id = t3.id;

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id = t2.id AND t2.id = t3.id;

SQL Modes

To set the SQL mode at server startup, use the --sql-mode="modes" command-line option, or sql-mode="modes" in an option file such as my.cnf (Unix) or my.ini (Windows). modes is a comma-separated list of individual modes.

To change the SQL mode at runtime, set the global or session sql_mode system variable:

SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';

The default SQL mode in MySQL 8.0 includes these modes:

ANSI — makes syntax and behavior conform more closely to standard SQL.
TRADITIONAL — makes MySQL behave like a "traditional" SQL database, giving an error instead of a warning when an incorrect value is inserted into a column.
ALLOW_INVALID_DATES — skips full date validation, checking only that the month is 1-12 and the day is 1-31. Applies to DATE and DATETIME columns, not TIMESTAMP.
ANSI_QUOTES — treats " as an identifier quote character rather than a string quote character (backtick still works). With this mode enabled, double quotes can no longer be used to quote string literals.
ONLY_FULL_GROUP_BY — rejects queries where the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in GROUP BY nor functionally dependent on the GROUP BY columns.
NO_ZERO_IN_DATE — affects whether the server permits dates whose year is nonzero but month or day is 0 (e.g. '2010-00-01'), but not '0000-00-00'.
NO_ZERO_DATE — affects whether '0000-00-00' is permitted as a valid date; its effect also depends on whether strict SQL mode is enabled.
ERROR_FOR_DIVISION_BY_ZERO — affects handling of division by zero, including MOD(N,0); for INSERT/UPDATE its effect also depends on strict mode.
NO_ENGINE_SUBSTITUTION — controls automatic substitution of the default storage engine when CREATE TABLE / ALTER TABLE specify a disabled or uncompiled engine.
TIME_TRUNCATE_FRACTIONAL — controls whether rounding (default) or truncation occurs when a fractional-seconds TIME/DATE/TIMESTAMP value is inserted into a column with fewer fractional digits.
STRICT_TRANS_TABLES — enables strict SQL mode for transactional storage engines, and when possible for nontransactional ones.
STRICT_ALL_TABLES — enables strict SQL mode for all storage engines; invalid data values are rejected.
REAL_AS_FLOAT — treats REAL as a synonym for FLOAT (by default MySQL treats REAL as a synonym for DOUBLE).
PIPES_AS_CONCAT — treats || as string concatenation (same as CONCAT()) rather than as a synonym for OR.
PAD_CHAR_TO_FULL_LENGTH — disables trimming of trailing spaces from CHAR values on retrieval, padding them to full length instead; does not apply to VARCHAR.
NO_UNSIGNED_SUBTRACTION — subtraction between an UNSIGNED integer and another integer produces an unsigned result; an error results if that result would otherwise be negative.
NO_DIR_IN_CREATE — ignores INDEX DIRECTORY and DATA DIRECTORY directives when creating a table; useful on replica servers.
NO_BACKSLASH_ESCAPES — disables the backslash character as an escape character in strings and identifiers; the default LIKE escape sequence is also changed so no escape character is used.
NO_AUTO_VALUE_ON_ZERO — affects handling of AUTO_INCREMENT columns; normally the next sequence value is generated by inserting NULL or 0.
IGNORE_SPACE — permits spaces between a function name and the opening parenthesis, causing built-in function names to be treated as reserved words.
HIGH_NOT_PRECEDENCE — gives NOT higher precedence, so NOT a BETWEEN b AND c parses as NOT (a BETWEEN b AND c).

See also Data Types and Transactions for related server behavior controlled outside of sql_mode.

MENU