1
SET @@session.storage_engine = 'InnoDB';
2
drop table if exists t1;
3
# Case 1. Partitioning by RANGE based on a non-stored virtual column.
8
PARTITION BY RANGE( b ) (
9
PARTITION p0 VALUES LESS THAN (2006),
10
PARTITION p2 VALUES LESS THAN (2008)
12
insert into t1 values ('2006-01-01',default);
13
insert into t1 values ('2007-01-01',default);
14
insert into t1 values ('2005-01-01',default);
20
select partition_name,table_rows,data_length from information_schema.partitions where table_name = 't1';
21
partition_name table_rows data_length
24
# Modify the expression of virtual column b
25
ALTER TABLE t1 modify b int as (year(a)-1);
31
select partition_name,table_rows,data_length from information_schema.partitions where table_name = 't1';
32
partition_name table_rows data_length
36
# Case 2. Partitioning by LIST based on a stored virtual column.
37
CREATE TABLE t1 (a int, b int as (a % 3 ) persistent)
38
PARTITION BY LIST (a+1)
39
(PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2));
40
insert into t1 values (1,default);
41
select partition_name,table_rows,data_length from information_schema.partitions where table_name = 't1';
42
partition_name table_rows data_length
52
# Case 3. Partitioning by HASH based on a non-stored virtual column.
57
PARTITION BY HASH( b % 3 ) PARTITIONS 3;
58
insert into t1 values ('2005-01-01',default);
59
insert into t1 values ('2006-01-01',default);
64
select partition_name,table_rows,data_length from information_schema.partitions where table_name = 't1';
65
partition_name table_rows data_length
69
# Modify the expression of virtual column b
70
ALTER TABLE t1 modify b int as (year(a)-1);
75
select partition_name,table_rows,data_length from information_schema.partitions where table_name = 't1';
76
partition_name table_rows data_length