-- 选择数据库
USE sewer_renovation;
-- 创建表及分区
CREATE TABLE employees_country (
id INT NOT NULL ,
NAME VARCHAR(255),
department VARCHAR(10),
country VARCHAR(255)
)
PARTITION BY LIST COLUMNS (department, country) -- LIST集合类型分区,多列
(
PARTITION first_office VALUES IN (('dep1', 'Russia'), ('dep1', 'Croatia')),
PARTITION second_office VALUES IN (('dep2', 'Russia'))
);
-- 查询分区,2个
SELECT PARTITION_NAME,PARTITION_DESCRIPTION,TABLE_ROWS
FROM information_schema.`PARTITIONS` WHERE table_name='employees_country';
-- 添加数据,成功
INSERT INTO employees_country VALUES(1, 'Ann', 'dep1', 'Russia');
-- SQL 错误 [1526] [HY000]: Table has no partition for value from column_list
INSERT INTO employees_country VALUES(2, 'Dan', 'dep2', 'Croatia');
-- SQL 错误 [1526] [HY000]: Table has no partition for value from column_list
INSERT INTO employees_country VALUES(3, 'Dan', 'dep2', 'China');
-- 添加新分区
ALTER TABLE employees_country
ADD PARTITION (
PARTITION third_office VALUES IN (('dep2', 'China')),
PARTITION four_office VALUES IN (('dep2', 'Croatia')) ENGINE = INNODB )
-- 重新查询分区,4个
SELECT PARTITION_NAME,PARTITION_DESCRIPTION,TABLE_ROWS
FROM information_schema.`PARTITIONS` WHERE table_name='employees_country';
-- 重新添加数据,成功
INSERT INTO employees_country VALUES(2, 'Dan', 'dep2', 'Croatia');
-- 重新添加数据,成功
INSERT INTO employees_country VALUES(3, 'Dan', 'dep2', 'China');
-- 查询分区,TABLE_ROWS=2
SELECT PARTITION_NAME,PARTITION_DESCRIPTION,TABLE_ROWS
FROM information_schema.`PARTITIONS` WHERE table_name='employees_country';
-- 查询数据,3行数据
SELECT * FROM employees_country;
-- 对表进行分析,OK
ANALYZE TABLE employees_country;
-- 查询分区,TABLE_ROWS=3
SELECT PARTITION_NAME,PARTITION_DESCRIPTION,TABLE_ROWS
FROM information_schema.`PARTITIONS` WHERE table_name='employees_country';
-- 重新定义分区
alter table employees_country
partition by LIST columns(department, country)
(
PARTITION first_office VALUES IN (('dep1', 'Russia'), ('dep1', 'Croatia')),
PARTITION third_office VALUES IN (('dep2', 'China')),
PARTITION four_office VALUES IN (('dep2', 'Croatia'))
);
-- 查询分区,3个
SELECT PARTITION_NAME,PARTITION_DESCRIPTION,TABLE_ROWS
FROM information_schema.`PARTITIONS` WHERE table_name='employees_country';
-- 删除分区
ALTER TABLE employees_country DROP PARTITION four_office;
-- 查询分区,2个
SELECT PARTITION_NAME,PARTITION_DESCRIPTION,TABLE_ROWS
FROM information_schema.`PARTITIONS` WHERE table_name='employees_country';
-- 查询数据,2行
SELECT * FROM employees_country;
-- 移除全部分区,不会丢失数据
alter table employees_country remove partitioning;
-- 查询分区,0个
SELECT PARTITION_NAME,PARTITION_DESCRIPTION,TABLE_ROWS
FROM information_schema.`PARTITIONS` WHERE table_name='employees_country';
-- 查询数据,2行
SELECT * FROM employees_country;
-- 重新定义分区
alter table employees_country
partition by LIST columns(department, country)
(
PARTITION first_office VALUES IN (('dep1', 'Russia'), ('dep1', 'Croatia')),
PARTITION second_office VALUES IN (('dep2', 'Russia')),
PARTITION third_office VALUES IN (('dep2', 'China')),
PARTITION four_office VALUES IN (('dep2', 'Croatia'))
);
-- 查询分区,4个
SELECT PARTITION_NAME,PARTITION_DESCRIPTION,TABLE_ROWS
FROM information_schema.`PARTITIONS` WHERE table_name='employees_country';
-- 查询数据,2行
SELECT * FROM employees_country;
感谢查阅.