Help Center/ GaussDB/ Developer Guide(Centralized_V2.0-3.x)/ Development and Design Proposal/ Database Programming Specifications/ INSERT
Updated on 2025-03-13 GMT+08:00
View PDF
Share

INSERT

  • [Specification] INSERT ON DUPLICATE KEY UPDATE does not support UPDATE on columns with PRIMARY KEY or UNIQUE constraints.

    The semantics of INSERT ON DUPLICATE KEY UPDATE is to update the rows that have UNIQUE constraint conflicts. In this process, the value of the constraint should not be updated.

  • [Rule] Do not run INSERT ON DUPLICATE KEY UPDATE on a table that has multiple UNIQUE constraints.

    A table may have multiple unique indexes, or both primary keys and unique indexes. When multiple UNIQUE constraints exist, the system checks all the UNIQUE constraints by default. If any constraint conflicts, the system updates the conflicting rows. That is, multiple records may be updated, which does not meet the service expectation. More specific conditions for inserting and updating data shall be added.

  • [Recommendation] In the case of batch insertion, you are advised to use executeBatch to execute INSERT INTO VALUES (?). The execution efficiency is higher than that of executing multiple INSERT INTO VALUES() or INSERT INTO VALUES(?), ...,(?) statements.