You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
3300 lines
108 KiB
3300 lines
108 KiB
drop table if exists t1, t2;
|
|
#
|
|
# Bug#11765667: bug#58655: ASSERTION FAILED,
|
|
# SERVER CRASHES WITH MYSQLD GOT SIGNAL 6
|
|
#
|
|
CREATE TABLE t1 (
|
|
id MEDIUMINT NOT NULL AUTO_INCREMENT,
|
|
dt DATE, st VARCHAR(255), uid INT,
|
|
id2nd LONGBLOB, filler VARCHAR(255), PRIMARY KEY(id, dt)
|
|
);
|
|
INSERT INTO t1 (dt, st, uid, id2nd, filler) VALUES
|
|
('1991-03-14', 'Initial Insert', 200, 1234567, 'No Data'),
|
|
('1991-02-26', 'Initial Insert', 201, 1234567, 'No Data'),
|
|
('1992-03-16', 'Initial Insert', 234, 1234567, 'No Data'),
|
|
('1992-07-02', 'Initial Insert', 287, 1234567, 'No Data'),
|
|
('1991-05-26', 'Initial Insert', 256, 1234567, 'No Data'),
|
|
('1991-04-25', 'Initial Insert', 222, 1234567, 'No Data'),
|
|
('1993-03-12', 'Initial Insert', 267, 1234567, 'No Data'),
|
|
('1993-03-14', 'Initial Insert', 291, 1234567, 'No Data'),
|
|
('1991-12-20', 'Initial Insert', 298, 1234567, 'No Data'),
|
|
('1994-10-31', 'Initial Insert', 220, 1234567, 'No Data');
|
|
ALTER TABLE t1 PARTITION BY LIST (YEAR(dt)) (
|
|
PARTITION d1 VALUES IN (1991, 1994),
|
|
PARTITION d2 VALUES IN (1993),
|
|
PARTITION d3 VALUES IN (1992, 1995, 1996)
|
|
);
|
|
INSERT INTO t1 (dt, st, uid, id2nd, filler) VALUES
|
|
('1991-07-14', 'After Partitioning Insert', 299, 1234567, 'Insert row');
|
|
UPDATE t1 SET filler='Updating the row' WHERE uid=298;
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#59297: Can't find record in 'tablename' on update inner join
|
|
#
|
|
CREATE TABLE t1 (
|
|
a char(2) NOT NULL,
|
|
b char(2) NOT NULL,
|
|
c int(10) unsigned NOT NULL,
|
|
d varchar(255) DEFAULT NULL,
|
|
e varchar(1000) DEFAULT NULL,
|
|
PRIMARY KEY (a, b, c),
|
|
KEY (a),
|
|
KEY (a, b)
|
|
)
|
|
/*!50100 PARTITION BY KEY (a)
|
|
PARTITIONS 20 */;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t1 (a, b, c, d, e) VALUES
|
|
('07', '03', 343, '1', '07_03_343'),
|
|
('01', '04', 343, '2', '01_04_343'),
|
|
('01', '06', 343, '3', '01_06_343'),
|
|
('01', '07', 343, '4', '01_07_343'),
|
|
('01', '08', 343, '5', '01_08_343'),
|
|
('01', '09', 343, '6', '01_09_343'),
|
|
('03', '03', 343, '7', '03_03_343'),
|
|
('03', '06', 343, '8', '03_06_343'),
|
|
('03', '07', 343, '9', '03_07_343'),
|
|
('04', '03', 343, '10', '04_03_343'),
|
|
('04', '06', 343, '11', '04_06_343'),
|
|
('05', '03', 343, '12', '05_03_343'),
|
|
('11', '03', 343, '13', '11_03_343'),
|
|
('11', '04', 343, '14', '11_04_343')
|
|
;
|
|
UPDATE t1 AS A,
|
|
(SELECT '03' AS a, '06' AS b, 343 AS c, 'last' AS d) AS B
|
|
SET A.e = B.d
|
|
WHERE A.a = '03'
|
|
AND A.b = '06'
|
|
AND A.c = 343;
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#59503: explain crash in get_mm_leaf
|
|
#
|
|
CREATE TABLE t1 (a VARCHAR(51) CHARACTER SET latin1)
|
|
PARTITION BY KEY (a) PARTITIONS 1;
|
|
INSERT INTO t1 VALUES ('a'),('b'),('c');
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
EXPLAIN SELECT 1 FROM t1 WHERE a > 1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 3 33.33 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 1)
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#57778: failed primary key add to partitioned innodb table
|
|
# inconsistent and crashes
|
|
#
|
|
CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL)
|
|
PARTITION BY KEY (a) PARTITIONS 2;
|
|
INSERT INTO t1 VALUES (0,1), (0,2);
|
|
ALTER TABLE t1 ADD PRIMARY KEY (a);
|
|
ERROR 23000: Duplicate entry '0' for key 'PRIMARY'
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) NOT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY KEY (a)
|
|
PARTITIONS 2 */
|
|
SELECT * FROM t1;
|
|
a b
|
|
0 1
|
|
0 2
|
|
UPDATE t1 SET a = 1, b = 1 WHERE a = 0 AND b = 2;
|
|
ALTER TABLE t1 ADD PRIMARY KEY (a);
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 1
|
|
0 1
|
|
ALTER TABLE t1 DROP PRIMARY KEY;
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 1
|
|
0 1
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#57113: ha_partition::extra(ha_extra_function):
|
|
# Assertion `m_extra_cache' failed
|
|
CREATE TABLE t1
|
|
(id INT NOT NULL PRIMARY KEY,
|
|
name VARCHAR(16) NOT NULL,
|
|
year YEAR,
|
|
INDEX name (name(8))
|
|
)
|
|
PARTITION BY HASH(id) PARTITIONS 2;
|
|
INSERT INTO t1 VALUES ( 1, 'FooBar', '1924' );
|
|
CREATE TABLE t2 (id INT);
|
|
INSERT INTO t2 VALUES (1),(2);
|
|
UPDATE t1, t2 SET t1.year = '1955' WHERE t1.name = 'FooBar';
|
|
DROP TABLE t1, t2;
|
|
#
|
|
# Bug#55458: Partitioned MyISAM table gets crashed by multi-table update
|
|
# After WL#8971, this test case is rewritten to use InnoDB in order to
|
|
# keep some test coverage for multi update and partitioning.
|
|
CREATE TABLE t1 (
|
|
`id` int NOT NULL,
|
|
`user_num` int DEFAULT NULL,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB CHARSET=latin1;
|
|
INSERT INTO t1 VALUES (1,8601);
|
|
INSERT INTO t1 VALUES (2,8601);
|
|
INSERT INTO t1 VALUES (3,8601);
|
|
INSERT INTO t1 VALUES (4,8601);
|
|
CREATE TABLE t2 (
|
|
`id` int(11) NOT NULL,
|
|
`user_num` int DEFAULT NULL,
|
|
`name` varchar(64) NOT NULL,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB CHARSET=latin1
|
|
PARTITION BY HASH (id)
|
|
PARTITIONS 2;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t2 VALUES (1,8601,'John');
|
|
INSERT INTO t2 VALUES (2,8601,'JS');
|
|
INSERT INTO t2 VALUES (3,8601,'John S');
|
|
UPDATE t1, t2 SET t2.name = 'John Smith' WHERE t1.user_num = t2.user_num;
|
|
DROP TABLE t1, t2;
|
|
# Bug#39338: Fieldnames in
|
|
# INFORMATIONSCHEMA.PARTITIONS.PARTITION_EXPRESSION become unescaped
|
|
# NOTE: the partition expression is saved as a string, so changing from
|
|
# normal quotes to ansi quotes does not change the expression, only
|
|
# for partition by KEY.
|
|
CREATE TABLE t1 (
|
|
ID int(11) NOT NULL,
|
|
`aaaa,aaaaa` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
|
|
ddddddddd int(11) NOT NULL DEFAULT '0',
|
|
new_field0 varchar(50),
|
|
PRIMARY KEY(ID, `aaaa,aaaaa`, ddddddddd))
|
|
PARTITION BY RANGE(ID)
|
|
PARTITIONS 3
|
|
SUBPARTITION BY LINEAR KEY(ID,`aaaa,aaaaa`)
|
|
SUBPARTITIONS 2 (
|
|
PARTITION p01 VALUES LESS THAN(100),
|
|
PARTITION p11 VALUES LESS THAN(200),
|
|
PARTITION p21 VALUES LESS THAN MAXVALUE);
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
SELECT PARTITION_EXPRESSION, SUBPARTITION_EXPRESSION FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='t1';
|
|
PARTITION_EXPRESSION SUBPARTITION_EXPRESSION
|
|
`ID` `ID`,`aaaa,aaaaa`
|
|
`ID` `ID`,`aaaa,aaaaa`
|
|
`ID` `ID`,`aaaa,aaaaa`
|
|
`ID` `ID`,`aaaa,aaaaa`
|
|
`ID` `ID`,`aaaa,aaaaa`
|
|
`ID` `ID`,`aaaa,aaaaa`
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`ID` int(11) NOT NULL,
|
|
`aaaa,aaaaa` tinyint(3) unsigned NOT NULL DEFAULT '0',
|
|
`ddddddddd` int(11) NOT NULL DEFAULT '0',
|
|
`new_field0` varchar(50) DEFAULT NULL,
|
|
PRIMARY KEY (`ID`,`aaaa,aaaaa`,`ddddddddd`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`ID`)
|
|
SUBPARTITION BY LINEAR KEY (ID,`aaaa,aaaaa`)
|
|
SUBPARTITIONS 2
|
|
(PARTITION p01 VALUES LESS THAN (100) ENGINE = InnoDB,
|
|
PARTITION p11 VALUES LESS THAN (200) ENGINE = InnoDB,
|
|
PARTITION p21 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
|
|
drop table t1;
|
|
CREATE TABLE t1 (a INT, b INT)
|
|
PARTITION BY LIST (a)
|
|
SUBPARTITION BY HASH (b)
|
|
(PARTITION p1 VALUES IN (1));
|
|
ALTER TABLE t1 ADD COLUMN c INT;
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (
|
|
a int NOT NULL,
|
|
b int NOT NULL);
|
|
CREATE TABLE t2 (
|
|
a int NOT NULL,
|
|
b int NOT NULL,
|
|
INDEX(b)
|
|
)
|
|
PARTITION BY HASH(a) PARTITIONS 2;
|
|
INSERT INTO t1 VALUES (399, 22);
|
|
INSERT INTO t2 VALUES (1, 22), (1, 42);
|
|
INSERT INTO t2 SELECT 1, 399 FROM t2, t1
|
|
WHERE t1.b = t2.b;
|
|
DROP TABLE t1, t2;
|
|
CREATE TABLE t1 (
|
|
a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
b varchar(10),
|
|
PRIMARY KEY (a)
|
|
)
|
|
PARTITION BY RANGE (UNIX_TIMESTAMP(a)) (
|
|
PARTITION p1 VALUES LESS THAN (1199134800),
|
|
PARTITION pmax VALUES LESS THAN MAXVALUE
|
|
);
|
|
INSERT INTO t1 VALUES ('2007-07-30 17:35:48', 'p1');
|
|
INSERT INTO t1 VALUES ('2009-07-14 17:35:55', 'pmax');
|
|
INSERT INTO t1 VALUES ('2009-09-21 17:31:42', 'pmax');
|
|
SELECT * FROM t1;
|
|
a b
|
|
2007-07-30 17:35:48 p1
|
|
2009-07-14 17:35:55 pmax
|
|
2009-09-21 17:31:42 pmax
|
|
SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01';
|
|
a b
|
|
2007-07-30 17:35:48 p1
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
EXPLAIN SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01';
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p1 range PRIMARY PRIMARY 4 NULL 1 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` between '2007-01-01' and '2007-08-01')
|
|
EXPLAIN SELECT * FROM t1 where a = '2007-07-30 17:35:48';
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p1 const PRIMARY PRIMARY 4 const 1 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select '2007-07-30 17:35:48' AS `a`,'p1' AS `b` from `test`.`t1` where true
|
|
ALTER TABLE t1 REORGANIZE PARTITION pmax INTO (
|
|
PARTITION p3 VALUES LESS THAN (1247688000),
|
|
PARTITION pmax VALUES LESS THAN MAXVALUE);
|
|
SELECT * FROM t1;
|
|
a b
|
|
2007-07-30 17:35:48 p1
|
|
2009-07-14 17:35:55 pmax
|
|
2009-09-21 17:31:42 pmax
|
|
SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01';
|
|
a b
|
|
2007-07-30 17:35:48 p1
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
EXPLAIN SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01';
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p1 range PRIMARY PRIMARY 4 NULL 1 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` between '2007-01-01' and '2007-08-01')
|
|
EXPLAIN SELECT * FROM t1 where a = '2007-07-30 17:35:48';
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p1 const PRIMARY PRIMARY 4 const 1 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select '2007-07-30 17:35:48' AS `a`,'p1' AS `b` from `test`.`t1` where true
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`b` varchar(10) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (unix_timestamp(`a`))
|
|
(PARTITION p1 VALUES LESS THAN (1199134800) ENGINE = InnoDB,
|
|
PARTITION p3 VALUES LESS THAN (1247688000) ENGINE = InnoDB,
|
|
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
|
|
DROP TABLE t1;
|
|
create table t1 (a int NOT NULL, b varchar(5) NOT NULL)
|
|
default charset=utf8
|
|
partition by list (a)
|
|
subpartition by key (b)
|
|
(partition p0 values in (1),
|
|
partition p1 values in (2));
|
|
Warnings:
|
|
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
|
|
drop table t1;
|
|
create table t1 (a int, b int, key(a))
|
|
partition by list (a)
|
|
( partition p0 values in (1),
|
|
partition p1 values in (2));
|
|
insert into t1 values (1,1),(2,1),(2,2),(2,3);
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
show indexes from t1;
|
|
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
|
|
t1 1 a 1 a A 1 NULL NULL YES BTREE YES NULL
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
show indexes from t1;
|
|
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
|
|
t1 1 a 1 a A 1 NULL NULL YES BTREE YES NULL
|
|
drop table t1;
|
|
create table t1 (a int)
|
|
partition by hash (a);
|
|
create index i on t1 (a);
|
|
insert into t1 values (1);
|
|
insert into t1 select * from t1;
|
|
create index i on t1 (a);
|
|
ERROR 42000: Duplicate key name 'i'
|
|
create index i2 on t1 (a);
|
|
Warnings:
|
|
Warning 1831 Duplicate index 'i2' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release.
|
|
drop table t1;
|
|
CREATE TABLE t0 (a INT PRIMARY KEY);
|
|
CREATE TABLE t1 (a INT, FOREIGN KEY (a) REFERENCES t0 (a))
|
|
PARTITION BY HASH (a);
|
|
ERROR HY000: Foreign keys are not yet supported in conjunction with partitioning
|
|
DROP TABLE t0;
|
|
CREATE TABLE t1 (
|
|
pk INT NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (pk)
|
|
)
|
|
/*!50100 PARTITION BY HASH (pk)
|
|
PARTITIONS 2 */;
|
|
INSERT INTO t1 VALUES (NULL);
|
|
INSERT INTO t1 VALUES (NULL);
|
|
INSERT INTO t1 VALUES (NULL);
|
|
SELECT * FROM t1 WHERE pk < 0 ORDER BY pk;
|
|
pk
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (a INT)
|
|
ENGINE=NonExistentEngine;
|
|
ERROR 42000: Unknown storage engine 'NonExistentEngine'
|
|
CREATE TABLE t1 (a INT)
|
|
ENGINE=NonExistentEngine
|
|
PARTITION BY HASH (a);
|
|
ERROR 42000: Unknown storage engine 'NonExistentEngine'
|
|
CREATE TABLE t1 (a INT);
|
|
ALTER TABLE t1 ENGINE=NonExistentEngine;
|
|
ERROR 42000: Unknown storage engine 'NonExistentEngine'
|
|
ALTER TABLE t1
|
|
PARTITION BY HASH (a)
|
|
(PARTITION p0 ENGINE=InnoDB,
|
|
PARTITION p1 ENGINE=NonExistentEngine);
|
|
ERROR 42000: Unknown storage engine 'NonExistentEngine'
|
|
ALTER TABLE t1 ENGINE=NonExistentEngine;
|
|
ERROR 42000: Unknown storage engine 'NonExistentEngine'
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
DROP TABLE t1;
|
|
SET sql_mode='';
|
|
CREATE TABLE t1 (a INT)
|
|
ENGINE=NonExistentEngine;
|
|
Warnings:
|
|
Warning 1286 Unknown storage engine 'NonExistentEngine'
|
|
Warning 1266 Using storage engine InnoDB for table 't1'
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (a INT)
|
|
ENGINE=NonExistentEngine
|
|
PARTITION BY HASH (a);
|
|
Warnings:
|
|
Warning 1286 Unknown storage engine 'NonExistentEngine'
|
|
Warning 1266 Using storage engine InnoDB for table 't1'
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (a INT);
|
|
ALTER TABLE t1 ENGINE=NonExistentEngine;
|
|
Warnings:
|
|
Warning 1286 Unknown storage engine 'NonExistentEngine'
|
|
ALTER TABLE t1
|
|
PARTITION BY HASH (a)
|
|
(PARTITION p0 ENGINE=InnoDB,
|
|
PARTITION p1 ENGINE=NonExistentEngine);
|
|
Warnings:
|
|
Warning 1286 Unknown storage engine 'NonExistentEngine'
|
|
ALTER TABLE t1 ENGINE=NonExistentEngine;
|
|
Warnings:
|
|
Warning 1286 Unknown storage engine 'NonExistentEngine'
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY HASH (`a`)
|
|
(PARTITION p0 ENGINE = InnoDB,
|
|
PARTITION p1 ENGINE = InnoDB) */
|
|
DROP TABLE t1;
|
|
SET sql_mode=DEFAULT;
|
|
CREATE TABLE t1 (a INT NOT NULL, KEY(a))
|
|
PARTITION BY RANGE(a)
|
|
(PARTITION p1 VALUES LESS THAN (200), PARTITION pmax VALUES LESS THAN MAXVALUE);
|
|
INSERT INTO t1 VALUES (2), (40), (40), (70), (60), (90), (199);
|
|
SELECT a FROM t1 WHERE a BETWEEN 60 AND 95 ORDER BY a ASC;
|
|
a
|
|
60
|
|
70
|
|
90
|
|
SELECT a FROM t1 WHERE a BETWEEN 60 AND 95;
|
|
a
|
|
60
|
|
70
|
|
90
|
|
INSERT INTO t1 VALUES (200), (250), (210);
|
|
SELECT a FROM t1 WHERE a BETWEEN 60 AND 220 ORDER BY a ASC;
|
|
a
|
|
60
|
|
70
|
|
90
|
|
199
|
|
200
|
|
210
|
|
SELECT a FROM t1 WHERE a BETWEEN 200 AND 220 ORDER BY a ASC;
|
|
a
|
|
200
|
|
210
|
|
SELECT a FROM t1 WHERE a BETWEEN 60 AND 95 ORDER BY a DESC;
|
|
a
|
|
90
|
|
70
|
|
60
|
|
SELECT a FROM t1 WHERE a BETWEEN 60 AND 220 ORDER BY a DESC;
|
|
a
|
|
210
|
|
200
|
|
199
|
|
90
|
|
70
|
|
60
|
|
SELECT a FROM t1 WHERE a BETWEEN 200 AND 220 ORDER BY a DESC;
|
|
a
|
|
210
|
|
200
|
|
SELECT a FROM t1 WHERE a BETWEEN 60 AND 220;
|
|
a
|
|
199
|
|
200
|
|
210
|
|
60
|
|
70
|
|
90
|
|
SELECT a FROM t1 WHERE a BETWEEN 200 AND 220;
|
|
a
|
|
200
|
|
210
|
|
SELECT a FROM t1 WHERE a BETWEEN 60 AND 95;
|
|
a
|
|
60
|
|
70
|
|
90
|
|
SELECT a FROM t1 WHERE a BETWEEN 60 AND 220;
|
|
a
|
|
199
|
|
200
|
|
210
|
|
60
|
|
70
|
|
90
|
|
SELECT a FROM t1 WHERE a BETWEEN 200 AND 220;
|
|
a
|
|
200
|
|
210
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (
|
|
a INT NOT NULL,
|
|
b MEDIUMINT NOT NULL,
|
|
c INT NOT NULL,
|
|
KEY b (b)
|
|
)
|
|
PARTITION BY LIST (a) (
|
|
PARTITION p0 VALUES IN (1)
|
|
);
|
|
INSERT INTO t1 VALUES (1,1,0), (1,1,1), (1,1,2), (1,1,53), (1,1,4), (1,1,5),
|
|
(1,1,6), (1,1,7), (1,1,8), (1,1,9), (1,1,10), (1,1,11), (1,1,12), (1,1,13),
|
|
(1,1,14), (1,1,15), (1,1,16), (1,1,67), (1,1,18), (1,1,19), (1,1,20), (1,1,21),
|
|
(1,1,22), (1,1,23), (1,1,24), (1,1,75), (1,1,26), (1,1,27), (1,1,128),
|
|
(1,1,79), (1,1,30), (1,1,31), (1,1,32), (1,1,33), (1,1,34), (1,1,85), (1,1,36),
|
|
(1,1,37), (1,1,38), (1,1,39), (1,1,40), (1,1,241), (1,1,42), (1,1,43),
|
|
(1,1,44), (1,1,45), (1,1,46), (1,1,147), (1,1,48), (1,1,49), (1,2,0), (1,2,1),
|
|
(1,2,2), (1,2,3), (1,2,4), (1,2,5), (1,2,6), (1,2,7), (1,2,8), (1,2,9),
|
|
(1,2,10), (1,2,11), (1,2,12), (1,2,13), (1,2,14), (1,2,15), (1,2,16), (1,2,17),
|
|
(1,2,18), (1,2,19), (1,2,20), (1,2,21), (1,2,22), (1,2,23), (1,2,24), (1,2,25),
|
|
(1,2,26), (1,2,27), (1,2,28), (1,2,29), (1,2,30), (1,2,31), (1,2,32), (1,2,33),
|
|
(1,2,34), (1,2,35), (1,2,36), (1,2,37), (1,2,38), (1,2,39), (1,2,40), (1,2,41),
|
|
(1,2,42), (1,2,43), (1,2,44), (1,2,45), (1,2,46), (1,2,47), (1,2,48), (1,2,49),
|
|
(1,6,0), (1,6,1), (1,6,2), (1,6,3), (1,6,4), (1,6,5), (1,6,6), (1,6,7),
|
|
(1,6,8), (1,6,9), (1,6,10), (1,6,11), (1,6,12), (1,6,13), (1,6,14), (1,6,15),
|
|
(1,6,16), (1,6,17), (1,6,18), (1,6,19), (1,6,20), (1,6,21), (1,6,22), (1,6,23),
|
|
(1,6,24), (1,6,25), (1,6,26), (1,6,27), (1,6,28), (1,6,29), (1,6,30), (1,6,31),
|
|
(1,6,32), (1,6,33), (1,6,34), (1,6,35), (1,6,36), (1,6,37), (1,6,38), (1,6,39),
|
|
(1,6,40), (1,6,41), (1,6,42), (1,6,43), (1,6,44), (1,6,45), (1,6,46), (1,6,47),
|
|
(1,6,48), (1,6,49), (1,7,0), (1,7,1), (1,7,2), (1,7,3), (1,7,4), (1,7,5),
|
|
(1,7,6), (1,7,7), (1,7,8), (1,7,9), (1,7,10), (1,7,11), (1,7,12), (1,7,13),
|
|
(1,7,14), (1,7,15), (1,7,16), (1,7,17), (1,7,18), (1,7,19), (1,7,20), (1,7,21),
|
|
(1,7,22), (1,7,23), (1,7,24), (1,7,25), (1,7,26), (1,7,27), (1,7,28), (1,7,29),
|
|
(1,7,30), (1,7,31), (1,7,32), (1,7,33), (1,7,34), (1,7,35), (1,7,38), (1,7,39),
|
|
(1,7,90), (1,7,41), (1,7,43), (1,7,48), (1,7,49), (1,9,0), (1,9,1), (1,9,2),
|
|
(1,9,3), (1,9,4), (1,9,5), (1,9,6), (1,9,7), (1,9,8), (1,9,9), (1,9,10),
|
|
(1,9,11), (1,9,12), (1,9,13), (1,9,14), (1,9,15), (1,9,16), (1,9,17), (1,9,18),
|
|
(1,9,19), (1,9,20), (1,9,21), (1,9,22), (1,9,23), (1,9,24), (1,9,25), (1,9,26),
|
|
(1,9,29), (1,9,32), (1,9,35), (1,9,38), (1,10,0), (1,10,1), (1,10,2), (1,10,3),
|
|
(1,10,4), (1,10,5), (1,10,6), (1,10,7), (1,10,8), (1,10,9), (1,10,10),
|
|
(1,10,11), (1,10,13), (1,10,14), (1,10,15), (1,10,16), (1,10,17), (1,10,18),
|
|
(1,10,22), (1,10,24), (1,10,25), (1,10,26), (1,10,28), (1,10,131), (1,10,33),
|
|
(1,10,84), (1,10,35), (1,10,40), (1,10,42), (1,10,49), (1,11,0), (1,11,1),
|
|
(1,11,2), (1,11,3), (1,11,4), (1,11,5), (1,11,6), (1,11,7), (1,11,8), (1,11,9),
|
|
(1,11,10), (1,11,11), (1,11,12), (1,11,13), (1,11,14), (1,11,15), (1,11,16),
|
|
(1,11,17), (1,11,18), (1,11,19), (1,11,20), (1,11,21), (1,11,22), (1,11,23),
|
|
(1,11,24), (1,11,25), (1,11,26), (1,11,27), (1,11,28), (1,11,30), (1,11,31),
|
|
(1,11,32), (1,11,33), (1,11,34), (1,11,35), (1,11,37), (1,11,39), (1,11,40),
|
|
(1,11,42), (1,11,44), (1,11,45), (1,11,47), (1,11,48), (1,14,104), (1,14,58),
|
|
(1,14,12), (1,14,13), (1,14,15), (1,14,16), (1,14,17), (1,14,34), (1,15,0),
|
|
(1,15,1), (1,15,2), (1,15,3), (1,15,4), (1,15,5), (1,15,7), (1,15,9),
|
|
(1,15,15), (1,15,27), (1,15,49), (1,16,0), (1,16,1), (1,16,3), (1,17,4),
|
|
(1,19,1);
|
|
SELECT COUNT(*) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
|
|
COUNT(*)
|
|
24
|
|
SELECT SUM(c) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
|
|
SUM(c)
|
|
400
|
|
ALTER TABLE t1 DROP INDEX b;
|
|
SELECT COUNT(*) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
|
|
COUNT(*)
|
|
24
|
|
SELECT SUM(c) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
|
|
SUM(c)
|
|
400
|
|
ALTER TABLE t1 ADD INDEX b USING HASH (b);
|
|
Warnings:
|
|
Note 3502 This storage engine does not support the HASH index algorithm, storage engine default was used instead.
|
|
SELECT COUNT(*) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
|
|
COUNT(*)
|
|
24
|
|
SELECT SUM(c) FROM t1 WHERE b NOT IN ( 1,2,6,7,9,10,11 );
|
|
SUM(c)
|
|
400
|
|
DROP TABLE t1;
|
|
CREATE TABLE `t1` (
|
|
`c1` int(11) DEFAULT NULL,
|
|
KEY `c1` (`c1`)
|
|
) DEFAULT CHARSET=latin1;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
CREATE TABLE `t2` (
|
|
`c1` int(11) DEFAULT NULL,
|
|
KEY `c1` (`c1`)
|
|
) DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (c1) (PARTITION a VALUES LESS THAN (100) , PARTITION b VALUES LESS THAN MAXVALUE ) */;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO `t1` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
|
|
INSERT INTO `t2` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
EXPLAIN SELECT c1 FROM t1 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL range c1 c1 5 NULL 4 100.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where (((`test`.`t1`.`c1` > 10) and (`test`.`t1`.`c1` < 13)) or ((`test`.`t1`.`c1` > 17) and (`test`.`t1`.`c1` < 20)))
|
|
FLUSH STATUS;
|
|
SELECT c1 FROM t1 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20);
|
|
c1
|
|
11
|
|
12
|
|
18
|
|
19
|
|
SHOW STATUS LIKE 'Handler_read_%';
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 2
|
|
Handler_read_last 0
|
|
Handler_read_next 4
|
|
Handler_read_prev 0
|
|
Handler_read_rnd 0
|
|
Handler_read_rnd_next 0
|
|
EXPLAIN SELECT c1 FROM t2 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 a range c1 c1 5 NULL 4 100.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`c1` AS `c1` from `test`.`t2` where (((`test`.`t2`.`c1` > 10) and (`test`.`t2`.`c1` < 13)) or ((`test`.`t2`.`c1` > 17) and (`test`.`t2`.`c1` < 20)))
|
|
FLUSH STATUS;
|
|
SELECT c1 FROM t2 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20);
|
|
c1
|
|
11
|
|
12
|
|
18
|
|
19
|
|
SHOW STATUS LIKE 'Handler_read_%';
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 2
|
|
Handler_read_last 0
|
|
Handler_read_next 4
|
|
Handler_read_prev 0
|
|
Handler_read_rnd 0
|
|
Handler_read_rnd_next 0
|
|
DROP TABLE t1,t2;
|
|
CREATE TABLE `t1` (
|
|
`c1` int(11) DEFAULT NULL,
|
|
KEY `c1` (`c1`)
|
|
) DEFAULT CHARSET=latin1;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
CREATE TABLE `t2` (
|
|
`c1` int(11) DEFAULT NULL,
|
|
KEY `c1` (`c1`)
|
|
) DEFAULT CHARSET=latin1
|
|
/*!50100 PARTITION BY RANGE (c1)
|
|
(PARTITION a VALUES LESS THAN (100) ,
|
|
PARTITION b VALUES LESS THAN MAXVALUE ) */;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO `t1` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
|
|
INSERT INTO `t2` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
EXPLAIN SELECT c1 FROM t1 WHERE (c1 > 2 AND c1 < 5);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL range c1 c1 5 NULL 2 100.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where ((`test`.`t1`.`c1` > 2) and (`test`.`t1`.`c1` < 5))
|
|
FLUSH STATUS;
|
|
SELECT c1 FROM t1 WHERE (c1 > 2 AND c1 < 5);
|
|
c1
|
|
3
|
|
4
|
|
SHOW STATUS LIKE 'Handler_read_%';
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 1
|
|
Handler_read_last 0
|
|
Handler_read_next 2
|
|
Handler_read_prev 0
|
|
Handler_read_rnd 0
|
|
Handler_read_rnd_next 0
|
|
EXPLAIN SELECT c1 FROM t2 WHERE (c1 > 2 AND c1 < 5);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 a range c1 c1 5 NULL 2 100.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`c1` AS `c1` from `test`.`t2` where ((`test`.`t2`.`c1` > 2) and (`test`.`t2`.`c1` < 5))
|
|
FLUSH STATUS;
|
|
SELECT c1 FROM t2 WHERE (c1 > 2 AND c1 < 5);
|
|
c1
|
|
3
|
|
4
|
|
SHOW STATUS LIKE 'Handler_read_%';
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 1
|
|
Handler_read_last 0
|
|
Handler_read_next 2
|
|
Handler_read_prev 0
|
|
Handler_read_rnd 0
|
|
Handler_read_rnd_next 0
|
|
EXPLAIN SELECT c1 FROM t1 WHERE (c1 > 12 AND c1 < 15);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL range c1 c1 5 NULL 2 100.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where ((`test`.`t1`.`c1` > 12) and (`test`.`t1`.`c1` < 15))
|
|
FLUSH STATUS;
|
|
SELECT c1 FROM t1 WHERE (c1 > 12 AND c1 < 15);
|
|
c1
|
|
13
|
|
14
|
|
SHOW STATUS LIKE 'Handler_read_%';
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 1
|
|
Handler_read_last 0
|
|
Handler_read_next 2
|
|
Handler_read_prev 0
|
|
Handler_read_rnd 0
|
|
Handler_read_rnd_next 0
|
|
EXPLAIN SELECT c1 FROM t2 WHERE (c1 > 12 AND c1 < 15);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 a range c1 c1 5 NULL 2 100.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`c1` AS `c1` from `test`.`t2` where ((`test`.`t2`.`c1` > 12) and (`test`.`t2`.`c1` < 15))
|
|
FLUSH STATUS;
|
|
SELECT c1 FROM t2 WHERE (c1 > 12 AND c1 < 15);
|
|
c1
|
|
13
|
|
14
|
|
SHOW STATUS LIKE 'Handler_read_%';
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 1
|
|
Handler_read_last 0
|
|
Handler_read_next 2
|
|
Handler_read_prev 0
|
|
Handler_read_rnd 0
|
|
Handler_read_rnd_next 0
|
|
DROP TABLE t1,t2;
|
|
create table t1 (a int) partition by list ((a/3)*10 div 1)
|
|
(partition p0 values in (0), partition p1 values in (1));
|
|
ERROR HY000: This partition function is not allowed
|
|
CREATE TABLE t1 (
|
|
d DATE NOT NULL
|
|
)
|
|
PARTITION BY RANGE( YEAR(d) ) (
|
|
PARTITION p0 VALUES LESS THAN (1960),
|
|
PARTITION p1 VALUES LESS THAN (1970),
|
|
PARTITION p2 VALUES LESS THAN (1980),
|
|
PARTITION p3 VALUES LESS THAN (1990)
|
|
);
|
|
ALTER TABLE t1 ADD PARTITION (
|
|
PARTITION `p5` VALUES LESS THAN (2010)
|
|
COMMENT 'APSTART \' APEND'
|
|
);
|
|
SELECT * FROM t1 LIMIT 1;
|
|
d
|
|
DROP TABLE t1;
|
|
create table t1 (id int auto_increment, s1 int, primary key (id));
|
|
insert into t1 values (null,1);
|
|
insert into t1 values (null,6);
|
|
select * from t1;
|
|
id s1
|
|
1 1
|
|
2 6
|
|
alter table t1 partition by range (id) (
|
|
partition p0 values less than (3),
|
|
partition p1 values less than maxvalue
|
|
);
|
|
drop table t1;
|
|
create table t1 (a int)
|
|
partition by key(a)
|
|
partitions 0.2+e1;
|
|
ERROR 42000: Only integers allowed as number here near '0.2+e1' at line 3
|
|
create table t1 (a int)
|
|
partition by key(a)
|
|
partitions -1;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 3
|
|
create table t1 (a int)
|
|
partition by key(a)
|
|
partitions 1.5;
|
|
ERROR 42000: Only integers allowed as number here near '1.5' at line 3
|
|
create table t1 (a int)
|
|
partition by key(a)
|
|
partitions 1e+300;
|
|
ERROR 42000: Only integers allowed as number here near '1e+300' at line 3
|
|
create table t1 (a int)
|
|
partition by list (a)
|
|
(partition p0 values in (1));
|
|
create procedure pz()
|
|
alter table t1;
|
|
call pz();
|
|
call pz();
|
|
drop procedure pz;
|
|
drop table t1;
|
|
create table t1 (a bigint)
|
|
partition by range (a)
|
|
(partition p0 values less than (0xFFFFFFFFFFFFFFFF),
|
|
partition p1 values less than (10));
|
|
ERROR HY000: VALUES value for partition 'p0' must have type INT
|
|
create table t1 (a bigint)
|
|
partition by list (a)
|
|
(partition p0 values in (0xFFFFFFFFFFFFFFFF),
|
|
partition p1 values in (10));
|
|
ERROR HY000: VALUES value for partition 'p0' must have type INT
|
|
create table t1 (a bigint unsigned)
|
|
partition by range (a)
|
|
(partition p0 values less than (100),
|
|
partition p1 values less than MAXVALUE);
|
|
insert into t1 values (1);
|
|
drop table t1;
|
|
create table t1 (a bigint unsigned)
|
|
partition by hash (a);
|
|
insert into t1 values (0xFFFFFFFFFFFFFFFD);
|
|
insert into t1 values (0xFFFFFFFFFFFFFFFE);
|
|
select * from t1 where (a + 1) < 10;
|
|
a
|
|
select * from t1 where (a + 1) > 10;
|
|
a
|
|
18446744073709551613
|
|
18446744073709551614
|
|
drop table t1;
|
|
create table t1 (a int)
|
|
partition by range (a)
|
|
subpartition by key (a)
|
|
(partition p0 values less than (1));
|
|
alter table t1 add partition (partition p1 values less than (2));
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
SUBPARTITION BY KEY (a)
|
|
(PARTITION p0 VALUES LESS THAN (1) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES LESS THAN (2) ENGINE = InnoDB) */
|
|
alter table t1 reorganize partition p1 into (partition p1 values less than (3));
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
SUBPARTITION BY KEY (a)
|
|
(PARTITION p0 VALUES LESS THAN (1) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES LESS THAN (3) ENGINE = InnoDB) */
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key(a,b))
|
|
partition by key (a);
|
|
select count(*) from t1;
|
|
count(*)
|
|
0
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) NOT NULL,
|
|
`c` int(11) NOT NULL,
|
|
PRIMARY KEY (`a`,`b`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY KEY (a) */
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key(a,b))
|
|
partition by key (a, b);
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key(a,b))
|
|
partition by key (a)
|
|
partitions 3
|
|
(partition x1, partition x2, partition x3);
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key(a,b))
|
|
partition by key (a)
|
|
partitions 3
|
|
(partition x1 nodegroup 0,
|
|
partition x2 nodegroup 1,
|
|
partition x3 nodegroup 2);
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key(a,b))
|
|
partition by key (a)
|
|
partitions 3
|
|
(partition x1 engine innodb,
|
|
partition x2 engine innodb,
|
|
partition x3 engine innodb);
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key(a,b))
|
|
partition by key (a)
|
|
partitions 3
|
|
(partition x1 tablespace innodb_file_per_table,
|
|
partition x2 tablespace innodb_file_per_table,
|
|
partition x3 tablespace innodb_file_per_table);
|
|
CREATE TABLE t2 LIKE t1;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) NOT NULL,
|
|
`c` int(11) NOT NULL,
|
|
PRIMARY KEY (`a`,`b`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY KEY (a)
|
|
(PARTITION x1 TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB,
|
|
PARTITION x2 TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB,
|
|
PARTITION x3 TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB) */
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) NOT NULL,
|
|
`c` int(11) NOT NULL,
|
|
PRIMARY KEY (`a`,`b`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY KEY (a)
|
|
(PARTITION x1 TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB,
|
|
PARTITION x2 TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB,
|
|
PARTITION x3 TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB) */
|
|
drop table t2;
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key(a,b))
|
|
partition by list (a)
|
|
partitions 3
|
|
(partition x1 values in (1,2,9,4),
|
|
partition x2 values in (3, 11, 5, 7),
|
|
partition x3 values in (16, 8, 5+19, 70-43));
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) NOT NULL,
|
|
`c` int(11) NOT NULL,
|
|
PRIMARY KEY (`a`,`b`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY LIST (`a`)
|
|
(PARTITION x1 VALUES IN (1,2,9,4) ENGINE = InnoDB,
|
|
PARTITION x2 VALUES IN (3,11,5,7) ENGINE = InnoDB,
|
|
PARTITION x3 VALUES IN (16,8,24,27) ENGINE = InnoDB) */
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key(a,b))
|
|
partition by list (b*a)
|
|
partitions 3
|
|
(partition x1 values in (1,2,9,4),
|
|
partition x2 values in (3, 11, 5, 7),
|
|
partition x3 values in (16, 8, 5+19, 70-43));
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) NOT NULL,
|
|
`c` int(11) NOT NULL,
|
|
PRIMARY KEY (`a`,`b`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY LIST ((`b` * `a`))
|
|
(PARTITION x1 VALUES IN (1,2,9,4) ENGINE = InnoDB,
|
|
PARTITION x2 VALUES IN (3,11,5,7) ENGINE = InnoDB,
|
|
PARTITION x3 VALUES IN (16,8,24,27) ENGINE = InnoDB) */
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key(a,b))
|
|
partition by list (b*a)
|
|
(partition x1 values in (1),
|
|
partition x2 values in (3, 11, 5, 7),
|
|
partition x3 values in (16, 8, 5+19, 70-43));
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) NOT NULL,
|
|
`c` int(11) NOT NULL,
|
|
PRIMARY KEY (`a`,`b`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY LIST ((`b` * `a`))
|
|
(PARTITION x1 VALUES IN (1) ENGINE = InnoDB,
|
|
PARTITION x2 VALUES IN (3,11,5,7) ENGINE = InnoDB,
|
|
PARTITION x3 VALUES IN (16,8,24,27) ENGINE = InnoDB) */
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
a int not null)
|
|
partition by key(a);
|
|
LOCK TABLES t1 WRITE;
|
|
insert into t1 values (1);
|
|
insert into t1 values (2);
|
|
insert into t1 values (3);
|
|
insert into t1 values (4);
|
|
UNLOCK TABLES;
|
|
drop table t1;
|
|
CREATE TABLE t1 (a int, name VARCHAR(50), purchased DATE)
|
|
PARTITION BY RANGE (a)
|
|
(PARTITION p0 VALUES LESS THAN (3),
|
|
PARTITION p1 VALUES LESS THAN (7),
|
|
PARTITION p2 VALUES LESS THAN (9),
|
|
PARTITION p3 VALUES LESS THAN (11));
|
|
INSERT INTO t1 VALUES
|
|
(1, 'desk organiser', '2003-10-15'),
|
|
(2, 'CD player', '1993-11-05'),
|
|
(3, 'TV set', '1996-03-10'),
|
|
(4, 'bookcase', '1982-01-10'),
|
|
(5, 'exercise bike', '2004-05-09'),
|
|
(6, 'sofa', '1987-06-05'),
|
|
(7, 'popcorn maker', '2001-11-22'),
|
|
(8, 'acquarium', '1992-08-04'),
|
|
(9, 'study desk', '1984-09-16'),
|
|
(10, 'lava lamp', '1998-12-25');
|
|
SELECT * from t1 ORDER BY a;
|
|
a name purchased
|
|
1 desk organiser 2003-10-15
|
|
2 CD player 1993-11-05
|
|
3 TV set 1996-03-10
|
|
4 bookcase 1982-01-10
|
|
5 exercise bike 2004-05-09
|
|
6 sofa 1987-06-05
|
|
7 popcorn maker 2001-11-22
|
|
8 acquarium 1992-08-04
|
|
9 study desk 1984-09-16
|
|
10 lava lamp 1998-12-25
|
|
ALTER TABLE t1 DROP PARTITION p0;
|
|
SELECT * from t1 ORDER BY a;
|
|
a name purchased
|
|
3 TV set 1996-03-10
|
|
4 bookcase 1982-01-10
|
|
5 exercise bike 2004-05-09
|
|
6 sofa 1987-06-05
|
|
7 popcorn maker 2001-11-22
|
|
8 acquarium 1992-08-04
|
|
9 study desk 1984-09-16
|
|
10 lava lamp 1998-12-25
|
|
drop table t1;
|
|
CREATE TABLE t1 (a int)
|
|
PARTITION BY LIST (a)
|
|
(PARTITION p0 VALUES IN (1,2,3), PARTITION p1 VALUES IN (4,5,6));
|
|
insert into t1 values (1),(2),(3),(4),(5),(6);
|
|
select * from t1;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
6
|
|
truncate t1;
|
|
select * from t1;
|
|
a
|
|
truncate t1;
|
|
select * from t1;
|
|
a
|
|
drop table t1;
|
|
CREATE TABLE t1 (a int, b int, primary key(a,b))
|
|
PARTITION BY KEY(b,a) PARTITIONS 4;
|
|
insert into t1 values (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
|
|
select * from t1 where a = 4;
|
|
a b
|
|
4 4
|
|
drop table t1;
|
|
CREATE TABLE t1 (c1 INT, c2 INT, PRIMARY KEY USING BTREE (c1,c2))
|
|
PARTITION BY KEY(c2,c1) PARTITIONS 4;
|
|
INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
|
|
SELECT * FROM t1 WHERE c1 = 4;
|
|
c1 c2
|
|
4 4
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (a int)
|
|
PARTITION BY LIST (a)
|
|
PARTITIONS 1
|
|
(PARTITION x1 VALUES IN (1) ENGINE=InnoDB);
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY LIST (`a`)
|
|
(PARTITION x1 VALUES IN (1) ENGINE = InnoDB) */
|
|
drop table t1;
|
|
CREATE TABLE t1 (a int, unique(a))
|
|
PARTITION BY LIST (a)
|
|
(PARTITION x1 VALUES IN (10), PARTITION x2 VALUES IN (20));
|
|
REPLACE t1 SET a = 4;
|
|
ERROR HY000: Table has no partition for value 4
|
|
drop table t1;
|
|
CREATE TABLE t1 (a int)
|
|
PARTITION BY LIST (a)
|
|
(PARTITION x1 VALUES IN (2), PARTITION x2 VALUES IN (3));
|
|
insert into t1 values (2), (3);
|
|
insert into t1 values (4);
|
|
ERROR HY000: Table has no partition for value 4
|
|
insert into t1 values (1);
|
|
ERROR HY000: Table has no partition for value 1
|
|
drop table t1;
|
|
CREATE TABLE t1 (a int)
|
|
PARTITION BY HASH(a)
|
|
PARTITIONS 5;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY HASH (`a`)
|
|
PARTITIONS 5 */
|
|
drop table t1;
|
|
CREATE TABLE t1 (a int)
|
|
PARTITION BY RANGE (a)
|
|
(PARTITION x1 VALUES LESS THAN (2));
|
|
insert into t1 values (1);
|
|
update t1 set a = 5;
|
|
ERROR HY000: Table has no partition for value 5
|
|
drop table t1;
|
|
CREATE TABLE t1 (a int)
|
|
PARTITION BY LIST (a)
|
|
(PARTITION x1 VALUES IN (10), PARTITION x2 VALUES IN (20));
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
drop table t1;
|
|
create table t1
|
|
(a int)
|
|
partition by range (a)
|
|
( partition p0 values less than(10),
|
|
partition p1 values less than (20),
|
|
partition p2 values less than (25));
|
|
alter table t1 reorganize partition p2 into (partition p2 values less than (30));
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
(PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,
|
|
PARTITION p2 VALUES LESS THAN (30) ENGINE = InnoDB) */
|
|
drop table t1;
|
|
CREATE TABLE t1 (a int, b int)
|
|
PARTITION BY RANGE (a)
|
|
(PARTITION x0 VALUES LESS THAN (2),
|
|
PARTITION x1 VALUES LESS THAN (4),
|
|
PARTITION x2 VALUES LESS THAN (6),
|
|
PARTITION x3 VALUES LESS THAN (8),
|
|
PARTITION x4 VALUES LESS THAN (10),
|
|
PARTITION x5 VALUES LESS THAN (12),
|
|
PARTITION x6 VALUES LESS THAN (14),
|
|
PARTITION x7 VALUES LESS THAN (16),
|
|
PARTITION x8 VALUES LESS THAN (18),
|
|
PARTITION x9 VALUES LESS THAN (20));
|
|
ALTER TABLE t1 REORGANIZE PARTITION x0,x1,x2 INTO
|
|
(PARTITION x1 VALUES LESS THAN (6));
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
(PARTITION x1 VALUES LESS THAN (6) ENGINE = InnoDB,
|
|
PARTITION x3 VALUES LESS THAN (8) ENGINE = InnoDB,
|
|
PARTITION x4 VALUES LESS THAN (10) ENGINE = InnoDB,
|
|
PARTITION x5 VALUES LESS THAN (12) ENGINE = InnoDB,
|
|
PARTITION x6 VALUES LESS THAN (14) ENGINE = InnoDB,
|
|
PARTITION x7 VALUES LESS THAN (16) ENGINE = InnoDB,
|
|
PARTITION x8 VALUES LESS THAN (18) ENGINE = InnoDB,
|
|
PARTITION x9 VALUES LESS THAN (20) ENGINE = InnoDB) */
|
|
drop table t1;
|
|
create table t1 (a int not null, b int not null) partition by LIST (a+b) (
|
|
partition p0 values in (12),
|
|
partition p1 values in (14)
|
|
);
|
|
insert into t1 values (10,1);
|
|
ERROR HY000: Table has no partition for value 11
|
|
drop table t1;
|
|
create table t1 (f1 integer,f2 integer, f3 varchar(10), primary key(f1,f2))
|
|
partition by range(f1) subpartition by hash(f2) subpartitions 2
|
|
(partition p1 values less than (0),
|
|
partition p2 values less than (2),
|
|
partition p3 values less than (2147483647));
|
|
insert into t1 values(10,10,'10');
|
|
insert into t1 values(2,2,'2');
|
|
select * from t1 where f1 = 2;
|
|
f1 f2 f3
|
|
2 2 2
|
|
drop table t1;
|
|
create table t1 (f1 integer,f2 integer, unique index(f1))
|
|
partition by range(f1 div 2)
|
|
subpartition by hash(f1) subpartitions 2
|
|
(partition partb values less than (2),
|
|
partition parte values less than (4),
|
|
partition partf values less than (10000));
|
|
insert into t1 values(10,1);
|
|
select * from t1 where f1 = 10;
|
|
f1 f2
|
|
10 1
|
|
drop table t1;
|
|
set @old_default_engine= @@session.default_storage_engine;
|
|
set session default_storage_engine= 'innodb';
|
|
create table t1 (f_int1 int(11) default null) engine = innodb
|
|
partition by range (f_int1) subpartition by hash (f_int1)
|
|
(partition part1 values less than (1000)
|
|
(subpartition subpart11 engine = innodb));
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
drop table t1;
|
|
set session default_storage_engine= @old_default_engine;
|
|
create table t1 (f_int1 integer, f_int2 integer, primary key (f_int1))
|
|
partition by hash(f_int1) partitions 2;
|
|
insert into t1 values (1,1),(2,2);
|
|
replace into t1 values (1,1),(2,2);
|
|
drop table t1;
|
|
create table t1 (s1 int, unique (s1)) partition by list (s1) (partition x1 VALUES in (10), partition x2 values in (20));
|
|
alter table t1 add partition (partition x3 values in (30));
|
|
drop table t1;
|
|
create table t1 (a int)
|
|
partition by key(a)
|
|
partitions 2
|
|
(partition p0 engine=innodb, partition p1 engine=innodb);
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY KEY (a)
|
|
(PARTITION p0 ENGINE = InnoDB,
|
|
PARTITION p1 ENGINE = InnoDB) */
|
|
alter table t1;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY KEY (a)
|
|
(PARTITION p0 ENGINE = InnoDB,
|
|
PARTITION p1 ENGINE = InnoDB) */
|
|
alter table t1 engine=innodb;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY KEY (a)
|
|
(PARTITION p0 ENGINE = InnoDB,
|
|
PARTITION p1 ENGINE = InnoDB) */
|
|
alter table t1 remove partitioning;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
drop table t1;
|
|
create table t1 (a int)
|
|
engine=innodb
|
|
partition by key(a)
|
|
partitions 2
|
|
(partition p0 engine=innodb, partition p1 engine=innodb);
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY KEY (a)
|
|
(PARTITION p0 ENGINE = InnoDB,
|
|
PARTITION p1 ENGINE = InnoDB) */
|
|
alter table t1 add column b int remove partitioning;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
alter table t1
|
|
engine=innodb
|
|
partition by key(a)
|
|
(partition p0 engine=innodb, partition p1);
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY KEY (a)
|
|
(PARTITION p0 ENGINE = InnoDB,
|
|
PARTITION p1 ENGINE = InnoDB) */
|
|
alter table t1 engine=myisam, add column c int remove partitioning;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
`c` int(11) DEFAULT NULL
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
alter table t1
|
|
engine=innodb
|
|
partition by key (a)
|
|
(partition p0, partition p1);
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
`c` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY KEY (a)
|
|
(PARTITION p0 ENGINE = InnoDB,
|
|
PARTITION p1 ENGINE = InnoDB) */
|
|
alter table t1
|
|
partition by key (a)
|
|
(partition p0, partition p1);
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
`c` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY KEY (a)
|
|
(PARTITION p0 ENGINE = InnoDB,
|
|
PARTITION p1 ENGINE = InnoDB) */
|
|
alter table t1
|
|
partition by key(a)
|
|
(partition p0, partition p1 engine=innodb);
|
|
alter table t1
|
|
partition by key(a)
|
|
(partition p0 engine=innodb, partition p1);
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
f_int1 INTEGER, f_int2 INTEGER,
|
|
f_char1 CHAR(10), f_char2 CHAR(10), f_charbig VARCHAR(1000)
|
|
)
|
|
PARTITION BY RANGE(f_int1 DIV 2)
|
|
SUBPARTITION BY HASH(f_int1)
|
|
SUBPARTITIONS 2
|
|
(PARTITION parta VALUES LESS THAN (0),
|
|
PARTITION partb VALUES LESS THAN (5),
|
|
PARTITION parte VALUES LESS THAN (10),
|
|
PARTITION partf VALUES LESS THAN (2147483647));
|
|
INSERT INTO t1 SET f_int1 = NULL , f_int2 = -20, f_char1 = CAST(-20 AS CHAR),
|
|
f_char2 = CAST(-20 AS CHAR), f_charbig = '#NULL#';
|
|
SELECT * FROM t1 WHERE f_int1 IS NULL;
|
|
f_int1 f_int2 f_char1 f_char2 f_charbig
|
|
NULL -20 -20 -20 #NULL#
|
|
SELECT * FROM t1;
|
|
f_int1 f_int2 f_char1 f_char2 f_charbig
|
|
NULL -20 -20 -20 #NULL#
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
f_int1 INTEGER, f_int2 INTEGER,
|
|
f_char1 CHAR(10), f_char2 CHAR(10), f_charbig VARCHAR(1000) )
|
|
PARTITION BY LIST(MOD(f_int1,2))
|
|
SUBPARTITION BY KEY(f_int1)
|
|
(PARTITION part1 VALUES IN (-1) (SUBPARTITION sp1, SUBPARTITION sp2),
|
|
PARTITION part2 VALUES IN (0) (SUBPARTITION sp3, SUBPARTITION sp5),
|
|
PARTITION part3 VALUES IN (1) (SUBPARTITION sp4, SUBPARTITION sp6));
|
|
INSERT INTO t1 SET f_int1 = 2, f_int2 = 2, f_char1 = '2', f_char2 = '2', f_charbig = '===2===';
|
|
INSERT INTO t1 SET f_int1 = 2, f_int2 = 2, f_char1 = '2', f_char2 = '2', f_charbig = '===2===';
|
|
SELECT * FROM t1 WHERE f_int1 IS NULL;
|
|
f_int1 f_int2 f_char1 f_char2 f_charbig
|
|
drop table t1;
|
|
create procedure p ()
|
|
begin
|
|
create table t1 (s1 mediumint,s2 mediumint)
|
|
partition by list (s2)
|
|
(partition p1 values in (0),
|
|
partition p2 values in (1));
|
|
end//
|
|
call p()//
|
|
drop procedure p//
|
|
drop table t1;
|
|
create procedure p ()
|
|
begin
|
|
create table t1 (a int not null,b int not null,c int not null,primary key (a,b))
|
|
partition by range (a)
|
|
subpartition by hash (a+b)
|
|
(partition x1 values less than (1)
|
|
(subpartition x11,
|
|
subpartition x12),
|
|
partition x2 values less than (5)
|
|
(subpartition x21,
|
|
subpartition x22));
|
|
end//
|
|
call p()//
|
|
drop procedure p//
|
|
drop table t1//
|
|
create table t1 (a int,b int,c int,key(a,b))
|
|
partition by range (a)
|
|
partitions 3
|
|
(partition x1 values less than (0),
|
|
partition x2 values less than (10),
|
|
partition x3 values less than maxvalue);
|
|
insert into t1 values (NULL, 1, 1);
|
|
insert into t1 values (0, 1, 1);
|
|
insert into t1 values (12, 1, 1);
|
|
select partition_name, partition_description, table_rows
|
|
from information_schema.partitions where table_schema ='test';
|
|
PARTITION_NAME PARTITION_DESCRIPTION TABLE_ROWS
|
|
x1 0 1
|
|
x2 10 1
|
|
x3 MAXVALUE 1
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
`c` int(11) DEFAULT NULL,
|
|
KEY `a` (`a`,`b`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
(PARTITION x1 VALUES LESS THAN (0) ENGINE = InnoDB,
|
|
PARTITION x2 VALUES LESS THAN (10) ENGINE = InnoDB,
|
|
PARTITION x3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
|
|
drop table t1;
|
|
create table t1 (a int,b int, c int)
|
|
partition by list(a)
|
|
partitions 2
|
|
(partition x123 values in (11,12),
|
|
partition x234 values in (1 ,NULL, NULL));
|
|
ERROR HY000: Multiple definition of same constant in list partitioning
|
|
create table t1 (a int,b int, c int)
|
|
partition by list(a)
|
|
partitions 2
|
|
(partition x123 values in (11, NULL),
|
|
partition x234 values in (1 ,NULL));
|
|
ERROR HY000: Multiple definition of same constant in list partitioning
|
|
create table t1 (a int,b int, c int)
|
|
partition by list(a)
|
|
partitions 2
|
|
(partition x123 values in (11, 12),
|
|
partition x234 values in (5, 1));
|
|
insert into t1 values (NULL,1,1);
|
|
ERROR HY000: Table has no partition for value NULL
|
|
drop table t1;
|
|
create table t1 (a int,b int, c int)
|
|
partition by list(a)
|
|
partitions 2
|
|
(partition x123 values in (11, 12),
|
|
partition x234 values in (NULL, 1));
|
|
insert into t1 values (11,1,6);
|
|
insert into t1 values (NULL,1,1);
|
|
select partition_name, partition_description, table_rows
|
|
from information_schema.partitions where table_schema ='test';
|
|
PARTITION_NAME PARTITION_DESCRIPTION TABLE_ROWS
|
|
x123 11,12 1
|
|
x234 NULL,1 1
|
|
drop table t1;
|
|
create table t1 (a int)
|
|
partition by list (a)
|
|
(partition p0 values in (1));
|
|
alter table t1 rebuild partition;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
|
|
drop table t1;
|
|
create table t1 (a int)
|
|
partition by list (a)
|
|
(partition p0 values in (5));
|
|
insert into t1 values (0);
|
|
ERROR HY000: Table has no partition for value 0
|
|
drop table t1;
|
|
create table t1 (a int)
|
|
partition by range (a) subpartition by hash (a)
|
|
(partition p0 values less than (100));
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
SUBPARTITION BY HASH (`a`)
|
|
(PARTITION p0 VALUES LESS THAN (100) ENGINE = InnoDB) */
|
|
alter table t1 add partition (partition p1 values less than (200)
|
|
(subpartition subpart21));
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
SUBPARTITION BY HASH (`a`)
|
|
(PARTITION p0 VALUES LESS THAN (100)
|
|
(SUBPARTITION p0sp0 ENGINE = InnoDB),
|
|
PARTITION p1 VALUES LESS THAN (200)
|
|
(SUBPARTITION subpart21 ENGINE = InnoDB)) */
|
|
drop table t1;
|
|
create table t1 (a int)
|
|
partition by key (a);
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY KEY (a) */
|
|
alter table t1 add partition (partition p1);
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY KEY (a)
|
|
(PARTITION p0 ENGINE = InnoDB,
|
|
PARTITION p1 ENGINE = InnoDB) */
|
|
drop table t1;
|
|
create table t1 (a int, b int)
|
|
partition by range (a)
|
|
subpartition by hash(a)
|
|
(partition p0 values less than (0) (subpartition sp0),
|
|
partition p1 values less than (1));
|
|
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ')' at line 5
|
|
create table t1 (a int, b int)
|
|
partition by range (a)
|
|
subpartition by hash(a)
|
|
(partition p0 values less than (0),
|
|
partition p1 values less than (1) (subpartition sp0));
|
|
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near 'subpartition sp0))' at line 5
|
|
create table t1 (a int, b int)
|
|
partition by list (a)
|
|
subpartition by hash(a)
|
|
(partition p0 values in (0),
|
|
partition p1 values in (1) (subpartition sp0));
|
|
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near 'subpartition sp0))' at line 5
|
|
create table t1 (a int)
|
|
partition by hash (a)
|
|
(partition p0 (subpartition sp0));
|
|
ERROR HY000: It is only possible to mix RANGE/LIST partitioning with HASH/KEY partitioning for subpartitioning
|
|
create table t1 (a int)
|
|
partition by range (a)
|
|
(partition p0 values less than (1));
|
|
alter table t1 add partition (partition p1 values in (2));
|
|
ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition
|
|
alter table t1 add partition (partition p1);
|
|
ERROR HY000: Syntax error: RANGE PARTITIONING requires definition of VALUES LESS THAN for each partition
|
|
drop table t1;
|
|
create table t1 (a int)
|
|
partition by list (a)
|
|
(partition p0 values in (1));
|
|
alter table t1 add partition (partition p1 values less than (2));
|
|
ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition
|
|
alter table t1 add partition (partition p1);
|
|
ERROR HY000: Syntax error: LIST PARTITIONING requires definition of VALUES IN for each partition
|
|
drop table t1;
|
|
create table t1 (a int)
|
|
partition by hash (a)
|
|
(partition p0);
|
|
alter table t1 add partition (partition p1 values less than (2));
|
|
ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition
|
|
alter table t1 add partition (partition p1 values in (2));
|
|
ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition
|
|
drop table t1;
|
|
create table t1 (a int)
|
|
partition by list (a)
|
|
(partition p0 values in (1));
|
|
alter table t1 rebuild partition;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
|
|
drop table t1;
|
|
create table t2 (s1 int not null auto_increment, primary key (s1)) partition by list (s1) (partition p1 values in (1),partition p2 values in (2),partition p3 values in (3),partition p4 values in (4));
|
|
insert into t2 values (null),(null),(null);
|
|
select * from t2;
|
|
s1
|
|
1
|
|
2
|
|
3
|
|
select * from t2 where s1 < 2;
|
|
s1
|
|
1
|
|
update t2 set s1 = s1 + 1 order by s1 desc;
|
|
select * from t2 where s1 < 3;
|
|
s1
|
|
2
|
|
select * from t2 where s1 = 2;
|
|
s1
|
|
2
|
|
drop table t2;
|
|
create temporary table t1 (a int) partition by hash(a);
|
|
ERROR HY000: Cannot create temporary table with partitions
|
|
create table t1 (a int, b int) partition by list (a)
|
|
(partition p1 values in (1), partition p2 values in (2));
|
|
alter table t1 add primary key (b);
|
|
ERROR HY000: A PRIMARY KEY must include all columns in the table's partitioning function
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY LIST (`a`)
|
|
(PARTITION p1 VALUES IN (1) ENGINE = InnoDB,
|
|
PARTITION p2 VALUES IN (2) ENGINE = InnoDB) */
|
|
drop table t1;
|
|
create table t1 (a int unsigned not null auto_increment primary key)
|
|
partition by key(a);
|
|
alter table t1 rename t2, add c char(10), comment "no comment";
|
|
show create table t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`c` char(10) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='no comment'
|
|
/*!50100 PARTITION BY KEY (a) */
|
|
drop table t2;
|
|
create table t1 (f1 int) partition by hash (f1) as select 1;
|
|
drop table t1;
|
|
prepare stmt1 from 'create table t1 (s1 int) partition by hash (s1)';
|
|
execute stmt1;
|
|
execute stmt1;
|
|
ERROR 42S01: Table 't1' already exists
|
|
drop table t1;
|
|
CREATE PROCEDURE test.p1(IN i INT)
|
|
BEGIN
|
|
DECLARE CONTINUE HANDLER FOR sqlexception BEGIN END;
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1 (num INT,PRIMARY KEY(num));
|
|
START TRANSACTION;
|
|
INSERT INTO t1 VALUES(i);
|
|
savepoint t1_save;
|
|
INSERT INTO t1 VALUES (14);
|
|
ROLLBACK to savepoint t1_save;
|
|
COMMIT;
|
|
END|
|
|
CALL test.p1(12);
|
|
CALL test.p1(13);
|
|
drop table t1;
|
|
drop procedure test.p1;
|
|
CREATE TABLE t1 (a int not null)
|
|
partition by key(a)
|
|
(partition p0 COMMENT='first partition');
|
|
drop table t1;
|
|
CREATE TABLE t1 (`a b` int not null)
|
|
partition by key(`a b`);
|
|
drop table t1;
|
|
CREATE TABLE t1 (`a b` int not null)
|
|
partition by hash(`a b`);
|
|
drop table t1;
|
|
create table t1 (f1 integer) partition by range(f1)
|
|
(partition p1 values less than (0), partition p2 values less than (10));
|
|
insert into t1 set f1 = null;
|
|
select * from t1 where f1 is null;
|
|
f1
|
|
NULL
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
explain select * from t1 where f1 is null;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p1 ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where (`test`.`t1`.`f1` is null)
|
|
drop table t1;
|
|
create table t1 (f1 integer) partition by list(f1)
|
|
(partition p1 values in (1), partition p2 values in (null));
|
|
insert into t1 set f1 = null;
|
|
insert into t1 set f1 = 1;
|
|
select * from t1 where f1 is null or f1 = 1;
|
|
f1
|
|
1
|
|
NULL
|
|
drop table t1;
|
|
create table t1 (f1 smallint)
|
|
partition by list (f1) (partition p0 values in (null));
|
|
insert into t1 values (null);
|
|
select * from t1 where f1 is null;
|
|
f1
|
|
NULL
|
|
select * from t1 where f1 < 1;
|
|
f1
|
|
select * from t1 where f1 <= NULL;
|
|
f1
|
|
select * from t1 where f1 < NULL;
|
|
f1
|
|
select * from t1 where f1 >= NULL;
|
|
f1
|
|
select * from t1 where f1 > NULL;
|
|
f1
|
|
select * from t1 where f1 > 1;
|
|
f1
|
|
drop table t1;
|
|
create table t1 (f1 smallint)
|
|
partition by range (f1) (partition p0 values less than (0));
|
|
insert into t1 values (null);
|
|
select * from t1 where f1 is null;
|
|
f1
|
|
NULL
|
|
drop table t1;
|
|
create table t1 (f1 integer) partition by list(f1)
|
|
(
|
|
partition p1 values in (1),
|
|
partition p2 values in (NULL),
|
|
partition p3 values in (2),
|
|
partition p4 values in (3),
|
|
partition p5 values in (4)
|
|
);
|
|
insert into t1 values (1),(2),(3),(4),(null);
|
|
select * from t1 where f1 < 3;
|
|
f1
|
|
1
|
|
2
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
explain select * from t1 where f1 < 3;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p1,p3 ALL NULL NULL NULL NULL 2 50.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where (`test`.`t1`.`f1` < 3)
|
|
select * from t1 where f1 is null;
|
|
f1
|
|
NULL
|
|
explain select * from t1 where f1 is null;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p2 ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where (`test`.`t1`.`f1` is null)
|
|
drop table t1;
|
|
create table t1 (f1 int) partition by list(f1 div 2)
|
|
(
|
|
partition p1 values in (1),
|
|
partition p2 values in (NULL),
|
|
partition p3 values in (2),
|
|
partition p4 values in (3),
|
|
partition p5 values in (4)
|
|
);
|
|
insert into t1 values (2),(4),(6),(8),(null);
|
|
select * from t1 where f1 < 3;
|
|
f1
|
|
2
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
explain select * from t1 where f1 < 3;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p1,p2,p3,p4,p5 ALL NULL NULL NULL NULL 5 33.33 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where (`test`.`t1`.`f1` < 3)
|
|
select * from t1 where f1 is null;
|
|
f1
|
|
NULL
|
|
explain select * from t1 where f1 is null;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p2 ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where (`test`.`t1`.`f1` is null)
|
|
drop table t1;
|
|
create table t1 (a int) partition by LIST(a) (
|
|
partition pn values in (NULL),
|
|
partition p0 values in (0),
|
|
partition p1 values in (1),
|
|
partition p2 values in (2)
|
|
);
|
|
insert into t1 values (NULL),(0),(1),(2);
|
|
select * from t1 where a is null or a < 2;
|
|
a
|
|
NULL
|
|
0
|
|
1
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
explain select * from t1 where a is null or a < 2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 pn,p0,p1 ALL NULL NULL NULL NULL 3 55.56 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` is null) or (`test`.`t1`.`a` < 2))
|
|
select * from t1 where a is null or a < 0 or a > 1;
|
|
a
|
|
NULL
|
|
2
|
|
explain select * from t1 where a is null or a < 0 or a > 1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 pn,p2 ALL NULL NULL NULL NULL 2 87.50 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` is null) or (`test`.`t1`.`a` < 0) or (`test`.`t1`.`a` > 1))
|
|
drop table t1;
|
|
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, name VARCHAR(20))
|
|
DEFAULT CHARSET=latin1
|
|
PARTITION BY RANGE(id)
|
|
(PARTITION p0 VALUES LESS THAN (10),
|
|
PARTITION p1 VALUES LESS THAN (20),
|
|
PARTITION p2 VALUES LESS THAN (30));
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
SHOW TABLE STATUS;
|
|
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
|
|
t1 InnoDB 10 Dynamic 0 0 0 0 0 0 NULL NULL NULL NULL latin1_swedish_ci NULL partitioned
|
|
DROP TABLE t1;
|
|
create table t1 (a bigint unsigned)
|
|
partition by list (a)
|
|
(partition p0 values in (0-1));
|
|
ERROR HY000: Partition constant is out of partition function domain
|
|
create table t1 (a bigint unsigned)
|
|
partition by range (a)
|
|
(partition p0 values less than (10));
|
|
insert into t1 values (0xFFFFFFFFFFFFFFFF);
|
|
ERROR HY000: Table has no partition for value 18446744073709551615
|
|
drop table t1;
|
|
create table t1 (a int)
|
|
partition by list (a)
|
|
(partition `s1 s2` values in (0));
|
|
drop table t1;
|
|
create table t1 (a int)
|
|
partition by list (a)
|
|
(partition `7` values in (0));
|
|
drop table t1;
|
|
create table t1 (a int)
|
|
partition by list (a)
|
|
(partition `s1 s2 ` values in (0));
|
|
ERROR HY000: Incorrect partition name
|
|
create table t1 (a int)
|
|
partition by list (a)
|
|
subpartition by hash (a)
|
|
(partition p1 values in (0) (subpartition `p1 p2 `));
|
|
ERROR HY000: Incorrect partition name
|
|
CREATE TABLE t1 (a int)
|
|
PARTITION BY LIST (a)
|
|
(PARTITION p0 VALUES IN (NULL));
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY LIST (`a`)
|
|
(PARTITION p0 VALUES IN (NULL) ENGINE = InnoDB) */
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (a int)
|
|
PARTITION BY RANGE(a)
|
|
(PARTITION p0 VALUES LESS THAN (NULL));
|
|
ERROR HY000: Not allowed to use NULL value in VALUES LESS THAN
|
|
create table t1 (s1 int auto_increment primary key)
|
|
partition by list (s1)
|
|
(partition p1 values in (1),
|
|
partition p2 values in (2),
|
|
partition p3 values in (3));
|
|
insert into t1 values (null);
|
|
insert into t1 values (null);
|
|
insert into t1 values (null);
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
select auto_increment from information_schema.tables where table_name='t1';
|
|
AUTO_INCREMENT
|
|
4
|
|
select * from t1;
|
|
s1
|
|
1
|
|
2
|
|
3
|
|
drop table t1;
|
|
create table t1 (a int)
|
|
partition by key(a);
|
|
insert into t1 values (1);
|
|
create index inx1 on t1(a);
|
|
drop table t1;
|
|
create table t1 (a int)
|
|
partition by key (a)
|
|
(partition p0 engine = MERGE);
|
|
ERROR HY000: Engine cannot be used in partitioned tables
|
|
create table t1 (a varchar(1))
|
|
partition by key (a)
|
|
as select 'a';
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` varchar(1) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY KEY (a) */
|
|
drop table t1;
|
|
CREATE TABLE t1 (a int) PARTITION BY KEY(a);
|
|
INSERT into t1 values (1), (2);
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
SHOW TABLE STATUS;
|
|
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
|
|
t1 InnoDB 10 Dynamic 2 8192 16384 0 0 0 NULL NULL NULL NULL utf8mb4_0900_ai_ci NULL partitioned
|
|
DELETE from t1 where a = 1;
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
SHOW TABLE STATUS;
|
|
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
|
|
t1 InnoDB 10 Dynamic 1 16384 16384 0 0 0 NULL NULL NULL NULL utf8mb4_0900_ai_ci NULL partitioned
|
|
ALTER TABLE t1 OPTIMIZE PARTITION p0;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 optimize note Table does not support optimize on partitions. All partitions will be rebuilt and analyzed.
|
|
test.t1 optimize status OK
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
SHOW TABLE STATUS;
|
|
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
|
|
t1 InnoDB 10 Dynamic 1 16384 16384 0 0 0 NULL NULL NULL NULL utf8mb4_0900_ai_ci NULL partitioned
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (a int, index(a)) PARTITION BY KEY(a);
|
|
ALTER TABLE t1 DISABLE KEYS;
|
|
Warnings:
|
|
Note 1031 Table storage engine for 't1' doesn't have this option
|
|
ALTER TABLE t1 ENABLE KEYS;
|
|
Warnings:
|
|
Note 1031 Table storage engine for 't1' doesn't have this option
|
|
DROP TABLE t1;
|
|
create table t1 (a int)
|
|
partition by key (a);
|
|
REPAIR TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 repair status OK
|
|
OPTIMIZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
|
|
test.t1 optimize status OK
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
drop table t1;
|
|
drop procedure if exists mysqltest_1;
|
|
create table t1 (a int)
|
|
partition by list (a)
|
|
(partition p0 values in (0));
|
|
insert into t1 values (0);
|
|
create procedure mysqltest_1 ()
|
|
begin
|
|
begin
|
|
declare continue handler for sqlexception begin end;
|
|
update ignore t1 set a = 1 where a = 0;
|
|
end;
|
|
prepare stmt1 from 'alter table t1';
|
|
execute stmt1;
|
|
end//
|
|
call mysqltest_1()//
|
|
drop table t1;
|
|
drop procedure mysqltest_1;
|
|
create table t1 (a int, index(a))
|
|
partition by hash(a);
|
|
insert into t1 values (1),(2);
|
|
select * from t1 ORDER BY a DESC;
|
|
a
|
|
2
|
|
1
|
|
drop table t1;
|
|
create table t1 (a bigint unsigned not null, primary key(a))
|
|
partition by key (a)
|
|
partitions 10;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` bigint(20) unsigned NOT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY KEY (a)
|
|
PARTITIONS 10 */
|
|
insert into t1 values (18446744073709551615), (0xFFFFFFFFFFFFFFFE),
|
|
(18446744073709551613), (18446744073709551612);
|
|
select * from t1;
|
|
a
|
|
18446744073709551614
|
|
18446744073709551612
|
|
18446744073709551613
|
|
18446744073709551615
|
|
select * from t1 where a = 18446744073709551615;
|
|
a
|
|
18446744073709551615
|
|
delete from t1 where a = 18446744073709551615;
|
|
select * from t1;
|
|
a
|
|
18446744073709551614
|
|
18446744073709551612
|
|
18446744073709551613
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
num int(11) NOT NULL, cs int(11) NOT NULL)
|
|
PARTITION BY RANGE (num) SUBPARTITION BY HASH (
|
|
cs) SUBPARTITIONS 2 (PARTITION p_X VALUES LESS THAN MAXVALUE);
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
ALTER TABLE t1
|
|
REORGANIZE PARTITION p_X INTO (
|
|
PARTITION p_100 VALUES LESS THAN (100),
|
|
PARTITION p_X VALUES LESS THAN MAXVALUE
|
|
);
|
|
drop table t1;
|
|
set session sql_mode='';
|
|
CREATE TABLE t2 (
|
|
taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
id int(11) NOT NULL DEFAULT '0',
|
|
PRIMARY KEY (id,taken),
|
|
KEY taken (taken)
|
|
) DEFAULT CHARSET=latin1;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t2 VALUES
|
|
('2006-09-27 21:50:01',16421),
|
|
('2006-10-02 21:50:01',16421),
|
|
('2006-09-27 21:50:01',19092),
|
|
('2006-09-28 21:50:01',19092),
|
|
('2006-09-29 21:50:01',19092),
|
|
('2006-09-30 21:50:01',19092),
|
|
('2006-10-01 21:50:01',19092),
|
|
('2006-10-02 21:50:01',19092),
|
|
('2006-09-27 21:50:01',22589),
|
|
('2006-09-29 21:50:01',22589);
|
|
CREATE TABLE t1 (
|
|
id int(8) NOT NULL,
|
|
PRIMARY KEY (id)
|
|
) DEFAULT CHARSET=latin1;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t1 VALUES
|
|
(16421),
|
|
(19092),
|
|
(22589);
|
|
CREATE TABLE t4 (
|
|
taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
id int(11) NOT NULL DEFAULT '0',
|
|
PRIMARY KEY (id,taken),
|
|
KEY taken (taken)
|
|
) DEFAULT CHARSET=latin1
|
|
PARTITION BY RANGE (to_days(taken))
|
|
(
|
|
PARTITION p01 VALUES LESS THAN (732920) ,
|
|
PARTITION p02 VALUES LESS THAN (732950) ,
|
|
PARTITION p03 VALUES LESS THAN MAXVALUE ) ;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t4 select * from t2;
|
|
set @f_date='2006-09-28';
|
|
set @t_date='2006-10-02';
|
|
SELECT t1.id AS MyISAM_part
|
|
FROM t1
|
|
WHERE t1.id IN (
|
|
SELECT distinct id
|
|
FROM t4
|
|
WHERE taken BETWEEN @f_date AND date_add(@t_date, INTERVAL 1 DAY))
|
|
ORDER BY t1.id;
|
|
MyISAM_part
|
|
16421
|
|
19092
|
|
22589
|
|
drop table t1, t2, t4;
|
|
CREATE TABLE t1 (
|
|
taken datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
|
|
id int(11) NOT NULL DEFAULT '0',
|
|
status varchar(20) NOT NULL DEFAULT '',
|
|
PRIMARY KEY (id,taken)
|
|
) DEFAULT CHARSET=latin1
|
|
PARTITION BY RANGE (to_days(taken))
|
|
(
|
|
PARTITION p15 VALUES LESS THAN (732950) ,
|
|
PARTITION p16 VALUES LESS THAN MAXVALUE ) ;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t1 VALUES
|
|
('2006-09-27 21:50:01',22589,'Open'),
|
|
('2006-09-29 21:50:01',22589,'Verified');
|
|
DROP TABLE IF EXISTS t2;
|
|
Warnings:
|
|
Note 1051 Unknown table 'test.t2'
|
|
CREATE TABLE t2 (
|
|
id int(8) NOT NULL,
|
|
severity tinyint(4) NOT NULL DEFAULT '0',
|
|
priority tinyint(4) NOT NULL DEFAULT '0',
|
|
status varchar(20) DEFAULT NULL,
|
|
alien tinyint(4) NOT NULL
|
|
) DEFAULT CHARSET=latin1;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t2 VALUES
|
|
(22589,1,1,'Need Feedback',0);
|
|
SELECT t2.id FROM t2 WHERE t2.id IN (SELECT id FROM t1 WHERE status = 'Verified');
|
|
id
|
|
22589
|
|
drop table t1, t2;
|
|
create table t1 (c1 varchar(255),c2 tinyint,primary key(c1))
|
|
partition by key (c1) partitions 10 ;
|
|
insert into t1 values ('aaa','1') on duplicate key update c2 = c2 + 1;
|
|
insert into t1 values ('aaa','1') on duplicate key update c2 = c2 + 1;
|
|
select * from t1;
|
|
c1 c2
|
|
aaa 2
|
|
drop table t1;
|
|
create table t1 (s1 bigint) partition by list (s1) (partition p1 values in (-9223372036854775808));
|
|
drop table t1;
|
|
create table t1(a int auto_increment, b int, primary key (a, b))
|
|
partition by hash(b) partitions 2;
|
|
insert into t1 values (null, 1);
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
show table status;
|
|
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
|
|
t1 InnoDB 10 Dynamic 1 32768 32768 0 0 0 2 NULL NULL NULL utf8mb4_0900_ai_ci NULL partitioned
|
|
drop table t1;
|
|
create table t1(a int auto_increment primary key)
|
|
partition by key(a) partitions 2;
|
|
insert into t1 values (null), (null), (null);
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
show table status;
|
|
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
|
|
t1 InnoDB 10 Dynamic 3 10922 32768 0 0 0 4 NULL NULL NULL utf8mb4_0900_ai_ci NULL partitioned
|
|
drop table t1;
|
|
CREATE TABLE t1(a INT NOT NULL, b TINYBLOB, KEY(a))
|
|
PARTITION BY RANGE(a) ( PARTITION p0 VALUES LESS THAN (32));
|
|
INSERT INTO t1 VALUES (1, REPEAT('a', 10));
|
|
INSERT INTO t1 SELECT a + 1, b FROM t1;
|
|
INSERT INTO t1 SELECT a + 2, b FROM t1;
|
|
INSERT INTO t1 SELECT a + 4, b FROM t1;
|
|
INSERT INTO t1 SELECT a + 8, b FROM t1;
|
|
ALTER TABLE t1 ADD PARTITION (PARTITION p1 VALUES LESS THAN (64));
|
|
ALTER TABLE t1 DROP PARTITION p1;
|
|
DROP TABLE t1;
|
|
create table t (s1 int) partition by key (s1);
|
|
create trigger t_ad after delete on t for each row insert into t values (old.s1);
|
|
insert into t values (1);
|
|
drop table t;
|
|
create table t2 (b int);
|
|
create table t1 (b int)
|
|
PARTITION BY RANGE (t2.b) (
|
|
PARTITION p1 VALUES LESS THAN (10),
|
|
PARTITION p2 VALUES LESS THAN (20)
|
|
) select * from t2;
|
|
ERROR 42S22: Unknown column 't2.b' in 'partition function'
|
|
create table t1 (a int)
|
|
PARTITION BY RANGE (b) (
|
|
PARTITION p1 VALUES LESS THAN (10),
|
|
PARTITION p2 VALUES LESS THAN (20)
|
|
) select * from t2;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`b`)
|
|
(PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
|
|
PARTITION p2 VALUES LESS THAN (20) ENGINE = InnoDB) */
|
|
drop table t1, t2;
|
|
create table t1
|
|
(s1 timestamp on update current_timestamp, s2 int)
|
|
partition by key(s1) partitions 3;
|
|
insert into t1 values (null,null);
|
|
drop table t1;
|
|
create table t1 (
|
|
c0 int,
|
|
c1 bigint,
|
|
c2 set('sweet'),
|
|
key (c2,c1,c0),
|
|
key(c0)
|
|
) partition by hash (c0) partitions 5;
|
|
insert ignore into t1 set c0 = -6502262, c1 = 3992917, c2 = 35019;
|
|
insert ignore into t1 set c0 = 241221, c1 = -6862346, c2 = 56644;
|
|
select c1 from t1 group by (select c0 from t1 limit 1);
|
|
c1
|
|
-6862346
|
|
drop table t1;
|
|
CREATE TABLE t1(a int)
|
|
PARTITION BY RANGE (a) (
|
|
PARTITION p1 VALUES LESS THAN (10),
|
|
PARTITION p2 VALUES LESS THAN (20)
|
|
);
|
|
ALTER TABLE t1 OPTIMIZE PARTITION p1 EXTENDED;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXTENDED' at line 1
|
|
ALTER TABLE t1 ANALYZE PARTITION p1 EXTENDED;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXTENDED' at line 1
|
|
ALTER TABLE t1 ANALYZE PARTITION p1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
ALTER TABLE t1 CHECK PARTITION p1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
ALTER TABLE t1 REPAIR PARTITION p1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 repair status OK
|
|
ALTER TABLE t1 OPTIMIZE PARTITION p1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 optimize note Table does not support optimize on partitions. All partitions will be rebuilt and analyzed.
|
|
test.t1 optimize status OK
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (s1 BIGINT UNSIGNED)
|
|
PARTITION BY RANGE (s1) (
|
|
PARTITION p0 VALUES LESS THAN (0),
|
|
PARTITION p1 VALUES LESS THAN (1),
|
|
PARTITION p2 VALUES LESS THAN (18446744073709551615)
|
|
);
|
|
INSERT INTO t1 VALUES (0), (18446744073709551614);
|
|
INSERT INTO t1 VALUES (18446744073709551615);
|
|
ERROR HY000: Table has no partition for value 18446744073709551615
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (s1 BIGINT UNSIGNED)
|
|
PARTITION BY RANGE (s1) (
|
|
PARTITION p0 VALUES LESS THAN (0),
|
|
PARTITION p1 VALUES LESS THAN (1),
|
|
PARTITION p2 VALUES LESS THAN (18446744073709551614),
|
|
PARTITION p3 VALUES LESS THAN MAXVALUE
|
|
);
|
|
INSERT INTO t1 VALUES (-1), (0), (18446744073709551613),
|
|
(18446744073709551614), (18446744073709551615);
|
|
Warnings:
|
|
Warning 1264 Out of range value for column 's1' at row 1
|
|
SELECT * FROM t1;
|
|
s1
|
|
0
|
|
0
|
|
18446744073709551613
|
|
18446744073709551614
|
|
18446744073709551615
|
|
SELECT * FROM t1 WHERE s1 = 0;
|
|
s1
|
|
0
|
|
0
|
|
SELECT * FROM t1 WHERE s1 = 18446744073709551614;
|
|
s1
|
|
18446744073709551614
|
|
SELECT * FROM t1 WHERE s1 = 18446744073709551615;
|
|
s1
|
|
18446744073709551615
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (s1 BIGINT UNSIGNED)
|
|
PARTITION BY RANGE (s1) (
|
|
PARTITION p0 VALUES LESS THAN (0),
|
|
PARTITION p1 VALUES LESS THAN (1),
|
|
PARTITION p2 VALUES LESS THAN (18446744073709551615),
|
|
PARTITION p3 VALUES LESS THAN MAXVALUE
|
|
);
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1
|
|
(int_column INT, char_column CHAR(5),
|
|
PRIMARY KEY(char_column,int_column))
|
|
PARTITION BY KEY(char_column,int_column)
|
|
PARTITIONS 101;
|
|
INSERT INTO t1 (int_column, char_column) VALUES
|
|
( 39868 ,'zZZRW'),
|
|
( 545592 ,'zZzSD'),
|
|
( 4936 ,'zzzsT'),
|
|
( 9274 ,'ZzZSX'),
|
|
( 970185 ,'ZZzTN'),
|
|
( 786036 ,'zZzTO'),
|
|
( 37240 ,'zZzTv'),
|
|
( 313801 ,'zzzUM'),
|
|
( 782427 ,'ZZZva'),
|
|
( 907955 ,'zZZvP'),
|
|
( 453491 ,'zzZWV'),
|
|
( 756594 ,'ZZZXU'),
|
|
( 718061 ,'ZZzZH');
|
|
SELECT * FROM t1 ORDER BY char_column DESC;
|
|
int_column char_column
|
|
718061 ZZzZH
|
|
756594 ZZZXU
|
|
453491 zzZWV
|
|
907955 zZZvP
|
|
782427 ZZZva
|
|
313801 zzzUM
|
|
37240 zZzTv
|
|
786036 zZzTO
|
|
970185 ZZzTN
|
|
9274 ZzZSX
|
|
4936 zzzsT
|
|
545592 zZzSD
|
|
39868 zZZRW
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1(id MEDIUMINT NOT NULL AUTO_INCREMENT,
|
|
user CHAR(25), PRIMARY KEY(id))
|
|
PARTITION BY RANGE(id)
|
|
SUBPARTITION BY hash(id) subpartitions 2
|
|
(PARTITION pa1 values less than (10),
|
|
PARTITION pa2 values less than (20),
|
|
PARTITION pa11 values less than MAXVALUE);
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
|
|
`user` char(25) DEFAULT NULL,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`id`)
|
|
SUBPARTITION BY HASH (`id`)
|
|
SUBPARTITIONS 2
|
|
(PARTITION pa1 VALUES LESS THAN (10) ENGINE = InnoDB,
|
|
PARTITION pa2 VALUES LESS THAN (20) ENGINE = InnoDB,
|
|
PARTITION pa11 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
|
|
`createdDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`number` int,
|
|
PRIMARY KEY (`ID`, number)
|
|
)
|
|
PARTITION BY RANGE (number) (
|
|
PARTITION p0 VALUES LESS THAN (6),
|
|
PARTITION p1 VALUES LESS THAN (11)
|
|
);
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
create table t2 (
|
|
`ID` bigint(20),
|
|
`createdDate` TIMESTAMP,
|
|
`number` int
|
|
);
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t1 SET number=1;
|
|
insert into t2 select * from t1;
|
|
SELECT SLEEP(1);
|
|
SLEEP(1)
|
|
0
|
|
UPDATE t1 SET number=6;
|
|
select count(*) from t1, t2 where t1.createdDate = t2.createdDate;
|
|
count(*)
|
|
1
|
|
drop table t1, t2;
|
|
SET @orig_sql_mode = @@SQL_MODE;
|
|
SET SQL_MODE='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
|
|
Warnings:
|
|
Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
|
|
CREATE TABLE t1 (c1 INT)
|
|
PARTITION BY LIST(1 DIV c1) (
|
|
PARTITION p0 VALUES IN (NULL),
|
|
PARTITION p1 VALUES IN (1)
|
|
);
|
|
INSERT INTO t1 VALUES (0);
|
|
ERROR 22012: Division by 0
|
|
SELECT * FROM t1;
|
|
c1
|
|
TRUNCATE t1;
|
|
INSERT INTO t1 VALUES (NULL), (0), (1), (2);
|
|
ERROR 22012: Division by 0
|
|
SELECT * FROM t1;
|
|
c1
|
|
DROP TABLE t1;
|
|
SET SQL_MODE= @orig_sql_mode;
|
|
create table t1 (s1 int) partition by hash(s1) partitions 2;
|
|
create index i on t1 (s1);
|
|
insert into t1 values (1);
|
|
insert into t1 select s1 from t1;
|
|
insert into t1 select s1 from t1;
|
|
insert into t1 select s1 from t1 order by s1 desc;
|
|
select * from t1;
|
|
s1
|
|
1
|
|
1
|
|
1
|
|
1
|
|
1
|
|
1
|
|
1
|
|
1
|
|
drop table t1;
|
|
create table t1 (s1 int) partition by range(s1)
|
|
(partition pa1 values less than (10),
|
|
partition pa2 values less than MAXVALUE);
|
|
create index i on t1 (s1);
|
|
insert into t1 values (1);
|
|
insert into t1 select s1 from t1;
|
|
insert into t1 select s1 from t1;
|
|
insert into t1 select s1 from t1 order by s1 desc;
|
|
select * from t1;
|
|
s1
|
|
1
|
|
1
|
|
1
|
|
1
|
|
1
|
|
1
|
|
1
|
|
1
|
|
drop table t1;
|
|
create table t1 (s1 int) partition by range(s1)
|
|
(partition pa1 values less than (10),
|
|
partition pa2 values less than MAXVALUE);
|
|
create index i on t1 (s1);
|
|
insert into t1 values (20);
|
|
insert into t1 select s1 from t1;
|
|
insert into t1 select s1 from t1;
|
|
insert into t1 select s1 from t1 order by s1 desc;
|
|
select * from t1;
|
|
s1
|
|
20
|
|
20
|
|
20
|
|
20
|
|
20
|
|
20
|
|
20
|
|
20
|
|
drop table t1;
|
|
create table t1 (s1 int) partition by range(s1)
|
|
(partition pa1 values less than (10),
|
|
partition pa2 values less than MAXVALUE);
|
|
create index i on t1 (s1);
|
|
insert into t1 values (1), (2), (3), (4), (5), (6), (7), (8);
|
|
insert into t1 select s1 from t1;
|
|
insert into t1 select s1 from t1;
|
|
insert into t1 select s1 from t1;
|
|
insert into t1 select s1 from t1;
|
|
insert into t1 select s1 from t1 order by s1 desc;
|
|
insert into t1 select s1 from t1 where s1=3;
|
|
select count(*) from t1;
|
|
count(*)
|
|
288
|
|
drop table t1;
|
|
#
|
|
# Bug#42944: partition not pruned correctly
|
|
#
|
|
CREATE TABLE t1 (a int) PARTITION BY RANGE (a)
|
|
(PARTITION p0 VALUES LESS THAN (100),
|
|
PARTITION p1 VALUES LESS THAN (200),
|
|
PARTITION p2 VALUES LESS THAN (300),
|
|
PARTITION p3 VALUES LESS THAN MAXVALUE);
|
|
INSERT INTO t1 VALUES (10), (100), (200), (300), (400);
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
EXPLAIN SELECT * FROM t1 WHERE a>=200;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p2,p3 ALL NULL NULL NULL NULL 3 33.33 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` >= 200)
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 ( a INT, b INT, c INT, KEY bc(b, c) )
|
|
PARTITION BY KEY (a, b) PARTITIONS 3
|
|
;
|
|
INSERT INTO t1 VALUES
|
|
(17, 1, -8),
|
|
(3, 1, -7),
|
|
(23, 1, -6),
|
|
(22, 1, -5),
|
|
(11, 1, -4),
|
|
(21, 1, -3),
|
|
(19, 1, -2),
|
|
(30, 1, -1),
|
|
(20, 1, 1),
|
|
(16, 1, 2),
|
|
(18, 1, 3),
|
|
(9, 1, 4),
|
|
(15, 1, 5),
|
|
(28, 1, 6),
|
|
(29, 1, 7),
|
|
(25, 1, 8),
|
|
(10, 1, 9),
|
|
(13, 1, 10),
|
|
(27, 1, 11),
|
|
(24, 1, 12),
|
|
(12, 1, 13),
|
|
(26, 1, 14),
|
|
(14, 1, 15)
|
|
;
|
|
SELECT b, c FROM t1 WHERE b = 1 GROUP BY b, c;
|
|
b c
|
|
1 -8
|
|
1 -7
|
|
1 -6
|
|
1 -5
|
|
1 -4
|
|
1 -3
|
|
1 -2
|
|
1 -1
|
|
1 1
|
|
1 2
|
|
1 3
|
|
1 4
|
|
1 5
|
|
1 6
|
|
1 7
|
|
1 8
|
|
1 9
|
|
1 10
|
|
1 11
|
|
1 12
|
|
1 13
|
|
1 14
|
|
1 15
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
EXPLAIN
|
|
SELECT b, c FROM t1 WHERE b = 1 GROUP BY b, c;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p0,p1,p2 ref bc bc 5 const 23 100.00 Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where (`test`.`t1`.`b` = 1) group by `test`.`t1`.`b`,`test`.`t1`.`c`
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug #45807: crash accessing partitioned table and sql_mode
|
|
# contains ONLY_FULL_GROUP_BY
|
|
# Bug#46923: select count(*) from partitioned table fails with
|
|
# ONLY_FULL_GROUP_BY
|
|
#
|
|
SET SESSION SQL_MODE='ONLY_FULL_GROUP_BY';
|
|
CREATE TABLE t1(id INT,KEY(id))
|
|
PARTITION BY HASH(id) PARTITIONS 2;
|
|
SELECT COUNT(*) FROM t1;
|
|
COUNT(*)
|
|
0
|
|
DROP TABLE t1;
|
|
SET SESSION SQL_MODE=DEFAULT;
|
|
#
|
|
# Bug#46198: Hang after failed ALTER TABLE on partitioned table.
|
|
#
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1 (s1 INT PRIMARY KEY) PARTITION BY HASH(s1);
|
|
LOCK TABLES t1 WRITE, t1 b READ;
|
|
UNLOCK TABLES;
|
|
ALTER TABLE t1 DROP PARTITION p1;
|
|
ERROR HY000: DROP PARTITION can only be used on RANGE/LIST partitions
|
|
SELECT * FROM t1;
|
|
s1
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (s1 VARCHAR(5) PRIMARY KEY) PARTITION BY KEY(s1);
|
|
LOCK TABLES t1 WRITE, t1 b READ;
|
|
UNLOCK TABLES;
|
|
ALTER TABLE t1 ADD COLUMN (s3 VARCHAR(5) UNIQUE);
|
|
ERROR HY000: A UNIQUE INDEX must include all columns in the table's partitioning function
|
|
SELECT * FROM t1;
|
|
s1
|
|
DROP TABLE t1;
|
|
#
|
|
# BUG#51868 - crash with myisam_use_mmap and partitioned myisam tables
|
|
#
|
|
SET GLOBAL myisam_use_mmap=1;
|
|
CREATE TABLE t1(a INT) PARTITION BY HASH(a) PARTITIONS 1;
|
|
INSERT INTO t1 VALUES(0);
|
|
FLUSH TABLE t1;
|
|
TRUNCATE TABLE t1;
|
|
INSERT INTO t1 VALUES(0);
|
|
DROP TABLE t1;
|
|
SET GLOBAL myisam_use_mmap=default;
|
|
End of 5.1 tests
|
|
# Start of 5.5 tests
|
|
#
|
|
# BUG#55385: UPDATE statement throws an error, but still updates
|
|
# the table entries
|
|
CREATE TABLE t1_part (
|
|
partkey int,
|
|
nokey int
|
|
) PARTITION BY LINEAR HASH(partkey) PARTITIONS 3;
|
|
INSERT INTO t1_part VALUES (1, 1) , (10, 10);
|
|
CREATE VIEW v1 AS SELECT * FROM t1_part;
|
|
|
|
# Should be (1,1),(10,10)
|
|
SELECT * FROM t1_part;
|
|
partkey nokey
|
|
1 1
|
|
10 10
|
|
|
|
# Case 1
|
|
# Update is refused because partitioning key is updated
|
|
UPDATE t1_part AS A NATURAL JOIN t1_part B SET A.partkey = 2, B.nokey = 3;
|
|
ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'.
|
|
UPDATE t1_part AS A NATURAL JOIN t1_part B SET A.nokey = 2, B.partkey = 3;
|
|
ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'.
|
|
|
|
# Case 2
|
|
# Like 1, but partition accessed through a view
|
|
UPDATE t1_part AS A NATURAL JOIN v1 as B SET A.nokey = 2 , B.partkey = 3;
|
|
ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'.
|
|
UPDATE v1 AS A NATURAL JOIN t1_part as B SET A.nokey = 2 , B.partkey = 3;
|
|
ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'.
|
|
|
|
# Should be (1,1),(10,10)
|
|
SELECT * FROM t1_part;
|
|
partkey nokey
|
|
1 1
|
|
10 10
|
|
|
|
# Case 3
|
|
# Update is accepted because partitioning key is not updated
|
|
UPDATE t1_part AS A NATURAL JOIN t1_part B SET A.nokey = 2 , B.nokey = 3;
|
|
|
|
# Should be (1,3),(10,3)
|
|
SELECT * FROM t1_part;
|
|
partkey nokey
|
|
1 3
|
|
10 3
|
|
|
|
DROP VIEW v1;
|
|
DROP TABLE t1_part;
|
|
# Moved from lock.test
|
|
# Separate case for partitioned tables is important
|
|
# because each partition has an own thr_lock object.
|
|
create table t1 (i int) partition by list (i)
|
|
(partition p0 values in (1),
|
|
partition p1 values in (2,3),
|
|
partition p2 values in (4,5));
|
|
lock tables t1 write, t1 as a read, t1 as b read;
|
|
alter table t1 add column j int;
|
|
unlock tables;
|
|
drop table t1;
|
|
# End of 5.5 tests
|
|
CREATE TABLE t1(a INT PRIMARY KEY) PARTITION BY LINEAR KEY (a);
|
|
CREATE ALGORITHM=TEMPTABLE VIEW vtmp AS
|
|
SELECT 1 FROM t1 AS t1_0 JOIN t1 ON t1_0.a LIKE (SELECT 1 FROM t1);
|
|
SELECT * FROM vtmp;
|
|
1
|
|
DROP VIEW vtmp;
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#13580775 ASSERTION FAILED: RECORD_LENGTH == M_RECORD_LENGTH,
|
|
# FILE FILESORT_UTILS.CC
|
|
#
|
|
CREATE TABLE t1 (
|
|
a INT PRIMARY KEY,
|
|
b INT,
|
|
c CHAR(1),
|
|
d INT,
|
|
KEY (c,d)
|
|
) PARTITION BY KEY () PARTITIONS 1;
|
|
INSERT INTO t1 VALUES (1,1,'a',1), (2,2,'a',1);
|
|
SELECT 1 FROM t1 WHERE 1 IN
|
|
(SELECT group_concat(b)
|
|
FROM t1
|
|
WHERE c > ST_geomfromtext('point(1 1)')
|
|
GROUP BY b
|
|
);
|
|
ERROR HY000: Incorrect arguments to >
|
|
DROP TABLE t1;
|
|
#
|
|
# WL#5855 (EXPLAIN FORMAT=JSON) partition test
|
|
#
|
|
CREATE TABLE t1 (c1 int(11) DEFAULT NULL, KEY c1 (c1))
|
|
PARTITION BY RANGE (c1)
|
|
(PARTITION a VALUES LESS THAN (10),
|
|
PARTITION b VALUES LESS THAN (100),
|
|
PARTITION c VALUES LESS THAN MAXVALUE);
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
|
|
EXPLAIN SELECT c1 FROM t1 WHERE (c1 > 2 AND c1 < 15);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 a,b range c1 c1 5 NULL 12 100.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where ((`test`.`t1`.`c1` > 2) and (`test`.`t1`.`c1` < 15))
|
|
EXPLAIN SELECT c1 FROM t1 WHERE (c1 > 2 AND c1 < 15);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 a,b range c1 c1 5 NULL 12 100.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where ((`test`.`t1`.`c1` > 2) and (`test`.`t1`.`c1` < 15))
|
|
EXPLAIN FORMAT=JSON SELECT c1 FROM t1 WHERE (c1 > 2 AND c1 < 15);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "2.66"
|
|
},
|
|
"table": {
|
|
"table_name": "t1",
|
|
"partitions": [
|
|
"a",
|
|
"b"
|
|
],
|
|
"access_type": "range",
|
|
"possible_keys": [
|
|
"c1"
|
|
],
|
|
"key": "c1",
|
|
"used_key_parts": [
|
|
"c1"
|
|
],
|
|
"key_length": "5",
|
|
"rows_examined_per_scan": 12,
|
|
"rows_produced_per_join": 12,
|
|
"filtered": "100.00",
|
|
"using_index": true,
|
|
"cost_info": {
|
|
"read_cost": "1.46",
|
|
"eval_cost": "1.20",
|
|
"prefix_cost": "2.66",
|
|
"data_read_per_join": "96"
|
|
},
|
|
"used_columns": [
|
|
"c1"
|
|
],
|
|
"attached_condition": "((`test`.`t1`.`c1` > 2) and (`test`.`t1`.`c1` < 15))"
|
|
}
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where ((`test`.`t1`.`c1` > 2) and (`test`.`t1`.`c1` < 15))
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#19584181: CRASH AFTER DDL RETURNS WARNINGS - CORRUPT ERROR_HANDLER
|
|
#
|
|
SET sql_mode= 'STRICT_TRANS_TABLES';
|
|
Warnings:
|
|
Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
|
|
CREATE TABLE t1 (d TIME)
|
|
PARTITION BY RANGE COLUMNS (d)
|
|
(PARTITION p0 VALUES LESS THAN ('2000-01-01'),
|
|
PARTITION p1 VALUES LESS THAN ('2040-01-01'));
|
|
ERROR HY000: Partition column values of incorrect type
|
|
SELECT @@sql_mode;
|
|
@@sql_mode
|
|
STRICT_TRANS_TABLES
|
|
SET sql_mode= DEFAULT;
|
|
#
|
|
# ASSERTION FAILED: !THD->IS_ERROR() PARTITIONS + OUT OF RANGE VALUES
|
|
#
|
|
CREATE TABLE t1 (a int PRIMARY KEY)
|
|
PARTITION BY LINEAR KEY(a) PARTITIONS 2;
|
|
SELECT 1 FROM t1 WHERE EXP(10000);
|
|
ERROR 22003: DOUBLE value is out of range in 'exp(10000)'
|
|
DROP TABLE t1;
|
|
create table t1 (a int)
|
|
partition by range (a)
|
|
subpartition by hash (a)
|
|
(partition p0 VALUES LESS THAN (1) DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp'
|
|
(SUBPARTITION subpart00, SUBPARTITION subpart01));
|
|
# Checking if file exists before alter
|
|
ALTER TABLE t1 REORGANIZE PARTITION p0 INTO
|
|
(partition p1 VALUES LESS THAN (1) DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp'
|
|
(SUBPARTITION subpart10, SUBPARTITION subpart11),
|
|
partition p2 VALUES LESS THAN (2) DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp'
|
|
(SUBPARTITION subpart20, SUBPARTITION subpart21));
|
|
# Checking if file exists after alter
|
|
drop table t1;
|
|
set innodb_strict_mode=off;
|
|
Warnings:
|
|
Warning 1618 <INDEX DIRECTORY> option ignored
|
|
set @org_mode=@@sql_mode;
|
|
set @@sql_mode='NO_DIR_IN_CREATE';
|
|
select @@sql_mode;
|
|
@@sql_mode
|
|
NO_DIR_IN_CREATE
|
|
create table t1 (i int )
|
|
partition by range (i)
|
|
(
|
|
partition p01 values less than (1000)
|
|
data directory='/not/existing'
|
|
index directory='/not/existing'
|
|
);
|
|
Warnings:
|
|
Warning 1618 <DATA DIRECTORY> option ignored
|
|
Warning 1618 <INDEX DIRECTORY> option ignored
|
|
show create table t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`i` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`i`)
|
|
(PARTITION p01 VALUES LESS THAN (1000) ENGINE = InnoDB) */
|
|
DROP TABLE t1, t2;
|
|
set @@sql_mode=@org_mode;
|
|
CREATE TABLE t1(a INT)
|
|
PARTITION BY KEY (a)
|
|
(PARTITION p0 DATA DIRECTORY 'MYSQLD_DATADIR/test');
|
|
ERROR HY000: Incorrect path value: 'MYSQLD_DATADIR/test'
|
|
CREATE TABLE t1(a INT)
|
|
PARTITION BY KEY (a)
|
|
(PARTITION p0 INDEX DIRECTORY 'MYSQLD_DATADIR/test');
|
|
ERROR HY000: Incorrect path value: 'MYSQLD_DATADIR/test'
|
|
CREATE TABLE ts (id INT, purchased DATE)
|
|
PARTITION BY RANGE(YEAR(purchased))
|
|
SUBPARTITION BY HASH(TO_DAYS(purchased)) (
|
|
PARTITION p0 VALUES LESS THAN (1990) (
|
|
SUBPARTITION s0a
|
|
DATA DIRECTORY = 'MYSQLD_DATADIR/test',
|
|
SUBPARTITION s0b
|
|
DATA DIRECTORY = 'MYSQLD_DATADIR/test'
|
|
));
|
|
ERROR HY000: Incorrect path value: 'MYSQLD_DATADIR/test'
|
|
CREATE TABLE ts (id INT, purchased DATE)
|
|
PARTITION BY RANGE(YEAR(purchased))
|
|
SUBPARTITION BY HASH(TO_DAYS(purchased)) (
|
|
PARTITION p0 VALUES LESS THAN (1990) (
|
|
SUBPARTITION s0a
|
|
INDEX DIRECTORY = 'MYSQLD_DATADIR/test',
|
|
SUBPARTITION s0b
|
|
INDEX DIRECTORY = 'MYSQLD_DATADIR/test'
|
|
));
|
|
ERROR HY000: Incorrect path value: 'MYSQLD_DATADIR/test'
|
|
DROP TABLE IF EXISTS `example`;
|
|
CREATE TABLE `example` (
|
|
`ID_EXAMPLE` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`DESCRIPTION` varchar(30) NOT NULL,
|
|
`LEVEL` smallint(5) unsigned DEFAULT NULL,
|
|
PRIMARY KEY (`ID_EXAMPLE`)
|
|
)
|
|
PARTITION BY HASH(ID_EXAMPLE)(
|
|
PARTITION p0 DATA DIRECTORY = 'MYSQLTEST_VARDIR/p0Data',
|
|
PARTITION p1 DATA DIRECTORY = 'MYSQLTEST_VARDIR/p1Data',
|
|
PARTITION p2 DATA DIRECTORY = 'MYSQLTEST_VARDIR/p2Data',
|
|
PARTITION p3 DATA DIRECTORY = 'MYSQLTEST_VARDIR/p3Data'
|
|
);
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
# Checking that InnoDB data files are in test db and data dir
|
|
DROP TABLE example;
|
|
#
|
|
# Bug#18513130 STRANGE INTERACTION OF SQL_MODE=ANSI AND PARTITION
|
|
#
|
|
SET @@SQL_MODE= 'ANSI_QUOTES';
|
|
CREATE TABLE t1(col1 INT, col2 DATE)
|
|
ENGINE=INNODB
|
|
PARTITION BY RANGE(YEAR("col2"))
|
|
SUBPARTITION BY HASH(TO_DAYS("col2"))(
|
|
PARTITION p0 VALUES LESS THAN (1990)(
|
|
SUBPARTITION s0,
|
|
SUBPARTITION s1 tablespace=`innodb_file_per_table`
|
|
),
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE(
|
|
SUBPARTITION s2,
|
|
SUBPARTITION s3 tablespace="innodb_file_per_table"
|
|
)
|
|
);
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE "t1" (
|
|
"col1" int(11) DEFAULT NULL,
|
|
"col2" date DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (year("col2"))
|
|
SUBPARTITION BY HASH (to_days("col2"))
|
|
(PARTITION p0 VALUES LESS THAN (1990)
|
|
(SUBPARTITION s0 TABLESPACE = "innodb_file_per_table" ENGINE = InnoDB,
|
|
SUBPARTITION s1 TABLESPACE = "innodb_file_per_table" ENGINE = InnoDB),
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE
|
|
(SUBPARTITION s2 TABLESPACE = "innodb_file_per_table" ENGINE = InnoDB,
|
|
SUBPARTITION s3 TABLESPACE = "innodb_file_per_table" ENGINE = InnoDB)) */
|
|
SELECT partition_expression FROM information_schema.partitions
|
|
WHERE table_schema = 'test' AND table_name = 't1';
|
|
PARTITION_EXPRESSION
|
|
year(`col2`)
|
|
year(`col2`)
|
|
year(`col2`)
|
|
year(`col2`)
|
|
SET @@SQL_MODE= DEFAULT;
|
|
FLUSH TABLES;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`col1` int(11) DEFAULT NULL,
|
|
`col2` date DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (year(`col2`))
|
|
SUBPARTITION BY HASH (to_days(`col2`))
|
|
(PARTITION p0 VALUES LESS THAN (1990)
|
|
(SUBPARTITION s0 TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB,
|
|
SUBPARTITION s1 TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB),
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE
|
|
(SUBPARTITION s2 TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB,
|
|
SUBPARTITION s3 TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB)) */
|
|
SELECT partition_expression FROM information_schema.partitions
|
|
WHERE table_schema = 'test' AND table_name = 't1';
|
|
PARTITION_EXPRESSION
|
|
year(`col2`)
|
|
year(`col2`)
|
|
year(`col2`)
|
|
year(`col2`)
|
|
SET @@SQL_QUOTE_SHOW_CREATE= 0;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE t1 (
|
|
col1 int(11) DEFAULT NULL,
|
|
col2 date DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (year(col2))
|
|
SUBPARTITION BY HASH (to_days(col2))
|
|
(PARTITION p0 VALUES LESS THAN (1990)
|
|
(SUBPARTITION s0 TABLESPACE = innodb_file_per_table ENGINE = InnoDB,
|
|
SUBPARTITION s1 TABLESPACE = innodb_file_per_table ENGINE = InnoDB),
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE
|
|
(SUBPARTITION s2 TABLESPACE = innodb_file_per_table ENGINE = InnoDB,
|
|
SUBPARTITION s3 TABLESPACE = innodb_file_per_table ENGINE = InnoDB)) */
|
|
SELECT partition_expression FROM information_schema.partitions
|
|
WHERE table_schema = 'test' AND table_name = 't1';
|
|
PARTITION_EXPRESSION
|
|
year(`col2`)
|
|
year(`col2`)
|
|
year(`col2`)
|
|
year(`col2`)
|
|
SET @@SQL_QUOTE_SHOW_CREATE= 1;
|
|
DROP TABLE t1;
|
|
#
|
|
# Test max partitioning and subpartition expression length
|
|
#
|
|
CREATE TABLE t1 (a INT) PARTITION BY HASH(ALMOST_TOO_LONG_EXPR);
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (a INT) PARTITION BY HASH(TOO_LONG_EXPR);
|
|
ERROR HY000: Partitioning expression is too long.
|
|
# Check that the limit is on number of characters and not on bytes
|
|
CREATE TABLE t1 (`å` INT) PARTITION BY HASH(ALMOST_TOO_LONG_EXPR);
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (`å` INT) PARTITION BY HASH(TOO_LONG_EXPR);
|
|
ERROR HY000: Partitioning expression is too long.
|
|
# Repeat the same tests for subpartitioning expressions.
|
|
CREATE TABLE t1 (b INT, a INT) PARTITION BY LIST (b) SUBPARTITION BY HASH(ALMOST_TOO_LONG_EXPR) (PARTITION p1 VALUES IN (1));
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (b INT, a INT) PARTITION BY LIST (b) SUBPARTITION BY HASH(TOO_LONG_EXPR) (PARTITION p1 VALUES IN (1));
|
|
ERROR HY000: Partitioning expression is too long.
|
|
# Again the limit is on number of characters and not on bytes
|
|
CREATE TABLE t1 (b INT, `å` INT) PARTITION BY LIST (b) SUBPARTITION BY HASH(ALMOST_TOO_LONG_EXPR) (PARTITION p1 VALUES IN (1));
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (b INT, `å` INT) PARTITION BY LIST (b) SUBPARTITION BY HASH(TOO_LONG_EXPR) (PARTITION p1 VALUES IN (1));
|
|
ERROR HY000: Partitioning expression is too long.
|
|
#
|
|
# Bug #27005020: UBSAN: GET_PART_ITER_FOR_INTERVAL_VIA_WALKING - SIGNED INTEGER OVERFLOW
|
|
#
|
|
CREATE TABLE t (a BIGINT NOT NULL)
|
|
PARTITION BY KEY(a) PARTITIONS 2;
|
|
INSERT INTO t VALUES(0);
|
|
SELECT 1 FROM t WHERE a = 0x8000000000000000;
|
|
1
|
|
DROP TABLE t;
|
|
#
|
|
# Bug#28573894 ALTER PARTITIONED TABLE ADD AUTO_INCREMENT DIFF RESULT
|
|
#
|
|
CREATE TABLE t (a VARCHAR(10) NOT NULL,b INT,PRIMARY KEY (b)) ENGINE=INNODB
|
|
PARTITION BY RANGE (b)
|
|
(PARTITION pa VALUES LESS THAN (2),
|
|
PARTITION pb VALUES LESS THAN (20),
|
|
PARTITION pc VALUES LESS THAN (30),
|
|
PARTITION pd VALUES LESS THAN (40));
|
|
INSERT INTO t
|
|
VALUES('A',0),('B',1),('C',2),('D',3),('E',4),('F',5),('G',25),('H',35);
|
|
ALTER TABLE t ADD COLUMN r INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD UNIQUE
|
|
KEY (r,b), ALGORITHM=INPLACE, LOCK=SHARED;
|
|
SELECT * FROM t;
|
|
a b r
|
|
A 0 1
|
|
B 1 2
|
|
C 2 3
|
|
D 3 4
|
|
E 4 5
|
|
F 5 6
|
|
G 25 7
|
|
H 35 8
|
|
DROP TABLE t;
|
|
#
|
|
# Bug#29317007: INIT_LEX_WITH_SINGLE_TABLE() CONFUSES TABLE
|
|
# AND SCHEMA NAME
|
|
#
|
|
CREATE SCHEMA tables;
|
|
CREATE TABLE tables.mysql(a INT);
|
|
ALTER TABLE tables.mysql PARTITION BY HASH (a);
|
|
DROP SCHEMA tables;
|
|
#
|
|
# Bug#29058369: MEMORY ACCESS ERROR WITH ALTER TABLE CHARACTER CHANGE.
|
|
#
|
|
# Test case 1
|
|
CREATE TABLE t1(c1 INT, c2 CHAR) PARTITION BY HASH(c1) PARTITIONS 50;
|
|
SELECT MBRTOUCHES(a.c2, b.c2) FROM t1 AS a JOIN t1 AS b;
|
|
MBRTOUCHES(a.c2, b.c2)
|
|
ALTER TABLE t1 CONVERT TO CHARACTER SET latin1;
|
|
DROP TABLE t1;
|
|
# Test case 2
|
|
CREATE TABLE t1 (s1 INT PRIMARY KEY) PARTITION BY HASH(s1);
|
|
LOCK TABLES t1 WRITE, t1 b READ;
|
|
UNLOCK TABLES;
|
|
ALTER TABLE t1 DROP PARTITION p1;
|
|
ERROR HY000: DROP PARTITION can only be used on RANGE/LIST partitions
|
|
ALTER TABLE t1 CONVERT TO CHARACTER SET latin1;
|
|
DROP TABLE t1;
|
|
# Test case with subpartitions
|
|
CREATE TABLE t1 (c1 INT NOT NULL, c2 INT)
|
|
PARTITION BY RANGE (c1) PARTITIONS 3 SUBPARTITION BY KEY (c2) (
|
|
PARTITION p1 VALUES LESS THAN (200) (
|
|
SUBPARTITION p11,
|
|
SUBPARTITION p12,
|
|
SUBPARTITION p13),
|
|
PARTITION p2 VALUES LESS THAN (600) (
|
|
SUBPARTITION p21,
|
|
SUBPARTITION p22,
|
|
SUBPARTITION p23),
|
|
PARTITION p3 VALUES LESS THAN (1800) (
|
|
SUBPARTITION p31,
|
|
SUBPARTITION p32,
|
|
SUBPARTITION p33));
|
|
SELECT MBRTOUCHES(a.c1, b.c2) FROM t1 AS a JOIN t1 AS b;
|
|
MBRTOUCHES(a.c1, b.c2)
|
|
ALTER TABLE t1 CONVERT TO CHARACTER SET latin1;
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#29541665 "SWAPPING COLUMNS USED IN PARTITIONED EXPRESSION BY RENAMING BREAKS PARTITIONING".
|
|
#
|
|
#
|
|
# Test case mimicking the original one.
|
|
CREATE TABLE t1 (i INT, j INT) PARTITION BY RANGE(i) (PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN MAXVALUE);
|
|
INSERT INTO t1 (i, j) VALUES (-1, 1);
|
|
ALTER TABLE t1 CHANGE COLUMN i j INT, CHANGE COLUMN j i INT;
|
|
ERROR HY000: Column 'i' has a partitioning function dependency and cannot be dropped or renamed.
|
|
# If the above ALTER succeeds the partitioning expression should
|
|
# stay unchanged. Failed ALTER should not change it either.
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`i` int(11) DEFAULT NULL,
|
|
`j` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`i`)
|
|
(PARTITION p0 VALUES LESS THAN (0) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
|
|
# There should be only single row belonging either
|
|
# to p0 (iff i=-1) or p1 (iff i=1).
|
|
SELECT i, j FROM t1 PARTITION (p0);
|
|
i j
|
|
-1 1
|
|
SELECT i, j FROM t1 PARTITION (p1);
|
|
i j
|
|
DROP TABLE t1;
|
|
# Similar test case involving column replacement.
|
|
CREATE TABLE t1 (i INT, j INT) PARTITION BY RANGE(i) (PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN MAXVALUE);
|
|
INSERT INTO t1 (i, j) VALUES (-1, 1);
|
|
ALTER TABLE t1 DROP COLUMN i, ADD COLUMN i INT DEFAULT 1;
|
|
ERROR HY000: Column 'i' has a partitioning function dependency and cannot be dropped or renamed.
|
|
# If the above ALTER succeeds the partitioning expression should
|
|
# stay unchanged. Failed ALTER should not change it either.
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`i` int(11) DEFAULT NULL,
|
|
`j` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`i`)
|
|
(PARTITION p0 VALUES LESS THAN (0) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
|
|
# There should be only single row belonging either
|
|
# to p0 (iff i=-1) or p1 (iff i=1).
|
|
SELECT i, j FROM t1 PARTITION (p0);
|
|
i j
|
|
-1 1
|
|
SELECT i, j FROM t1 PARTITION (p1);
|
|
i j
|
|
DROP TABLE t1;
|
|
#
|
|
# More systematic test coverage.
|
|
#
|
|
#
|
|
# 1) Renaming column on which partitioning expression depends.
|
|
#
|
|
# 1.1) Renaming column on which partitioning expression depends
|
|
# is prohibited (for both RENAME COLUMN and CHANGE syntaxes).
|
|
CREATE TABLE t1 (i INT, j INT) PARTITION BY RANGE(i+1) (PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN MAXVALUE);
|
|
ALTER TABLE t1 RENAME COLUMN i TO k;
|
|
ERROR HY000: Column 'i' has a partitioning function dependency and cannot be dropped or renamed.
|
|
ALTER TABLE t1 CHANGE COLUMN i k INT;
|
|
ERROR HY000: Column 'i' has a partitioning function dependency and cannot be dropped or renamed.
|
|
# Even if substitute for renamed column is provided.
|
|
ALTER TABLE t1 RENAME COLUMN i TO j, RENAME COLUMN j TO i;
|
|
ERROR HY000: Column 'i' has a partitioning function dependency and cannot be dropped or renamed.
|
|
# Also works for case of degenerate expression like COLUMN partitioning.
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (i INT) PARTITION BY LIST COLUMNS (i) (PARTITION p0 VALUES IN (-2,-1), PARTITION p1 VALUES IN (0, 1, 2));
|
|
ALTER TABLE t1 RENAME COLUMN i TO k;
|
|
ERROR HY000: Column 'i' has a partitioning function dependency and cannot be dropped or renamed.
|
|
#
|
|
# 1.2) It is OK to rename such columns if partitioning is removed by
|
|
# the same ALTER TABLE.
|
|
ALTER TABLE t1 RENAME COLUMN i TO k REMOVE PARTITIONING;
|
|
DROP TABLE t1;
|
|
#
|
|
# 1.3) Renaming such columns is also OK if partitioned expression
|
|
# is updated by the same ALTER TABLE.
|
|
CREATE TABLE t1 (i INT, j INT) PARTITION BY RANGE(i) (PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN MAXVALUE);
|
|
INSERT INTO t1 (i, j) VALUES (-1, 1);
|
|
ALTER TABLE t1 RENAME COLUMN i TO k PARTITION BY RANGE(k) (PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN MAXVALUE);
|
|
# If new expression uses wrong column name an appropriate error
|
|
# is reported.
|
|
ALTER TABLE t1 RENAME COLUMN k TO l PARTITION BY RANGE(m) (PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN MAXVALUE);
|
|
ERROR 42S22: Unknown column 'm' in 'partition function'
|
|
#
|
|
# 1.4) Such renames combined with partitioning function update should
|
|
# move rows between partitions as appropriate.
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`k` int(11) DEFAULT NULL,
|
|
`j` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`k`)
|
|
(PARTITION p0 VALUES LESS THAN (0) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
|
|
SELECT k, j FROM t1 PARTITION (p0);
|
|
k j
|
|
-1 1
|
|
SELECT k, j FROM t1 PARTITION (p1);
|
|
k j
|
|
# Partitioning expression is kept the same, but value in its column is changed by rename.
|
|
ALTER TABLE t1 RENAME COLUMN k TO j, RENAME COLUMN j TO k PARTITION BY RANGE(k) (PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN MAXVALUE);
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`j` int(11) DEFAULT NULL,
|
|
`k` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`k`)
|
|
(PARTITION p0 VALUES LESS THAN (0) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
|
|
SELECT k, j FROM t1 PARTITION (p0);
|
|
k j
|
|
SELECT k, j FROM t1 PARTITION (p1);
|
|
k j
|
|
1 -1
|
|
# Because of this such renames can't be done in-place.
|
|
ALTER TABLE t1 RENAME COLUMN k TO j, RENAME COLUMN j TO k, ALGORITHM=INPLACE PARTITION BY RANGE(k) (PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN MAXVALUE);
|
|
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
|
|
DROP TABLE t1;
|
|
#
|
|
# 1.5) We also allow renaming columns which are used by partitioning function
|
|
# implicitly, because it is specified by "PARTITION BY KEY ()" (note empty
|
|
# column list) clause. In this case partitioning function is defined by
|
|
# the primary key, and the latter is automagically updated when column
|
|
# is renamed.
|
|
#
|
|
# We test scenario similar to one from the bug report.
|
|
CREATE TABLE t1 (i INT, j INT, PRIMARY KEY (i)) PARTITION BY KEY () PARTITIONS 2 (PARTITION p0, PARTITION p1);
|
|
INSERT INTO t1 (i, j) VALUES (-1, 1);
|
|
SELECT i, j FROM t1 PARTITION (p0);
|
|
i j
|
|
SELECT i, j FROM t1 PARTITION (p1);
|
|
i j
|
|
-1 1
|
|
ALTER TABLE t1 RENAME COLUMN i TO j, RENAME COLUMN j TO i;
|
|
# PRIMARY KEY should contain use new column name.
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`j` int(11) NOT NULL,
|
|
`i` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`j`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY KEY ()
|
|
(PARTITION p0 ENGINE = InnoDB,
|
|
PARTITION p1 ENGINE = InnoDB) */
|
|
# Hence the row should not be moved between partitions.
|
|
SELECT i, j FROM t1 PARTITION (p0);
|
|
i j
|
|
SELECT i, j FROM t1 PARTITION (p1);
|
|
i j
|
|
1 -1
|
|
DROP TABLE t1;
|
|
#
|
|
# However, renaming of columns is not allowed if their are mentioned
|
|
# explicitly in the KEY list, as partitioning function is not updated
|
|
# automagically in this case.
|
|
CREATE TABLE t1 (i INT, j INT, PRIMARY KEY (i, j)) PARTITION BY KEY (i) PARTITIONS 2 (PARTITION p0, PARTITION p1);
|
|
INSERT INTO t1 (i, j) VALUES (-1, 1);
|
|
SELECT i, j FROM t1 PARTITION (p0);
|
|
i j
|
|
SELECT i, j FROM t1 PARTITION (p1);
|
|
i j
|
|
-1 1
|
|
ALTER TABLE t1 RENAME COLUMN i TO j, RENAME COLUMN j TO i;
|
|
ERROR HY000: Column 'i' has a partitioning function dependency and cannot be dropped or renamed.
|
|
# It is still possible to rename columns if partitioning is updated.
|
|
ALTER TABLE t1 RENAME COLUMN i TO j, RENAME COLUMN j TO i PARTITION BY KEY (i) PARTITIONS 2 (PARTITION p0, PARTITION p1);
|
|
# Notice that we now use second column from PK for partitioning.
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`j` int(11) NOT NULL,
|
|
`i` int(11) NOT NULL,
|
|
PRIMARY KEY (`j`,`i`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY KEY (i)
|
|
(PARTITION p0 ENGINE = InnoDB,
|
|
PARTITION p1 ENGINE = InnoDB) */
|
|
# Hence the row should have been moved between partitions.
|
|
SELECT i, j FROM t1 PARTITION (p0);
|
|
i j
|
|
1 -1
|
|
SELECT i, j FROM t1 PARTITION (p1);
|
|
i j
|
|
DROP TABLE t1;
|
|
#
|
|
# 2) Replacing (deleting and then adding) column on which partitioning
|
|
# expression depends.
|
|
#
|
|
# 2.1) Replacing column on which partitioning expression depends
|
|
# is prohibited.
|
|
CREATE TABLE t1 (i INT, j INT) PARTITION BY RANGE(i+1) (PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN MAXVALUE);
|
|
ALTER TABLE t1 DROP COLUMN i, ADD COLUMN i INT;
|
|
ERROR HY000: Column 'i' has a partitioning function dependency and cannot be dropped or renamed.
|
|
# Also works for case of degenerate expression like COLUMN partitioning.
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (i INT) PARTITION BY LIST COLUMNS (i) (PARTITION p0 VALUES IN (-2,-1), PARTITION p1 VALUES IN (0, 1, 2));
|
|
ALTER TABLE t1 DROP COLUMN i, ADD COLUMN i INT;
|
|
ERROR HY000: Column 'i' has a partitioning function dependency and cannot be dropped or renamed.
|
|
#
|
|
# 2.2) It is OK to replace such columns if partitioning is removed by
|
|
# the same ALTER TABLE.
|
|
ALTER TABLE t1 DROP COLUMN i, ADD COLUMN i INT REMOVE PARTITIONING;
|
|
DROP TABLE t1;
|
|
#
|
|
# 2.3) Replacing such columns is also OK if partitioned expression
|
|
# is updated by the same ALTER TABLE.
|
|
CREATE TABLE t1 (i INT, j INT) PARTITION BY RANGE(i) (PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN MAXVALUE);
|
|
ALTER TABLE t1 DROP COLUMN i, ADD COLUMN i INT PARTITION BY RANGE(i) (PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN MAXVALUE);
|
|
# If new expression uses wrong column name an appropriate error
|
|
# is reported.
|
|
ALTER TABLE t1 DROP COLUMN i, ADD COLUMN i INT PARTITION BY RANGE(k) (PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN MAXVALUE);
|
|
ERROR 42S22: Unknown column 'k' in 'partition function'
|
|
#
|
|
# 2.4) Such replacements combined with partitioning function update should
|
|
# move rows between partitions as appropriate.
|
|
INSERT INTO t1 (i, j) VALUES (-1, 1);
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`j` int(11) DEFAULT NULL,
|
|
`i` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`i`)
|
|
(PARTITION p0 VALUES LESS THAN (0) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
|
|
SELECT i, j FROM t1 PARTITION (p0);
|
|
i j
|
|
-1 1
|
|
SELECT i, j FROM t1 PARTITION (p1);
|
|
i j
|
|
# Partitioning expression is kept the same, but value in its column is changed by replacement.
|
|
ALTER TABLE t1 DROP COLUMN i, ADD COLUMN i INT DEFAULT 1 PARTITION BY RANGE(i) (PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN MAXVALUE);
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`j` int(11) DEFAULT NULL,
|
|
`i` int(11) DEFAULT '1'
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`i`)
|
|
(PARTITION p0 VALUES LESS THAN (0) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
|
|
SELECT i, j FROM t1 PARTITION (p0);
|
|
i j
|
|
SELECT i, j FROM t1 PARTITION (p1);
|
|
i j
|
|
1 1
|
|
# Because of this such replacements can't be done in-place.
|
|
ALTER TABLE t1 DROP COLUMN i, ADD COLUMN i INT DEFAULT 2, ALGORITHM=INPLACE PARTITION BY RANGE(i) (PARTITION p0 VALUES LESS THAN (0), PARTITION p1 VALUES LESS THAN MAXVALUE);
|
|
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
|
|
DROP TABLE t1;
|
|
#
|
|
# 2.5) We also allow removal of columns which are used by partitioning function
|
|
# implicitly, because it is specified by "PARTITION BY KEY ()" (note empty
|
|
# column list) clause. In this case partitioning function is defined by
|
|
# the primary key, and the latter is adjusted when one of its columns is
|
|
# dropped. Such adjustment is considered as change of primary key (and thus
|
|
# partitioning function) which is supposed to be correctly handled by SE by
|
|
# moving rows between partitions.
|
|
CREATE TABLE t1 (i INT, j INT, PRIMARY KEY (i, j)) PARTITION BY KEY () PARTITIONS 2 (PARTITION p0, PARTITION p1);
|
|
INSERT INTO t1 (i, j) VALUES (1, 2);
|
|
SELECT i, j FROM t1 PARTITION (p0);
|
|
i j
|
|
1 2
|
|
SELECT i, j FROM t1 PARTITION (p1);
|
|
i j
|
|
ALTER TABLE t1 DROP COLUMN i;
|
|
# PRIMARY KEY is adjusted.
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`j` int(11) NOT NULL,
|
|
PRIMARY KEY (`j`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY KEY ()
|
|
(PARTITION p0 ENGINE = InnoDB,
|
|
PARTITION p1 ENGINE = InnoDB) */
|
|
# The row should be moved between partitions as appropriate.
|
|
SELECT j FROM t1 PARTITION (p0);
|
|
j
|
|
SELECT j FROM t1 PARTITION (p1);
|
|
j
|
|
2
|
|
# Dropping all/last column in PRIMARY KEY is prohibited.
|
|
ALTER TABLE t1 ADD COLUMN k INT, DROP COLUMN j;
|
|
ERROR HY000: Field in list of fields for partition function not found in table
|
|
DROP TABLE t1;
|
|
# Let us also check how replacement of column in PRIMARY KEY works.
|
|
CREATE TABLE t1 (i INT, j INT, PRIMARY KEY (i, j)) PARTITION BY KEY () PARTITIONS 2 (PARTITION p0, PARTITION p1);
|
|
INSERT INTO t1 (i, j) VALUES (1, 2);
|
|
SELECT i, j FROM t1 PARTITION (p0);
|
|
i j
|
|
1 2
|
|
SELECT i, j FROM t1 PARTITION (p1);
|
|
i j
|
|
ALTER TABLE t1 DROP COLUMN i, ADD COLUMN i INT DEFAULT 0;
|
|
# New column becomes part of PRIMARY KEY [sic!].
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`j` int(11) NOT NULL,
|
|
`i` int(11) NOT NULL DEFAULT '0',
|
|
PRIMARY KEY (`i`,`j`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY KEY ()
|
|
(PARTITION p0 ENGINE = InnoDB,
|
|
PARTITION p1 ENGINE = InnoDB) */
|
|
# Again the row should be moved between partitions as appropriate.
|
|
SELECT i, j FROM t1 PARTITION (p0);
|
|
i j
|
|
SELECT i, j FROM t1 PARTITION (p1);
|
|
i j
|
|
0 2
|
|
DROP TABLE t1;
|
|
|