如何创建分区mysql 8

Question

我正在尝试创建一个带有分区的表，方法如下

create table transaction_demo
(
    trx_id bigint not null primary key,
    created_datetime  datetime
) partition by range (YEAR(created_datetime)) (
    partition p1 values less than (2019),
    partition p2 values less than (2020),
    partition p3 values less than (2021)
    );

但它不起作用。我收到以下错误

[2019-08-02 15:08:43] [HY000][1503] A PRIMARY KEY must include all columns in the table's partitioning function
[2019-08-02 15:08:43] [HY000][1503] A PRIMARY KEY must include all columns in the table's partitioning function

如何解决这个问题？

Answer 1

然后为将成为分区一部分的每一列定义主键。

CREATE TABLE transaction_demo (
        trx_id int NOT NULL,
        created_datetime datetime NOT NULL,
        other_column_1 int NULL,
        other_column_2 varchar(222) NULL,
        PRIMARY KEY(trx_id,created_datetime)
    )
        PARTITION BY RANGE( YEAR(created_datetime) )(
        PARTITION p1 VALUES LESS THAN (2019),
        PARTITION p2 VALUES LESS THAN (2020),
        PARTITION p3 VALUES LESS THAN (2021)
    );