github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/table_partition.test (about) 1 CREATE TABLE tp1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY(col3) PARTITIONS 4; 2 show create table tp1; 3 CREATE TABLE tp2 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY(col3); 4 show create table tp2; 5 CREATE TABLE tp3 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR KEY(col3) PARTITIONS 5; 6 show create table tp3; 7 CREATE TABLE tp4 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY ALGORITHM = 1 (col3); 8 show create table tp4; 9 CREATE TABLE tp5 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR KEY ALGORITHM = 1 (col3) PARTITIONS 5; 10 show create table tp5; 11 CREATE TABLE tp6 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY(col1, col2) PARTITIONS 4; 12 show create table tp6; 13 CREATE TABLE tp7 (col1 INT NOT NULL PRIMARY KEY, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL) PARTITION BY KEY(col1) PARTITIONS 4; 14 show create table tp7; 15 CREATE TABLE tp8 (col1 INT, col2 CHAR(5)) PARTITION BY HASH(col1); 16 show create table tp8; 17 CREATE TABLE tp9 (col1 INT, col2 CHAR(5)) PARTITION BY HASH(col1) PARTITIONS 4; 18 show create table tp9; 19 CREATE TABLE tp10 (col1 INT, col2 CHAR(5), col3 DATETIME) PARTITION BY HASH (YEAR(col3)); 20 show create table tp10; 21 CREATE TABLE tp11 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR HASH( YEAR(col3)) PARTITIONS 6; 22 show create table tp11; 23 CREATE TABLE tp12 (col1 INT NOT NULL PRIMARY KEY, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL) PARTITION BY HASH(col1) PARTITIONS 4; 24 show create table tp12; 25 CREATE TABLE tp13 ( 26 id INT NOT NULL PRIMARY KEY, 27 fname VARCHAR(30), 28 lname VARCHAR(30), 29 hired DATE NOT NULL DEFAULT '1970-01-01', 30 separated DATE NOT NULL DEFAULT '9999-12-31', 31 job_code INT NOT NULL, 32 store_id INT NOT NULL 33 ) 34 PARTITION BY RANGE (id) ( 35 PARTITION p0 VALUES LESS THAN (6), 36 PARTITION p1 VALUES LESS THAN (11), 37 PARTITION p2 VALUES LESS THAN (16), 38 PARTITION p3 VALUES LESS THAN (21) 39 ); 40 show create table tp13; 41 CREATE TABLE tp14 ( 42 id INT NOT NULL, 43 fname VARCHAR(30), 44 lname VARCHAR(30), 45 hired DATE NOT NULL DEFAULT '1970-01-01', 46 separated DATE NOT NULL DEFAULT '9999-12-31', 47 job_code INT, 48 store_id INT 49 ) 50 PARTITION BY RANGE ( YEAR(separated) ) ( 51 PARTITION p0 VALUES LESS THAN (1991), 52 PARTITION p1 VALUES LESS THAN (1996), 53 PARTITION p2 VALUES LESS THAN (2001), 54 PARTITION p3 VALUES LESS THAN MAXVALUE 55 ); 56 show create table tp14; 57 CREATE TABLE tp15 ( 58 a INT NOT NULL, 59 b INT NOT NULL 60 ) 61 PARTITION BY RANGE COLUMNS(a,b) PARTITIONS 4 ( 62 PARTITION p0 VALUES LESS THAN (10,5), 63 PARTITION p1 VALUES LESS THAN (20,10), 64 PARTITION p2 VALUES LESS THAN (50,20), 65 PARTITION p3 VALUES LESS THAN (65,30) 66 ); 67 show create table tp15; 68 CREATE TABLE tp16 ( 69 id INT PRIMARY KEY, 70 name VARCHAR(35), 71 age INT unsigned 72 ) 73 PARTITION BY LIST (id) ( 74 PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21), 75 PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22), 76 PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23), 77 PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24) 78 ); 79 show create table tp16; 80 CREATE TABLE tp17 ( 81 id INT, 82 name VARCHAR(35), 83 age INT unsigned 84 ) 85 PARTITION BY LIST (id) ( 86 PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21), 87 PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22), 88 PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23), 89 PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24) 90 ); 91 show create table tp17; 92 CREATE TABLE tp18 ( 93 a INT NULL, 94 b INT NULL 95 ) 96 PARTITION BY LIST COLUMNS(a,b) ( 97 PARTITION p0 VALUES IN( (0,0), (NULL,NULL) ), 98 PARTITION p1 VALUES IN( (0,1), (0,2), (0,3), (1,1), (1,2) ), 99 PARTITION p2 VALUES IN( (1,0), (2,0), (2,1), (3,0), (3,1) ), 100 PARTITION p3 VALUES IN( (1,3), (2,2), (2,3), (3,2), (3,3) ) 101 ); 102 show create table tp18; 103 104 CREATE TABLE tp19 ( 105 col1 INT NOT NULL, 106 col2 DATE NOT NULL, 107 col3 INT NOT NULL UNIQUE, 108 col4 INT NOT NULL 109 ) 110 PARTITION BY KEY(col3) 111 PARTITIONS 4; 112 show create table tp19; 113 114 CREATE TABLE tp20 ( 115 col1 INT NOT NULL, 116 col2 DATE NOT NULL, 117 col3 INT NOT NULL UNIQUE, 118 col4 INT NOT NULL 119 ) 120 PARTITION BY HASH(col3) 121 PARTITIONS 4; 122 show create table tp20; 123 124 drop table tp1; 125 drop table tp2; 126 drop table tp3; 127 drop table tp4; 128 drop table tp5; 129 drop table tp6; 130 drop table tp7; 131 drop table tp8; 132 drop table tp9; 133 drop table tp10; 134 drop table tp11; 135 drop table tp12; 136 drop table tp13; 137 drop table tp14; 138 drop table tp15; 139 drop table tp16; 140 drop table tp17; 141 drop table tp18; 142 drop table tp19; 143 drop table tp20; 144 drop database if exists test; 145 create database test; 146 use test; 147 148 /*!40101 SET @saved_cs_client = @@character_set_client */; 149 /*!50503 SET character_set_client = utf8mb4 */; 150 CREATE TABLE test01 ( 151 `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'pk', 152 `pk_id` bigint NOT NULL COMMENT 'pk_id', 153 `config_id` bigint NOT NULL COMMENT 'config_id', 154 `trace_id` varchar(255) COLLATE utf8mb4_general_ci NOT NULL, 155 `type` varchar(8) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'message_type', 156 `data` varchar(1024) COLLATE utf8mb4_general_ci NOT NULL, 157 `times` tinyint NOT NULL, 158 `status` tinyint NOT NULL, 159 `push_time` varchar(255) COLLATE utf8mb4_general_ci NOT NULL, 160 `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, 161 `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, 162 PRIMARY KEY (`id`,`create_time`) USING BTREE, 163 UNIQUE KEY (`trace_id`,`create_time`) 164 ) ENGINE=InnoDB AUTO_INCREMENT=654 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='trace id' 165 /*!50500 PARTITION BY RANGE COLUMNS(create_time) 166 (PARTITION p20240115 VALUES LESS THAN ('2024-01-15 00:00:00') ENGINE = InnoDB, 167 PARTITION p20240116 VALUES LESS THAN ('2024-01-16 00:00:00') ENGINE = InnoDB, 168 PARTITION p20240117 VALUES LESS THAN ('2024-01-17 00:00:00') ENGINE = InnoDB, 169 PARTITION p20240118 VALUES LESS THAN ('2024-01-18 00:00:00') ENGINE = InnoDB, 170 PARTITION p20240119 VALUES LESS THAN ('2024-01-19 00:00:00') ENGINE = InnoDB, 171 PARTITION p20240120 VALUES LESS THAN ('2024-01-20 00:00:00') ENGINE = InnoDB, 172 PARTITION p20240121 VALUES LESS THAN ('2024-01-21 00:00:00') ENGINE = InnoDB, 173 PARTITION p20240122 VALUES LESS THAN ('2024-01-22 00:00:00') ENGINE = InnoDB, 174 PARTITION p20240123 VALUES LESS THAN ('2024-01-23 00:00:00') ENGINE = InnoDB, 175 PARTITION p20240124 VALUES LESS THAN ('2024-01-24 00:00:00') ENGINE = InnoDB, 176 PARTITION p20240125 VALUES LESS THAN ('2024-01-25 00:00:00') ENGINE = InnoDB) */; 177 /*!40101 SET character_set_client = @saved_cs_client */; 178 show create table test01; 179 drop table test01; 180 181 /*!40101 SET @saved_cs_client = @@character_set_client */; 182 /*!50503 SET character_set_client = utf8mb4 */; 183 CREATE TABLE test02 ( 184 `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'pk', 185 `pk_id` bigint NOT NULL COMMENT 'pk_id', 186 `config_id` bigint NOT NULL COMMENT 'config_id', 187 `trace_id` varchar(255) COLLATE utf8mb4_general_ci NOT NULL, 188 `type` varchar(8) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'message_type', 189 `data` varchar(1024) COLLATE utf8mb4_general_ci NOT NULL, 190 `times` tinyint NOT NULL, 191 `status` tinyint NOT NULL, 192 `push_time` varchar(255) COLLATE utf8mb4_general_ci NOT NULL, 193 `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, 194 `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, 195 PRIMARY KEY (`id`,`create_time`) USING BTREE, 196 UNIQUE KEY (`trace_id`,`create_time`) 197 ) ENGINE=InnoDB AUTO_INCREMENT=654 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='trace id' 198 PARTITION BY RANGE COLUMNS(create_time) 199 (PARTITION p20240115 VALUES LESS THAN ('2024-01-15 00:00:00') ENGINE = InnoDB, 200 PARTITION p20240116 VALUES LESS THAN ('2024-01-16 00:00:00') ENGINE = InnoDB, 201 PARTITION p20240117 VALUES LESS THAN ('2024-01-17 00:00:00') ENGINE = InnoDB, 202 PARTITION p20240118 VALUES LESS THAN ('2024-01-18 00:00:00') ENGINE = InnoDB, 203 PARTITION p20240119 VALUES LESS THAN ('2024-01-19 00:00:00') ENGINE = InnoDB, 204 PARTITION p20240120 VALUES LESS THAN ('2024-01-20 00:00:00') ENGINE = InnoDB, 205 PARTITION p20240121 VALUES LESS THAN ('2024-01-21 00:00:00') ENGINE = InnoDB, 206 PARTITION p20240122 VALUES LESS THAN ('2024-01-22 00:00:00') ENGINE = InnoDB, 207 PARTITION p20240123 VALUES LESS THAN ('2024-01-23 00:00:00') ENGINE = InnoDB, 208 PARTITION p20240124 VALUES LESS THAN ('2024-01-24 00:00:00') ENGINE = InnoDB, 209 PARTITION p20240125 VALUES LESS THAN ('2024-01-25 00:00:00') ENGINE = InnoDB); 210 /*!40101 SET character_set_client = @saved_cs_client */; 211 show create table test02; 212 drop table test02; 213 214 drop database test;