0

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?

daniil sidorov
  • 61
  • 5
  • "Since there is a lot of data, it will take quite a long time to check each record to make it insert or update." - **whoa, slow down there** - the number of records in a table **should not** affect insert or update performance. If it does, then something else is wrong in your database. – Dai Sep 24 '21 at 21:31
  • "The uniqueness of the record can only be in a combination of two fields order_id and position_id." - yes, it means you need a **composite primary key**. That's all you need. – Dai Sep 24 '21 at 21:33
  • https://stackoverflow.com/questions/1110349/how-can-i-define-a-composite-primary-key-in-sql – Dai Sep 24 '21 at 21:33

1 Answers1

0

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.

Dai
  • 141,631
  • 28
  • 261
  • 374
  • Thanks a lot! Is it possible to save the ID field and add a composite unique key? – daniil sidorov Sep 25 '21 at 08:17
  • @daniilsidorov That would be defeating the point of having a composite natural key. Why do you want a serial/identity column so badly? – Dai Sep 25 '21 at 17:27