github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/hash_key_partition.sql (about) 1 -- key partition: char,varchar ; pk/not pk 2 create table p_table_01(col1 int,col2 varchar(25),col3 decimal(6,2))partition by key(col2)partitions 4; 3 insert into p_table_01 values (1,'mod',78.9),(2,'proto',0.34),(3,'mod',6.5),(4,'mode',9.0),(5,'make',662.9),(6,'io',88.92); 4 select * from `%!%p0%!%p_table_01`; 5 select * from `%!%p1%!%p_table_01`; 6 select * from `%!%p2%!%p_table_01`; 7 select * from `%!%p3%!%p_table_01`; 8 show create table p_table_01; 9 create table p_table_02(col1 int,col2 char(25),col3 decimal(6,3),primary key(col1,col2))partition by key(col2)partitions 2; 10 insert into p_table_02 values (1,'mod',78.9),(2,'proto',0.34),(3,'mod',6.5),(4,'mode',9.0),(5,'make',662.9),(6,'io',88.92); 11 select * from `%!%p0%!%p_table_02`; 12 select * from `%!%p1%!%p_table_02`; 13 14 -- key partition: char include null values 15 create table p_table_03(col1 int,col2 char(25),col3 decimal(4,2),primary key(col1,col2))partition by key(col2)partitions 4; 16 insert into p_table_03 values (1,'',78.9),(2,'proto',0.34),(3,'',6.5),(4,'mode',9.0),(5,'make',62.9),(6,'io',88.92); 17 select * from `%!%p0%!%p_table_03`; 18 select * from `%!%p1%!%p_table_03`; 19 select * from `%!%p2%!%p_table_03`; 20 select * from `%!%p3%!%p_table_03`; 21 show create table p_table_03; 22 23 -- key partition: varchar ; key() and duplicate key 24 create table p_table_04(col1 int,col2 char(25) primary key,col3 decimal(4,2))partition by key()partitions 8; 25 insert into p_table_04 values (1,'mod',78.9),(2,'proto',0.34),(3,'mod',6.5),(4,'mode',9.0),(5,'make',62.9),(6,'io',88.92); 26 insert into p_table_04 values (1,'mod',78.9),(2,'proto',0.34),(3,'rep',6.5),(4,'test',9.0),(5,'make',62.9),(6,'io',88.92); 27 select * from `%!%p0%!%p_table_04`; 28 select * from `%!%p1%!%p_table_04`; 29 select * from `%!%p2%!%p_table_04`; 30 select * from `%!%p3%!%p_table_04`; 31 select * from `%!%p4%!%p_table_04`; 32 select * from `%!%p5%!%p_table_04`; 33 select * from `%!%p6%!%p_table_04`; 34 select * from `%!%p7%!%p_table_04`; 35 create table p_table_05(col1 int,col2 char(25),col3 decimal(4,2),unique key k2(col2))partition by key()partitions 6; 36 37 -- key partition: char, unique key 38 create table p_table_06(col1 int,col2 char(25),col3 decimal(6,3),unique key k2(col1,col2))partition by key(col2)partitions 4; 39 insert into p_table_06 values (1,'mod',78.9),(2,'proto',0.34),(3,'mod',6.5),(4,'mode',9.0),(5,'make',662.9),(6,'io',88.92); 40 select * from `%!%p0%!%p_table_06`; 41 select * from `%!%p1%!%p_table_06`; 42 select * from `%!%p2%!%p_table_06`; 43 select * from `%!%p3%!%p_table_06`; 44 45 -- abnormal test 46 create table p_table_07(col1 int,col2 char(25),col3 decimal(4,2),unique key k2(col1,col2))partition by key()partitions 8; 47 create table p_table_07(col1 int,col2 char(25),col3 decimal(4,2))partition by key()partitions 8; 48 create table p_table_07(col1 int,col2 char(25),col3 decimal(4,2),unique key k2(col1))partition by key(col2)partitions 8; 49 create table p_table_07(col1 int unsigned,col2 date, col3 varchar(25),primary key(col1,col2) ,unique key k1(col3))partition by key(col1)partitions 4; 50 51 --key partition: tinyint,tinyint unsigned,smallint,smallint unsigned 52 create table p_table_08(col1 tinyint,col2 varchar(25),col3 decimal(6,2))partition by key(col1)partitions 4; 53 insert into p_table_08 values (34,'mod',78.9),(34,'proto',0.34),(-80,'mod',6.5),(-80,'mode',9.0),(59,'make',62.9),(59,'io',88.92); 54 select * from `%!%p0%!%p_table_08`; 55 select * from `%!%p1%!%p_table_08`; 56 select * from `%!%p2%!%p_table_08`; 57 select * from `%!%p3%!%p_table_08`; 58 drop table p_table_08; 59 create table p_table_08(col1 tinyint unsigned,col2 varchar(25),col3 decimal(6,2))partition by key(col1)partitions 4; 60 insert into p_table_08 values (34,'mod',78.9),(34,'proto',0.34),(80,'mod',6.5),(80,'mode',9.0),(59,'make',62.9),(59,'io',88.92); 61 select * from `%!%p0%!%p_table_08`; 62 select * from `%!%p1%!%p_table_08`; 63 select * from `%!%p2%!%p_table_08`; 64 select * from `%!%p3%!%p_table_08`; 65 drop table p_table_08; 66 create table p_table_08(col1 smallint ,col2 varchar(25),col3 decimal(6,2))partition by key(col1)partitions 4; 67 insert into p_table_08 values (34,'mod',78.9),(34,'proto',0.34),(80,'mod',6.5),(80,'mode',9.0),(59,'make',62.9),(59,'io',88.92); 68 select * from `%!%p0%!%p_table_08`; 69 select * from `%!%p1%!%p_table_08`; 70 select * from `%!%p2%!%p_table_08`; 71 select * from `%!%p3%!%p_table_08`; 72 drop table p_table_08; 73 create table p_table_08(col1 smallint unsigned ,col2 varchar(25),col3 decimal(6,2))partition by key(col1)partitions 4; 74 insert into p_table_08 values (34,'mod',78.9),(34,'proto',0.34),(80,'mod',6.5),(80,'mode',9.0),(59,'make',62.9),(59,'io',88.92); 75 select * from `%!%p0%!%p_table_08`; 76 select * from `%!%p1%!%p_table_08`; 77 select * from `%!%p2%!%p_table_08`; 78 select * from `%!%p3%!%p_table_08`; 79 80 --key partition: int, not pk, key more columns 81 create table p_table_09(col1 int,col2 date, col3 varchar(25))partition by key(col1,col2)partitions 4; 82 insert into p_table_09 values (72,'1999-09-29','res1'),(60,'1999-10-01','opt1'),(72,'1999-09-29','res2'),(60,'1999-10-01','opt2'), (200,'1999-10-29','oop1'),(200,'1999-10-29','oop1'); 83 select * from `%!%p0%!%p_table_09`; 84 select * from `%!%p1%!%p_table_09`; 85 select * from `%!%p2%!%p_table_09`; 86 select * from `%!%p3%!%p_table_09`; 87 insert into p_table_09 values (900,'1999-09-29','res1'),(900,'1999-09-29','opt1'),(1000,'1999-10-01','res2'),(1000,'1999-10-01','opt2'), (200,'1999-10-29','oop1'),(200,'1999-10-29','oop1'); 88 select * from `%!%p0%!%p_table_09`; 89 select * from `%!%p1%!%p_table_09`; 90 select * from `%!%p2%!%p_table_09`; 91 select * from `%!%p3%!%p_table_09`; 92 show create table p_table_09; 93 --key partition: int unsigned,pk/unique key 94 create table p_table_10(col1 int unsigned,col2 date, col3 varchar(25),primary key(col1,col2))partition by key(col1)partitions 4; 95 insert into p_table_10 values (72,'1999-09-29','res1'),(60,'1999-10-01','opt1'),(72,'1999-10-02','res2'),(72,'1999-10-03','opt2'), (60,'1999-10-29','oop1'),(206,'1999-10-30','oop1'); 96 select * from `%!%p0%!%p_table_10`; 97 select * from `%!%p1%!%p_table_10`; 98 select * from `%!%p2%!%p_table_10`; 99 select * from `%!%p3%!%p_table_10`; 100 select * from p_table_10 where col1<70; 101 create table p_table_11(col1 int unsigned,col2 date, col3 varchar(25),unique key k1(col1,col2))partition by key(col1)partitions 4; 102 insert into p_table_11 values (72,'1999-09-29','res1'),(60,'1999-10-01','opt1'),(72,'1999-10-02','res2'),(72,'1999-10-03','opt2'), (60,'1999-10-29','oop1'),(206,'1999-10-30','oop1'); 103 select * from `%!%p0%!%p_table_11`; 104 select * from `%!%p1%!%p_table_11`; 105 select * from `%!%p2%!%p_table_11`; 106 select * from `%!%p3%!%p_table_11`; 107 select * from p_table_11; 108 --key partition: int unsigned,null value 109 create table p_table_12(col1 int unsigned,col2 date, col3 varchar(25),unique key k1(col1,col2))partition by key(col1)partitions 4; 110 insert into p_table_12 values (72,'1999-09-29','res1'),(NULL,'1999-10-01','opt1'),(72,'1999-10-02','res2'),(72,'1999-10-03','opt2'), (NULL,'1999-10-29','oop1'),(206,'1999-10-30','oop1'); 111 select * from `%!%p0%!%p_table_12`; 112 select * from `%!%p1%!%p_table_12`; 113 select * from `%!%p2%!%p_table_12`; 114 select * from `%!%p3%!%p_table_12`; 115 select * from p_table_12 where col2='1999-09-29'; 116 117 -- key partition: int ; key() 118 create table p_table_13(col1 int primary key auto_increment,col2 char(25),col3 decimal(4,2))partition by key()partitions 2; 119 insert into p_table_13(col2,col3) values ('mod',78.9),('proto',0.34),('mod',6.5),('mode',9.0),('make',62.9),('io',88.92); 120 select * from `%!%p0%!%p_table_13`; 121 select * from `%!%p1%!%p_table_13`; 122 123 -- key partition: bigint,more columns 124 create table p_table_14(col1 bigint,col2 date,col3 varchar(25),col4 decimal(6,4))partition by key(col1,col2,col3)partitions 8; 125 insert into p_table_14 values (1000,'1999-09-29','res1',0.12),(6000,'1999-10-01','opt1',0.89),(729,'1999-10-02','res2',0.32),(6000,'1999-10-01','opt1',0.64), (6000,'1999-10-01','opt1',0.55),(206,'1999-10-30','oop1',0.87); 126 insert into p_table_14 values (1000,'1999-09-29','res1',0.12),(6000,'1999-10-01','opt1',0.89),(1000,'1999-09-29','res1',0.32),(206,'1999-10-30','oop1',0.64), (1000,'1999-09-29','res1',0.55),(206,'1999-10-30','oop1',0.87); 127 select * from `%!%p0%!%p_table_14`; 128 select * from `%!%p1%!%p_table_14`; 129 select * from `%!%p2%!%p_table_14`; 130 select * from `%!%p3%!%p_table_14`; 131 select * from `%!%p4%!%p_table_14`; 132 select * from `%!%p5%!%p_table_14`; 133 select * from `%!%p6%!%p_table_14`; 134 select * from `%!%p7%!%p_table_14`; 135 select * from p_table_14 where col3 in ('opt1','res1'); 136 insert into p_table_14 values (30,'1970-01-01','use',5.7), (30,'1970-01-01','kkk',9.8); 137 select * from `%!%p0%!%p_table_14`; 138 select * from `%!%p1%!%p_table_14`; 139 select * from `%!%p2%!%p_table_14`; 140 select * from `%!%p3%!%p_table_14`; 141 select * from `%!%p4%!%p_table_14`; 142 select * from `%!%p5%!%p_table_14`; 143 select * from `%!%p6%!%p_table_14`; 144 select * from `%!%p7%!%p_table_14`; 145 update p_table_14 set col2='1999-01-01' where col2<'1999-10-02'; 146 select * from p_table_14; 147 select * from `%!%p0%!%p_table_14`; 148 select * from `%!%p1%!%p_table_14`; 149 select * from `%!%p2%!%p_table_14`; 150 select * from `%!%p3%!%p_table_14`; 151 select * from `%!%p4%!%p_table_14`; 152 select * from `%!%p5%!%p_table_14`; 153 select * from `%!%p6%!%p_table_14`; 154 select * from `%!%p7%!%p_table_14`; 155 update p_table_14 set col4=0.999 where col1=1000; 156 select * from p_table_14 where col1>1000 ; 157 delete from p_table_14 where col3 in ('res1','res2'); 158 select * from p_table_14; 159 select * from `%!%p0%!%p_table_14`; 160 select * from `%!%p1%!%p_table_14`; 161 select * from `%!%p2%!%p_table_14`; 162 select * from `%!%p3%!%p_table_14`; 163 select * from `%!%p4%!%p_table_14`; 164 select * from `%!%p5%!%p_table_14`; 165 select * from `%!%p6%!%p_table_14`; 166 select * from `%!%p7%!%p_table_14`; 167 truncate table p_table_14; 168 169 -- key partition: bigint 170 create table p_table_15(col1 bigint auto_increment,col2 date,col3 varchar(25),col4 decimal(6,4),primary key(col1,col2,col3))partition by key(col2)partitions 4; 171 insert into p_table_15(col2,col3,col4) values ('1999-09-29','res1',0.12),('1999-10-02','res2',0.32),('1999-10-01','opt1',0.64), ('1999-10-01','opt2',0.55),('1999-09-29','oop1',0.87); 172 insert into p_table_15(col2,col3,col4) values ('1999-09-29','res1',0.12),('1999-10-02','res2',0.32),('1999-10-01','opt1',0.64), ('1999-10-01','opt2',0.55),('1999-09-29','oop1',0.87); 173 select * from p_table_15; 174 select * from `%!%p0%!%p_table_15`; 175 select * from `%!%p1%!%p_table_15`; 176 select * from `%!%p2%!%p_table_15`; 177 select * from `%!%p3%!%p_table_15`; 178 truncate table p_table_15; 179 insert into p_table_15(col2,col3,col4) values ('1999-09-29','res1',0.12),('1999-10-02','res2',0.32),('1999-10-01','opt1',0.64), ('1999-10-01','opt2',0.55),('1999-09-29','oop1',0.87); 180 insert into p_table_15(col2,col3,col4) values ('1999-09-29','res1',0.12),('1999-10-02','res2',0.32),('1999-10-01','opt1',0.64), ('1999-10-01','opt2',0.55),('1999-09-29','oop1',0.87); 181 select * from p_table_15; 182 select * from `%!%p0%!%p_table_15`; 183 select * from `%!%p1%!%p_table_15`; 184 select * from `%!%p2%!%p_table_15`; 185 select * from `%!%p3%!%p_table_15`; 186 update p_table_15 set col1=100 where col2='1999-09-29'; 187 update p_table_15 set col2='2022-10-01' where col1>4; 188 select * from p_table_15; 189 select * from `%!%p0%!%p_table_15`; 190 select * from `%!%p1%!%p_table_15`; 191 select * from `%!%p2%!%p_table_15`; 192 select * from `%!%p3%!%p_table_15`; 193 delete from p_table_15 where col2='1999-09-29'; 194 select * from p_table_15; 195 select * from `%!%p0%!%p_table_15`; 196 select * from `%!%p1%!%p_table_15`; 197 select * from `%!%p2%!%p_table_15`; 198 select * from `%!%p3%!%p_table_15`; 199 delete from p_table_15; 200 select * from p_table_15; 201 select * from `%!%p0%!%p_table_15`; 202 select * from `%!%p1%!%p_table_15`; 203 select * from `%!%p2%!%p_table_15`; 204 select * from `%!%p3%!%p_table_15`; 205 206 -- abnormal test: duplicate value, out of range 207 create table p_table_16(col1 bigint,col2 date,col3 varchar(25),col4 decimal(6,4),primary key(col1,col2,col3))partition by key(col2)partitions 8; 208 insert into p_table_16 values (1000,'1999-09-29','res1',0.12),(6000,'1999-10-01','opt1',0.89),(729,'1999-10-02','res2',0.32),(6000,'1999-10-01','opt1',0.64), (6000,'1999-10-01','opt1',0.55),(206,'1999-10-30','oop1',0.87); 209 insert into p_table_16 values (1000,'0001-09-29','res1',0.12),(6000,'1999-10-11','opt1',0.89); 210 select * from p_table_16; 211 212 -- key partition: bigint unsigned 213 create table p_table_temp(col1 bigint unsigned auto_increment,col2 date,col3 varchar(25),col4 decimal(6,4),primary key(col1,col2,col3)); 214 insert into p_table_temp(col2,col3,col4) values ('1999-09-29','res1',0.12),('1999-10-02','res2',0.32),('1999-10-01','opt1',0.64), ('1999-10-01','opt2',0.55),('1999-09-29','oop1',0.87); 215 insert into p_table_temp(col2,col3,col4) values ('1999-09-29','res1',0.12),('1999-10-02','res2',0.32),('1999-10-01','opt1',0.64), ('1999-10-01','opt2',0.55),('1999-09-29','oop1',0.87); 216 create table p_table_17(col1 bigint unsigned auto_increment,col2 date,col3 varchar(25),col4 decimal(6,4),primary key(col1,col2,col3))partition by key(col2)partitions 4; 217 insert into p_table_17 select col1,col2,col3,col4 from p_table_temp; 218 select * from p_table_17; 219 select * from `%!%p0%!%p_table_17`; 220 select * from `%!%p1%!%p_table_17`; 221 select * from `%!%p2%!%p_table_17`; 222 select * from `%!%p3%!%p_table_17`; 223 224 -- key partition: decimal,float,double 225 create table p_table_18(col1 bigint,col2 varchar(25),col3 decimal(6,4))partition by key(col3)partitions 2; 226 insert into p_table_18 values(932,'rel',0.98),(76,'opp',8.94),(823,'var',0.98); 227 select * from p_table_18; 228 select * from `%!%p0%!%p_table_18`; 229 select * from `%!%p1%!%p_table_18`; 230 drop table p_table_18; 231 create table p_table_18(col1 bigint,col2 varchar(25),col3 float)partition by key(col3)partitions 2; 232 insert into p_table_18 values(932,'rel',0.98),(76,'opp',8.94),(823,'var',0.98); 233 select * from p_table_18; 234 select * from `%!%p0%!%p_table_18`; 235 select * from `%!%p1%!%p_table_18`; 236 drop table p_table_18; 237 create table p_table_18(col1 bigint,col2 varchar(25),col3 double)partition by key(col3)partitions 2; 238 insert into p_table_18 values(932,'rel',0.98),(76,'opp',8.94),(823,'var',0.98); 239 select * from p_table_18; 240 select * from `%!%p0%!%p_table_18`; 241 select * from `%!%p1%!%p_table_18`; 242 243 -- key partition: date,datetime,timestamp 244 create table p_table_19(col1 int,col2 date,col3 varchar(25))partition by key(col2)partitions 6; 245 load data infile '$resources/load_data/key_partition_data.csv' into table p_table_19 fields terminated by ','; 246 select * from p_table_19 where col1>1500; 247 select * from `%!%p0%!%p_table_19`; 248 select * from `%!%p1%!%p_table_19`; 249 select * from `%!%p2%!%p_table_19`; 250 select * from `%!%p3%!%p_table_19`; 251 select * from `%!%p4%!%p_table_19`; 252 select * from `%!%p5%!%p_table_19`; 253 select count(*) from p_table_19; 254 drop table p_table_19; 255 create table p_table_19(col1 int auto_increment,col2 date,col3 varchar(25),primary key(col1,col2))partition by key(col2)partitions 3; 256 load data infile '$resources/load_data/key_partition_data.csv' into table p_table_19 fields terminated by ','; 257 select * from p_table_19 where col1>1500; 258 select * from `%!%p0%!%p_table_19`; 259 select * from `%!%p1%!%p_table_19`; 260 select * from `%!%p2%!%p_table_19`; 261 drop table p_table_19; 262 create table p_table_19(col1 int auto_increment,col2 date,col3 varchar(25),primary key(col1,col2,col3))partition by key()partitions 3; 263 insert into p_table_19(col2,col3) values('2023-01-01','a'),('2023-01-02','b'),('2023-01-03','c'),('2023-01-04','d'); 264 select * from p_table_19; 265 select * from `%!%p0%!%p_table_19`; 266 select * from `%!%p1%!%p_table_19`; 267 select * from `%!%p2%!%p_table_19`; 268 269 -- key partition: binary,varbinary,blob 270 create table p_table_001(col1 int,col2 binary(50))partition by key(col2)partitions 4; 271 insert into p_table_001 values (12,'var1'),(56,'sstt'),(78,'var2'),(90,'lop'); 272 select * from `%!%p0%!%p_table_001`; 273 select * from `%!%p1%!%p_table_001`; 274 select * from `%!%p2%!%p_table_001`; 275 select * from `%!%p3%!%p_table_001`; 276 create table p_table_002(col1 int,col2 varbinary(50))partition by key(col2)partitions 4; 277 insert into p_table_002 values (12,'var1'),(56,'sstt'),(78,'var2'),(90,'lop'); 278 select * from `%!%p0%!%p_table_002`; 279 select * from `%!%p1%!%p_table_002`; 280 select * from `%!%p2%!%p_table_002`; 281 select * from `%!%p3%!%p_table_002`; 282 283 -- key partition abnormal type : json,blob,text 284 create table p_table_003(col1 int,col2 blob)partition by key(col2)partitions 4; 285 create table p_table_non(col1 int,col2 json)partition by key(col2)partitions 4; 286 create table p_table_004(col1 int,col2 text)partition by key(col2)partitions 4; 287 288 -- key partition: 289 create temporary table p_table_non(col1 int,col2 varchar(25))partition by key(col2)partitions 4; 290 create view p_view as select * from p_table_19; 291 select * from p_view; 292 drop view p_view; 293 CREATE TABLE IF NOT EXISTS p_table_20( 294 `id` INT, 295 `act_name` VARCHAR(20) NOT NULL, 296 `spu_id` VARCHAR(30) NOT NULL, 297 `uv` BIGINT NOT NULL, 298 `update_time` date default '2020-10-10' COMMENT 'lastest time', 299 PRIMARY KEY ( `id`, `act_name`) 300 )partition by key(`act_name`) partitions 3; 301 insert into p_table_20 values (1,'beijing','001',1,'2021-01-03'),(2,'beijing','002',2,'2022-09-23'),(3,'guangzhou','003',3,'2022-09-23'),(3,'shanghai','003',3,'2022-09-23'); 302 insert into p_table_20 values (4,'shenzheng','004',4,'2021-05-28'),(1,'beijing','010',5,'2022-10-23') on duplicate key update id=id*10; 303 select * from p_table_20; 304 select * from `%!%p0%!%p_table_20`; 305 select * from `%!%p1%!%p_table_20`; 306 select * from `%!%p2%!%p_table_20`; 307 drop table p_table_20; 308 309 create table p_table_20(col1 int,col2 date, col3 varchar(25))partition by key(col1,col2)partitions 4; 310 start transaction; 311 insert into p_table_20 values (72,'1999-09-29','res1'),(60,'1999-10-01','opt1'),(72,'1999-09-29','res2'),(60,'1999-10-01','opt2'), (200,'1999-10-29','oop1'),(200,'1999-10-29','oop1'); 312 -- @session:id=2{ 313 use hash_key_partition; 314 select * from p_table_20; 315 -- @session} 316 select * from `%!%p0%!%p_table_20`; 317 select * from `%!%p1%!%p_table_20`; 318 select * from `%!%p2%!%p_table_20`; 319 select * from `%!%p3%!%p_table_20`; 320 rollback; 321 select * from p_table_20; 322 select * from `%!%p0%!%p_table_20`; 323 select * from `%!%p1%!%p_table_20`; 324 select * from `%!%p2%!%p_table_20`; 325 select * from `%!%p3%!%p_table_20`; 326 327 drop table p_table_20; 328 create table p_table_20(col1 int,col2 date, col3 varchar(25))partition by key(col1,col2)partitions 4; 329 begin; 330 insert into p_table_20 values (72,'1999-09-29','res1'),(60,'1999-10-01','opt1'),(72,'1999-09-29','res2'),(60,'1999-10-01','opt2'), (200,'1999-10-29','oop1'),(200,'1999-10-29','oop1'); 331 -- @session:id=2{ 332 select * from p_table_20; 333 -- @session} 334 select * from `%!%p0%!%p_table_20`; 335 select * from `%!%p1%!%p_table_20`; 336 select * from `%!%p2%!%p_table_20`; 337 select * from `%!%p3%!%p_table_20`; 338 commit; 339 select * from p_table_20; 340 select * from `%!%p0%!%p_table_20`; 341 select * from `%!%p1%!%p_table_20`; 342 select * from `%!%p2%!%p_table_20`; 343 select * from `%!%p3%!%p_table_20`; 344 345 -- hash partition: int,pk/not pk 346 create table p_hash_table_01(col1 int not null,col2 varchar(30),col3 date not null default '1970-01-01',col4 int)partition by hash(col4) partitions 4; 347 insert into p_hash_table_01 values (-120,'78',NULL,90); 348 insert into p_hash_table_01 values (-120,'78','2020-12-15',90),(84,'334','2021-01-01',34),(20,'55','2021-01-01',72),(-120,'45','2023-10-09',99),(84,'3','2022-11-01',34),(200,'55','2021-08-11',72); 349 select * from `%!%p0%!%p_hash_table_01`; 350 select * from `%!%p1%!%p_hash_table_01`; 351 select * from `%!%p2%!%p_hash_table_01`; 352 select * from `%!%p3%!%p_hash_table_01`; 353 show create table p_hash_table_01; 354 create table p_hash_table_02(col1 int not null,col2 varchar(30),col3 date default '1970-01-01',col4 int,primary key(col1,col2))partition by hash(col1) partitions 4; 355 insert into p_hash_table_02 values (-120,'78',NULL,90),(84,'334','2021-01-01',34),(20,'55','2021-01-01',72),(-120,'45',NULL,99),(84,'3','2022-11-01',34),(200,'55','2021-08-11',72); 356 select * from `%!%p0%!%p_hash_table_02`; 357 select * from `%!%p1%!%p_hash_table_02`; 358 select * from `%!%p2%!%p_hash_table_02`; 359 select * from `%!%p3%!%p_hash_table_02`; 360 361 -- hash partition: bigint,key（expr） 362 create table p_hash_table_03(col1 bigint auto_increment,col2 varchar(30),col3 date default '1970-01-01',col4 int,unique key k1(col1,col2))partition by hash(col1) partitions 4; 363 insert into p_hash_table_03 values (-120,'78',NULL,90),(84,'334','2021-01-01',34),(20,'55','2021-01-01',72),(-120,'45',NULL,99),(84,'3','2022-11-01',34),(200,'55','2021-08-11',72); 364 select * from `%!%p0%!%p_hash_table_03`; 365 select * from `%!%p1%!%p_hash_table_03`; 366 select * from `%!%p2%!%p_hash_table_03`; 367 select * from `%!%p3%!%p_hash_table_03`; 368 show create table p_hash_table_03; 369 create table p_hash_table_04(col1 bigint ,col2 date,col3 varchar(30))partition by hash(year(col2)) partitions 4; 370 load data infile '$resources/load_data/key_partition_data.csv' into table p_hash_table_04 fields terminated by ','; 371 select * from `%!%p0%!%p_hash_table_04`; 372 select * from `%!%p1%!%p_hash_table_04`; 373 select * from `%!%p2%!%p_hash_table_04`; 374 select * from `%!%p3%!%p_hash_table_04`; 375 select * from p_hash_table_04 where col1>1050; 376 update p_hash_table_04 set col2='2023-09-05' where col1=1000; 377 update p_hash_table_04 set col1=999 where col2='2010-08-12'; 378 select * from `%!%p0%!%p_hash_table_04`; 379 select * from `%!%p1%!%p_hash_table_04`; 380 select * from `%!%p2%!%p_hash_table_04`; 381 select * from `%!%p3%!%p_hash_table_04`; 382 delete from p_hash_table_04 where col2='2010-08-12'; 383 select * from `%!%p0%!%p_hash_table_04`; 384 select * from `%!%p1%!%p_hash_table_04`; 385 select * from `%!%p2%!%p_hash_table_04`; 386 select * from `%!%p3%!%p_hash_table_04`; 387 truncate table p_hash_table_04; 388 select * from p_hash_table_04; 389 select * from `%!%p0%!%p_hash_table_04`; 390 select * from `%!%p1%!%p_hash_table_04`; 391 392 -- hash partition: null values 393 create table p_hash_table_05(col1 bigint unsigned,col2 varchar(30),col3 datetime)partition by hash(year(col3)) partitions 3; 394 insert into p_hash_table_05 values(1,'a','2023-04-24 23:00:00'), (8,'b','2023-04-24 13:00:00'),(8,'b',NULL); 395 select * from `%!%p0%!%p_hash_table_05`; 396 select * from `%!%p1%!%p_hash_table_05`; 397 select * from `%!%p2%!%p_hash_table_05`; 398 select * from p_hash_table_05; 399 400 -- hash partition: tinyint,tinyint unsigned 401 create table p_hash_table_06(col1 tinyint,col2 varchar(30))partition by hash(col1) partitions 2; 402 insert into p_hash_table_06 values (10,'nb'),(10,'bv'),(12,'nb'),(12,'bv'),(13,'nb'),(14,'bv'); 403 select * from `%!%p0%!%p_hash_table_06`; 404 select * from `%!%p1%!%p_hash_table_06`; 405 create table p_hash_table_07(col1 tinyint unsigned,col2 varchar(30))partition by hash(col1) partitions 2; 406 insert into p_hash_table_07 values (10,'nb'),(10,'bv'),(12,'nb'),(12,'bv'),(13,'nb'),(14,'bv'); 407 select * from `%!%p0%!%p_hash_table_07`; 408 select * from `%!%p1%!%p_hash_table_07`; 409 select * from p_hash_table_07; 410 411 -- hash partition: key(expr) 412 create table p_hash_table_08(col1 tinyint,col2 varchar(30),col3 decimal(6,3))partition by hash(ceil(col3)) partitions 2; 413 insert into p_hash_table_08 values (10,'nb',35.5),(10,'bv',35.45),(12,'nb',30.09),(12,'nb',30.23); 414 select * from `%!%p0%!%p_hash_table_08`; 415 select * from `%!%p1%!%p_hash_table_08`; 416 select * from p_hash_table_08; 417 drop table if exists p_hash_table_08; 418 create table p_hash_table_08(col1 tinyint,col2 varchar(30),col3 decimal(6,3))partition by hash(col1*100)partitions 2; 419 insert into p_hash_table_08 values (10,'nb',35.5),(10,'bv',35.45),(12,'nb',30.09),(12,'nb',30.23); 420 select * from `%!%p0%!%p_hash_table_08`; 421 select * from `%!%p1%!%p_hash_table_08`; 422 select * from p_hash_table_08; 423 424 -- abnormal test 425 drop table if exists p_hash_table_03; 426 create table p_hash_table_03(col1 bigint auto_increment,col2 varchar(30),col3 date default '1970-01-01',col4 int,primary key(col1,col2),unique key k1(col4))partition by hash(col1) partitions 4; 427 create table p_hash_table_03(col1 bigint ,col2 date default '1970-01-01',col3 varchar(30))partition by hash(year(col3)) partitions 8; 428 create table p_hash_table_06(col1 tinyint,col2 varchar(30),col3 decimal(6,3))partition by hash(col3) partitions 2; 429 create table p_hash_table_06(col1 tinyint,col2 varchar(30),col3 blob)partition by hash(col3) partitions 2; 430 create table p_hash_table_06(col1 tinyint,col2 varchar(30))partition by hash(col2) partitions 2; 431 create table p_hash_table_06(col1 tinyint,col2 varchar(30),col3 json)partition by hash(col3) partitions 2; 432 create table p_hash_table_06(col1 tinyint,col2 varchar(30),col3 Binary)partition by hash(col3) partitions 2; 433 create table p_hash_table_06(col1 tinyint,col2 varchar(30),col3 varbinary(25))partition by hash(col3) partitions 2; 434 create table p_hash_table_06(col1 tinyint,col2 varchar(30),col3 date)partition by hash(col3) partitions 2; 435 create table p_hash_table_06(col1 tinyint,col2 varchar(30),col3 datetime)partition by hash(col3) partitions 2; 436 create table p_hash_table_06(col1 tinyint,col2 varchar(30),col3 timestamp)partition by hash(col3) partitions 2; 437 create table p_hash_table_06(col1 tinyint,col2 varchar(30),col3 varchar(25))partition by hash(col3) partitions 2; 438 create table p_hash_table_06(col1 tinyint,col2 varchar(30),col3 char(25))partition by hash(col3) partitions 2; 439 create table p_hash_table_06(col1 tinyint,col2 varchar(30),col3 bool)partition by hash(col3) partitions 2; 440 create table p_hash_table_08(col1 tinyint,col2 varchar(30),col3 decimal(6,3))partition by hash(col1*100/3)partitions 2; 441 442 create table p_hash_table_09(col1 bigint auto_increment,col2 varchar(30),col3 date default '1970-01-01',col4 int,unique key k1(col1,col2))partition by hash(col1) partitions 4; 443 start transaction ; 444 insert into p_hash_table_09 values (-120,'78',NULL,90),(84,'334','2021-01-01',34),(20,'55','2021-01-01',72),(-120,'45',NULL,99),(84,'3','2022-11-01',34),(200,'55','2021-08-11',72); 445 -- @session:id=2{ 446 use hash_key_partition; 447 select * from p_hash_table_09; 448 -- @session} 449 select * from p_hash_table_09; 450 select * from `%!%p0%!%p_hash_table_09`; 451 select * from `%!%p1%!%p_hash_table_09`; 452 select * from `%!%p2%!%p_hash_table_09`; 453 select * from `%!%p3%!%p_hash_table_09`; 454 rollback; 455 select * from p_hash_table_09; 456 select * from `%!%p0%!%p_hash_table_09`; 457 select * from `%!%p1%!%p_hash_table_09`; 458 select * from `%!%p2%!%p_hash_table_09`; 459 select * from `%!%p3%!%p_hash_table_09`; 460 drop table p_hash_table_09; 461 462 create table p_hash_table_09(col1 bigint auto_increment,col2 varchar(30),col3 date default '1970-01-01',col4 int,unique key k1(col1,col2))partition by hash(col1) partitions 4; 463 start transaction ; 464 insert into p_hash_table_09 values (-120,'78',NULL,90),(84,'334','2021-01-01',34),(20,'55','2021-01-01',72),(-120,'45',NULL,99),(84,'3','2022-11-01',34),(200,'55','2021-08-11',72); 465 -- @session:id=2{ 466 use hash_key_partition; 467 select * from p_hash_table_09; 468 -- @session} 469 select * from p_hash_table_09; 470 select * from `%!%p0%!%p_hash_table_09`; 471 select * from `%!%p1%!%p_hash_table_09`; 472 select * from `%!%p2%!%p_hash_table_09`; 473 select * from `%!%p3%!%p_hash_table_09`; 474 commit; 475 select * from p_hash_table_09; 476 select * from `%!%p0%!%p_hash_table_09`; 477 select * from `%!%p1%!%p_hash_table_09`; 478 select * from `%!%p2%!%p_hash_table_09`; 479 select * from `%!%p3%!%p_hash_table_09`; 480 481 create table p_hash_table_10(col1 bigint ,col2 date,col3 varchar(30))partition by hash(to_days(col2)) partitions 4; 482 load data infile '$resources/load_data/key_partition_data.csv' into table p_hash_table_10 fields terminated by ','; 483 select * from `%!%p0%!%p_hash_table_10`; 484 select * from `%!%p1%!%p_hash_table_10`; 485 select * from `%!%p2%!%p_hash_table_10`; 486 select * from `%!%p3%!%p_hash_table_10`; 487 drop table p_hash_table_10; 488 create table p_hash_table_10(col1 bigint ,col2 date,col3 varchar(30))partition by hash(to_seconds(col2)) partitions 4; 489 load data infile '$resources/load_data/key_partition_data.csv' into table p_hash_table_10 fields terminated by ','; 490 select * from `%!%p0%!%p_hash_table_10`; 491 select * from `%!%p1%!%p_hash_table_10`; 492 select * from `%!%p2%!%p_hash_table_10`; 493 select * from `%!%p3%!%p_hash_table_10`;