partition_decimal_innodb.result 3.68 KB
Newer Older
1 2
create table t1 (a decimal(10,4) not null, primary key(a)) engine='InnoDB' 
partition by key (a) (
3 4 5 6
partition pa1 max_rows=20 min_rows=2,
partition pa2 max_rows=30 min_rows=3,
partition pa3 max_rows=30 min_rows=4,
partition pa4 max_rows=40 min_rows=2);
7 8 9 10 11
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` decimal(10,4) NOT NULL,
  PRIMARY KEY (`a`)
12
) ENGINE=InnoDB DEFAULT CHARSET=latin1
13 14 15 16 17
 PARTITION BY KEY (`a`)
(PARTITION `pa1` MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
 PARTITION `pa2` MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
 PARTITION `pa3` MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
 PARTITION `pa4` MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB)
18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55
insert into t1 values (999999.9999), (-999999.9999), (123456.7899), (-123456.7899), (-1.5), (1), (0), (-1), (1.5), (1234.567), (-1234.567);
select * from t1;
a
-999999.9999
-123456.7899
-1234.5670
-1.5000
-1.0000
0.0000
1.0000
1.5000
1234.5670
123456.7899
999999.9999
select * from t1 where a=1234.567;
a
1234.5670
delete from t1 where a=1234.567;
select * from t1;
a
-999999.9999
-123456.7899
-1234.5670
-1.5000
-1.0000
0.0000
1.0000
1.5000
123456.7899
999999.9999
drop table t1;
create table t2 (a decimal(18,9) not null, primary key(a)) engine='InnoDB' 
partition by key (a) partitions 10;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` decimal(18,9) NOT NULL,
  PRIMARY KEY (`a`)
56
) ENGINE=InnoDB DEFAULT CHARSET=latin1
57
 PARTITION BY KEY (`a`)
58
PARTITIONS 10
59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88
insert into t2 values (999999999.999999999), (-999999999.999999999), (-1.5), (-1), (0), (1.5), (1234.567), (-1234.567);
select * from t2;
a
-999999999.999999999
-1234.567000000
-1.500000000
-1.000000000
0.000000000
1.500000000
1234.567000000
999999999.999999999
select * from t2 where a=1234.567;
a
1234.567000000
delete from t2 where a=1234.567;
select * from t2;
a
-999999999.999999999
-1234.567000000
-1.500000000
-1.000000000
0.000000000
1.500000000
999999999.999999999
delete from t2;
1024*3 inserts;
select count(*) from t2;
count(*)
3072
drop table t2;
89 90 91 92 93 94 95 96 97 98 99 100 101 102
create table t3 (a decimal(18,9) not null, primary key(a)) engine='InnoDB' 
partition by range (floor(a)) subpartition by key (a) subpartitions 2 (
partition pa2 values less than (2),
partition pa4 values less than (4),
partition pa6 values less than (6),
partition pa8 values less than (8),
partition pa10 values less than (10)
);
show create table t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `a` decimal(18,9) NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
103 104
 PARTITION BY RANGE (floor(`a`))
SUBPARTITION BY KEY (`a`)
105
SUBPARTITIONS 2
106 107 108 109 110
(PARTITION `pa2` VALUES LESS THAN (2) ENGINE = InnoDB,
 PARTITION `pa4` VALUES LESS THAN (4) ENGINE = InnoDB,
 PARTITION `pa6` VALUES LESS THAN (6) ENGINE = InnoDB,
 PARTITION `pa8` VALUES LESS THAN (8) ENGINE = InnoDB,
 PARTITION `pa10` VALUES LESS THAN (10) ENGINE = InnoDB)
111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129
9*3 inserts;
select count(*) from t3;
count(*)
27
drop table t3;
create table t4 (a decimal(18,9) not null, primary key(a)) engine='InnoDB' 
partition by list (ceiling(a)) subpartition by key (a) subpartitions 2 (
partition pa2 values in (1,2),
partition pa4 values in (3,4),
partition pa6 values in (5,6),
partition pa8 values in (7,8),
partition pa10 values in (9,10)
);
show create table t4;
Table	Create Table
t4	CREATE TABLE `t4` (
  `a` decimal(18,9) NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
130 131
 PARTITION BY LIST (ceiling(`a`))
SUBPARTITION BY KEY (`a`)
132
SUBPARTITIONS 2
133 134 135 136 137
(PARTITION `pa2` VALUES IN (1,2) ENGINE = InnoDB,
 PARTITION `pa4` VALUES IN (3,4) ENGINE = InnoDB,
 PARTITION `pa6` VALUES IN (5,6) ENGINE = InnoDB,
 PARTITION `pa8` VALUES IN (7,8) ENGINE = InnoDB,
 PARTITION `pa10` VALUES IN (9,10) ENGINE = InnoDB)
138 139 140 141 142
9*3 inserts;
select count(*) from t4;
count(*)
27
drop table t4;