This is my table
Column A | Column B | Column C | Column D |
---|---|---|---|
Cell 1 | Cell 2 | Cell 1 | Cell 2 |
Cell 3 | Cell 4 | Cell 3 | Cell 4 |
Where Column A is the primary key and Column D is a TINYINT column. Column D contains values from 0 to 3 only. (0,1,2,3) I want to partition this table based on column D.
I tried this code to partition the table.
ALTER TABLE to_be_partitioned PARTITION BY HASH(Column D) PARTITIONS 4;
It says A PRIMARY KEY must include all columns in the table's partitioning function
How can I partition this table based on Column D values please???
I tried using KEY partition type and it also gives an error.
I'm expecting something like this.
P0 contains all records with column D value of 0
P1 contains all records with column D value of 1
P2 contains all records with column D value of 2
P3 contains all records with column D value of 3
CodePudding user response:
DEMO
Partitioning with PK provided by additional table and a trigger.
The table which must be partitioned - no PK.
CREATE TABLE main (
colA INT NOT NULL, -- should be AI PK
colB INT,
colC TINYINT CHECK (colC BETWEEN 0 AND 3)
)
PARTITION BY LIST (colC) (
PARTITION zero VALUES IN (0),
PARTITION one VALUES IN (1),
PARTITION two VALUES IN (2),
PARTITION three VALUES IN (3)
);
Additional table which will be used for AI PK generation.
CREATE TABLE main_ai_pk (
colA INT AUTO_INCREMENT PRIMARY KEY
);
Trigger which will generate AI PK. If explicit value for colA
is provided then it will be overrided.
CREATE TRIGGER tr_bi_main_set_pk
BEFORE INSERT ON main
FOR EACH ROW
BEGIN
INSERT INTO main_ai_pk VALUES (DEFAULT); -- generate new AI value
SET NEW.colA = LAST_INSERT_ID(); -- assign it to "PK" in main table
DELETE FROM main_ai_pk WHERE colA < NEW.colA; -- clear excess rows
END
Some inserts. In 2nd INSERT the value for colA
which is provided explicitly is overrided.
INSERT INTO main (colB, colC) VALUES (11,1), (22,2), (111,1);
INSERT INTO main VALUES (NULL,33,3), (3333,333,3);
Look at final data state.
SELECT * FROM main ORDER BY colA;
SELECT * FROM main_ai_pk;
SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'main'
ORDER BY PARTITION_ORDINAL_POSITION;
colA | colB | colC |
---|---|---|
1 | 11 | 1 |
2 | 22 | 2 |
3 | 111 | 1 |
4 | 33 | 3 |
5 | 333 | 3 |
colA |
---|
5 |
PARTITION_NAME | TABLE_ROWS |
---|---|
zero | 0 |
one | 2 |
two | 1 |
three | 2 |
CodePudding user response:
- As Akina suggested, partitioning by LIST makes for sense for this case.
- A PRIMARY KEY must include all columns in the table's partitioning function. Your primary key needs to be either (Column A, Column D) or (Column D, Column A), or you'll get
SQL Error [1503] [HY000]
.
create table to_be_partitioned (
col_a int,
col_b int,
col_c int,
col_d int,
primary key (col_a, col_d))
partition by list (col_d) (
partition p0 values in (0),
partition p1 values in (1),
partition p2 values in (2),
partition p3 values in (3)
);
or
create table to_be_partitioned (
col_a int,
col_b int,
col_c int,
col_d int,
primary key (col_d, col_a))
partition by list (col_d) (
partition p0 values in (0),
partition p1 values in (1),
partition p2 values in (2),
partition p3 values in (3)
);
EDIT:
- If
col_a
is an auto_increment column,col_a
alone is already unique. Thus (col_a
,col_d
) is also unique. - If the purpose of partitioning is to evenly distribute data. The DDL below may works for you:
-- answer per comment
create table to_be_partitioned (
col_a int auto_increment,
col_b int,
col_c int,
col_d int,
primary key (col_a, col_d))
partition by list (col_d) (
partition p0 values in (0),
partition p1 values in (1),
partition p2 values in (2),
partition p3 values in (3)
);
insert into to_be_partitioned (col_b, col_c, col_d) values (1, 1, last_insert_id() mod 4);
insert into to_be_partitioned (col_b, col_c, col_d) values (2, 1, last_insert_id() mod 4);
insert into to_be_partitioned (col_b, col_c, col_d) values (3, 1, last_insert_id() mod 4);
insert into to_be_partitioned (col_b, col_c, col_d) values (4, 1, last_insert_id() mod 4);
insert into to_be_partitioned (col_b, col_c, col_d) values (5, 1, last_insert_id() mod 4);
insert into to_be_partitioned (col_b, col_c, col_d) values (6, 1, last_insert_id() mod 4);
insert into to_be_partitioned (col_b, col_c, col_d) values (7, 1, last_insert_id() mod 4);
select * from to_be_partitioned order by col_a;
col_a|col_b|col_c|col_d|
----- ----- ----- -----
1| 1| 1| 1|
2| 2| 1| 1|
3| 3| 1| 2|
4| 4| 1| 3|
5| 5| 1| 0|
6| 6| 1| 1|
7| 7| 1| 2|
-- from a partition p2
select * from to_be_partitioned partition(p2);
col_a|col_b|col_c|col_d|
----- ----- ----- -----
3| 3| 1| 2|
7| 7| 1| 2|