Edit File: partition_decimal_innodb.result
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 /*!50100 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 /*!50100 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 /*!50100 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; insert into t3 values (9); insert into t3 values (9+0.333333333); insert into t3 values (9+0.755555555); insert into t3 values (8); insert into t3 values (8+0.333333333); insert into t3 values (8+0.755555555); insert into t3 values (7); insert into t3 values (7+0.333333333); insert into t3 values (7+0.755555555); insert into t3 values (6); insert into t3 values (6+0.333333333); insert into t3 values (6+0.755555555); insert into t3 values (5); insert into t3 values (5+0.333333333); insert into t3 values (5+0.755555555); insert into t3 values (4); insert into t3 values (4+0.333333333); insert into t3 values (4+0.755555555); insert into t3 values (3); insert into t3 values (3+0.333333333); insert into t3 values (3+0.755555555); insert into t3 values (2); insert into t3 values (2+0.333333333); insert into t3 values (2+0.755555555); insert into t3 values (1); insert into t3 values (1+0.333333333); insert into t3 values (1+0.755555555); 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 /*!50100 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; insert into t4 values (9); insert into t4 values (9+0.333333333); insert into t4 values (9+0.755555555); insert into t4 values (8); insert into t4 values (8+0.333333333); insert into t4 values (8+0.755555555); insert into t4 values (7); insert into t4 values (7+0.333333333); insert into t4 values (7+0.755555555); insert into t4 values (6); insert into t4 values (6+0.333333333); insert into t4 values (6+0.755555555); insert into t4 values (5); insert into t4 values (5+0.333333333); insert into t4 values (5+0.755555555); insert into t4 values (4); insert into t4 values (4+0.333333333); insert into t4 values (4+0.755555555); insert into t4 values (3); insert into t4 values (3+0.333333333); insert into t4 values (3+0.755555555); insert into t4 values (2); insert into t4 values (2+0.333333333); insert into t4 values (2+0.755555555); insert into t4 values (1); insert into t4 values (1+0.333333333); insert into t4 values (1+0.755555555); select count(*) from t4; count(*) 27 drop table t4;
Back to File Manager