INSERT-PolarDB(PolarDB)-阿里云帮助中心

Inserts one or more rows into a table. PolarDB for XScale supports three syntax variants.

Syntax

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

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] [schema_name.]tbl_name
    SET assignment_list
    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] [schema_name.]tbl_name
    [(col_name [, col_name] ...)]
    SELECT ...
    [ON DUPLICATE KEY UPDATE assignment_list]

value_list:
    value [, value] ...

value:
    {expr | DEFAULT}

assignment_list:
    assignment [, assignment] ...

assignment:
    col_name = value

Limitations

Unsupported syntax

The following INSERT patterns are not supported:

Pattern	Example
`INSERT IGNORE` combined with `ON DUPLICATE KEY UPDATE`	`INSERT IGNORE INTO tb (id) VALUES(7) ON DUPLICATE KEY UPDATE id = id + 1;`
`PARTITION` clause	`INSERT INTO tb PARTITION (p0) (id) VALUES(7);`
Nested `NEXTVAL` in a value expression	`INSERT INTO tb(id) VALUES(SEQ1.NEXTVAL + 1);`
Column name references within `VALUES`	`INSERT INTO tb(id1, id2) VALUES(1, id1 + 1);`

Distributed transaction limits

Note A transaction that executes entirely within a single database shard — even when the table is sharded — is treated as a single-database transaction. For example, if a transaction includes a shard key and all INSERT or UPDATE operations route to the same shard, it is a single-database transaction and is not subject to the restrictions below.

When the distributed transaction feature is enabled, INSERT is not supported in the following cases.

Table without a primary key

CREATE TABLE tb(id INT, name VARCHAR(10));
INSERT INTO tb VALUES(1, 'a');  -- Not supported

Non-sharded table with AUTO_INCREMENT primary key but no DRDS sequence

CREATE TABLE tb(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(10));
INSERT INTO tb(name) VALUES('a');  -- Not supported

Workaround: Define the primary key with AUTO_INCREMENT BY GROUP to use a DRDS sequence.

CREATE TABLE tb(id INT PRIMARY KEY AUTO_INCREMENT BY GROUP, name VARCHAR(10));
INSERT INTO tb(name) VALUES('a');  -- Supported

References

INSERT statement in the MySQL 5.7 reference manual