Is it possible to make a batch insert/update if the uniqueness of the record is a bundle of two fiel-CodePudding

I have the following table structure (example)

CREATE TABLE `Test` (
  `id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `position_id` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `price` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `Test` ADD PRIMARY KEY (`id`);
ALTER TABLE `Test` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

This table contains data that is constantly in need of updating. There is also new data that needs to be entered. Since there is a lot of data, it will take quite a long time to check each record to make it insert or update.

After studying the question, I realized that I need to use batch insert/update with:

INSERT on DUPLICATE KEY UPDATE

But the documentation says that the fields must have a unique index. But I don't have any unique fields, I can't use the ID field. The uniqueness of the record can only be in a combination of two fields order_id and position_id.

Is it possible to make a batch insert/update if the uniqueness of the record is a bundle of two fields?

CodePudding user response：

You need a composite primary-key. You also don't need your AUTO_INCREMENT id column, so you can drop it.

Like so:

CREATE TABLE `Test` (
  `order_id`     int           NOT NULL,
  `position_id`  int           NOT NULL,
  `name`         varchar(255)  NOT NULL COLLATE utf8mb4_unicode_ci,
  `price`        decimal(10,2) NOT NULL,

  CONSTRAINT PK_Test PRIMARY KEY ( `order_id`, `position_id` )

) ENGINE=InnoDB

Then you can use INSERT ON DUPLICATE KEY UPDATE.