github.com/pingcap/tiflow@v0.0.0-20240520035814-5bf52d54e205/tests/integration_tests/partition_table/data/prepare.sql (about)

     1  drop database if exists `partition_table`;
     2  drop database if exists `partition_table2`;
     3  create database `partition_table`;
     4  use `partition_table`;
     5  
     6  create table t (a int, primary key (a)) partition by hash(a) partitions 5;
     7  insert into t values (1),(2),(3),(4),(5),(6);
     8  insert into t values (7),(8),(9);
     9  alter table t truncate partition p3;
    10  update t set a=a+10 where a=2;
    11  
    12  
    13  create table t1 (a int primary key) PARTITION BY RANGE ( a ) ( PARTITION p0 VALUES LESS THAN (6),PARTITION p1 VALUES LESS THAN (11),PARTITION p2 VALUES LESS THAN (21));
    14  insert into t1 values (1),(2),(3),(4),(5),(6);
    15  insert into t1 values (7),(8),(9);
    16  insert into t1 values (11),(12),(20);
    17  alter table t1 add partition (partition p3 values less than (30), partition p4 values less than (40));
    18  insert into t1 values (25),(29),(35); /*these values in p3,p4*/
    19  alter table t1 truncate partition p0;
    20  alter table t1 drop partition p1;
    21  insert into t1 values (7),(8),(9);
    22  update t1 set a=a+10 where a=9;
    23  
    24  /* Remove partitioning + add partitioning back again */
    25  alter table t remove partitioning;
    26  insert into t values (20),(21),(22),(23),(24),(25);
    27  alter table t partition by hash (a) partitions 5;
    28  insert into t values (30),(31),(32),(33),(34),(35);
    29  
    30  /* exchange partition case 1: source table and target table in same database */
    31  create table t2 (a int primary key);
    32  ALTER TABLE t1 EXCHANGE PARTITION p3 WITH TABLE t2;
    33  insert into t2 values (100),(101),(102),(103),(104),(105); /*these values will be replicated to in downstream t2*/
    34  insert into t1 values (25),(29); /*these values will be replicated to in downstream t1.p3*/
    35  
    36  /* exchange partition ccase 2: source table and target table in different database */
    37  create database `partition_table2`;
    38  create table partition_table2.t2 (a int primary key);
    39  ALTER TABLE t1 EXCHANGE PARTITION p3 WITH TABLE partition_table2.t2;
    40  insert into partition_table2.t2 values (1002),(1012),(1022),(1032),(1042),(1052); /*these values will be replicated to in downstream t2*/
    41  insert into t1 values (21),(28); /*these values will be replicated to in downstream t1.p3*/
    42  
    43  ALTER TABLE t1 REORGANIZE PARTITION p0,p2 INTO (PARTITION p0 VALUES LESS THAN (5), PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (21));
    44  insert into t1 values (-1),(6),(13);
    45  update t1 set a=a-22 where a=20;
    46  delete from t1 where a = 5;
    47  ALTER TABLE t1 REORGANIZE PARTITION p2,p3,p4 INTO (PARTITION p2 VALUES LESS THAN (20), PARTITION p3 VALUES LESS THAN (26), PARTITION p4 VALUES LESS THAN (35), PARTITION pMax VALUES LESS THAN (MAXVALUE));
    48  insert into t1 values (-3),(5),(14),(22),(30),(100);
    49  update t1 set a=a-16 where a=12;
    50  delete from t1 where a = 29;
    51  
    52  /* Change partitioning to key based and then back to range */
    53  alter table t1 partition by key(a) partitions 7;
    54  insert into t1 values (-2001),(2001),(2002),(-2002),(-2003),(2003),(-2004),(2004),(-2005),(2005),(2006),(-2006),(2007),(-2007);
    55  ALTER TABLE t1 partition by range(a) (partition p0 values less than (5), PARTITION p2 VALUES LESS THAN (20), PARTITION p3 VALUES LESS THAN (26), PARTITION p4 VALUES LESS THAN (35), PARTITION pMax VALUES LESS THAN (MAXVALUE));
    56  
    57  create table finish_mark (a int primary key);