1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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
56
57
58
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
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
|
create table t1 (a decimal(10,4) not null, primary key(a)) engine='InnoDB'
partition by key (a) (
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);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` decimal(10,4) NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
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)
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`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
PARTITION BY KEY (`a`)
PARTITIONS 10
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;
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 COLLATE=latin1_swedish_ci
PARTITION BY RANGE (floor(`a`))
SUBPARTITION BY KEY (`a`)
SUBPARTITIONS 2
(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)
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 COLLATE=latin1_swedish_ci
PARTITION BY LIST (ceiling(`a`))
SUBPARTITION BY KEY (`a`)
SUBPARTITIONS 2
(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)
9*3 inserts;
select count(*) from t4;
count(*)
27
drop table t4;
|
