首页 > 代码库 > 创建,增加,删除mysql表分区
创建,增加,删除mysql表分区
1.测试添加分区和删除分区###添加删除range分区(1)创建一个分区:CREATE TABLE titles ( emp_no INT NOT NULL, title VARCHAR(50) NOT NULL, from_date DATE NOT NULL, to_date DATE, KEY (emp_no), PRIMARY KEY (emp_no,title, from_date)) partition by range columns(from_date)(partition p01 values less than (‘1985-12-31‘),partition p02 values less than (‘1990-12-31‘),partition p03 values less than (‘1995-12-31‘),partition p04 values less than (‘2000-12-31‘),partition p05 values less than (‘2005-12-31‘),partition p06 values less than (‘2010-12-31‘),partition p07 values less than (‘2015-12-31‘),partition p08 values less than (‘2020-12-31‘),partition p09 values less than (‘2025-12-31‘),partition p10 values less than (‘2030-12-31‘));导入数据mysql> source titles.sql(2)添加分区:备注:不能超过p04的范围,严格递增每个分区,即最小不能小于前一个分区下面新曾了两个分区n01和n02 alter table titles reorganize partition p04 into( partition n01 values less than(‘1997-12-31‘), partition n02 values less than(‘1998-12-31‘), partition p04 values less than(‘2000-12-31‘) );(3)删除分区:删除分区即删除数据。mysql> select count(*) from titles where from_date<‘1985-12-31‘;+----------+| count(*) |+----------+| 18238 |+----------+1 row in set (0.09 sec)mysql> alter table titles drop partition p01;Query OK, 0 rows affected (0.05 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> select count(*) from titles where from_date<‘1985-12-31‘;+----------+| count(*) |+----------+| 0 |+----------+1 row in set (0.04 sec)###添加删除list分区(1)创建list分区CREATE TABLE titles ( emp_no INT NOT NULL, title VARCHAR(50) NOT NULL, from_date DATE NOT NULL, to_date DATE, KEY (emp_no), PRIMARY KEY (emp_no,title, from_date)) partition by list columns(title)(partition p0 values in (‘Assistant Engineer‘),partition p1 values in (‘Engineer‘),partition p2 values in (‘Manager‘),partition p3 values in (‘Senior Engineer‘),partition p4 values in (‘Senior Staff‘),partition p5 values in (‘Staff‘),partition p6 values in (‘Technique Leader‘));导入数据titles.sql 是employees数据库导出的数据mysql> source titles.sql(2)添加分区mysql> alter table titles add partition(partition p7 values in(‘CEO‘));Query OK, 0 rows affected (0.07 sec)Records: 0 Duplicates: 0 Warnings: 0(3)删除分区:mysql> alter table titles drop partition p0;Query OK, 0 rows affected (0.04 sec)Records: 0 Duplicates: 0 Warnings: 0###添加删除hash分区(1)创建hash分区CREATE TABLE titles ( emp_no INT NOT NULL, title VARCHAR(50) NOT NULL, from_date DATE NOT NULL, to_date DATE, KEY (emp_no), PRIMARY KEY (emp_no,title, from_date)) partition by hash(emp_no)partitions 4;导入数据:mysql> source titles.sql(2)删除hash分区4个分区减少到两个,4-2=2mysql> alter table titles coalesce partition 2;Query OK, 443308 rows affected (12.41 sec)Records: 443308 Duplicates: 0 Warnings: 0(3)添加hash分区增加到5个,2+3=5mysql> alter table titles add partition partitions 3;Query OK, 443308 rows affected (11.54 sec)Records: 443308 Duplicates: 0 Warnings: 0###添加删除key分区(1)创建key分区CREATE TABLE titles ( emp_no INT NOT NULL, title VARCHAR(50) NOT NULL, from_date DATE NOT NULL, to_date DATE, KEY (emp_no), PRIMARY KEY (emp_no,title, from_date)) partition by key(emp_no)partitions 4;导入数据mysql> source titles.sql(2)删除分区,同hashmysql> alter table titles coalesce partition 2;Query OK, 443308 rows affected (7.86 sec)Records: 443308 Duplicates: 0 Warnings: 0(3)添加分区,同hashmysql> alter table titles add partition partitions 3;Query OK, 443308 rows affected (6.17 sec)Records: 443308 Duplicates: 0 Warnings: 0
创建,增加,删除mysql表分区
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。