CODEWITHSUNDEEP
mysqlpartitioningdatabase-partitioning
Lofrank
Lofrank

Reputation: 113

Mysql - how to keep unique constraint while partitioning by RANGE (timestamp)?

I have one table, want to partition by RANGE (created_at timestamp), so can delete old data easily (by drop partition).

CREATE TABLE `orders` (
  `order_id` NVARCHAR(64) NOT NULL,
  `amount` INTEGER NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `modified_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE dropship.orders
PARTITION BY RANGE (UNIX_TIMESTAMP(created_at)) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-03-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-05-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-06-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2019-07-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN (MAXVALUE)
);

This table only has two usages: get by order_id, or update by order_id.

select * from orders where order_id = '123';
update orders set amount = 10 where order_id = '123';

Due to the limitation of Mysql partitioning, I cannot add an unique key for order_id since will use created_at field for partitioning.

All columns used in the table's partitioning expression must be part of every unique key that the table may have, including any primary key.

Question:

Any way to make order_id unique in this table please?

I have thought about partitioning by order_id, but it's hard to delete old data in that way.

Any suggestion is welcome. (For example may be you have better design for this table).

Upvotes: 0

Views: 311

Answers (1)

Rick James
Rick James

Reputation: 142503

BEGIN;
SELECT 1 FROM orders WHERE order_id = 234  FOR UPDATE;
if row exists, you have a dup error.
INSERT INTO orders ... order_id = 234;
COMMIT;

But, as Raymond points out, you may as well drop PARTITIONing and make the column the PRIMARY KEY. This would make all the stated operations slightly faster.

Upvotes: 2

Related Questions