INSERT

Common Syntax

INSERT [INTO] tbl_name
[(col_name,...)]
{VALUES | VALUE} ({expr },...),(...),...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
OR
INSERT [INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
col_name=expr [, col_name=expr] ... ]

Syntax Constraints

• INSERT DELAYED is not supported.
• Only INSERT statements that contain sharding fields are supported.
• PARTITION syntax is not supported, so partitioned tables are not recommended.
• Setting YYYY of datetime (in the format of YYYY-MM-DD HH:MM:SS) to 1582 or any value smaller in INSERT statements is not supported.
• INSERT cannot be used to insert sharding key value DEFAULT.
• If you specify an auto-increment key value in an INSERT statement and execute it on a sharded table, the auto-increment key value of the inserted data entry changes. Auto-increment key values of data entries inserted subsequently will increase based on the first inserted data entry unless you specify a new auto-increment key value.
• Referencing a table column in function REPEAT of the VALUES statement is not supported.

  Example:

  INSERT INTO T(NAME) VALUES(REPEAT(ID,3));

• Only constants are supported when you use the INSERT DUPLICATE statement to update the sharding key. Functions and expressions, such as VALUES and LAST_INSERT_ID, are not supported.
• Sharded tables containing GSI cannot be updated using the INSERT DUPLICATE statement.

Use Constraints

• If the sharding key value in the INSERT statement is invalid, data is routed to database shard 0 or table shard 0 by default.
• Do not use functions VERSION, DATABASE, or USER in the INSERT statement. When you execute such functions, you may not obtain the expected results because their results depend on whether the statement is pushed to data nodes for execution.
• Do not use the INSERT DUPLICATE statement to update the sharding key. You may not obtain the expected results because the statement is pushed down to data nodes for execution.