MySQL分区（PARTITION）操作

Buty9147

于 2022-11-23 15:34:58 发布

阅读量925

点赞数

分类专栏：数据库文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/Butingnal/article/details/128001036

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏


-- 选择数据库
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;