创建,增加,删除mysql表分区
来源:互联网 发布:程序员最讨厌 皇长子 编辑:程序博客网 时间:2024/05/10 03:20
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
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: 0
mysql>
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=2
mysql> 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=5
mysql> 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)删除分区,同
hash
mysql> alter table titles coalesce partition 2;
Query OK, 443308 rows affected (7.86 sec)
Records: 443308 Duplicates: 0 Warnings: 0
(3)添加分区,同
hash
mysql> alter table titles add partition partitions 3;
Query OK, 443308 rows affected (6.17 sec)
Records: 443308 Duplicates: 0 Warnings: 0
0 0
- 创建,增加,删除mysql表分区
- 创建,增加,删除mysql表分区
- MySQL表的四种分区类型&创建,增加,删除mysql表分区
- MYSQL创建或增加分区
- Oracle定时删除、增加表分区
- 增加、删除和收缩表分区!
- db2 增加删除分区
- db2 增加删除分区
- mysql 创建用户删除用户增加用户权限
- 分区表 分区索引 增加 收缩 删除 表分区
- mysql动态创建表分区
- MYSQL数据库创建表分区
- MSSQLSERVER 分区创建 分区合并删除 分区
- mysql 删除分区 备份分区
- Oracle定时删除、增加表分区(TOAD,PL/SQL)
- Oracle定时删除、增加表分区(TOAD,PL/SQL)
- mysql 批量删除分区
- oracle和mysql在创建表、更新表(增加、修改、删除字段)、删除表的区别与联系
- Firefox Developer Edition已阻止此网站安装未经验证的附加组件的解决办法
- java动态代理和静态代理
- Maven学习总结(四)——Maven核心概念
- 尚学堂-马士兵老师的《自学之路》
- 卷积神经网络
- 创建,增加,删除mysql表分区
- C/C++中如何获取数组的长度?
- 黑马程序员——OC视频学习过程6
- OAuth 2.0 原理介绍
- JAVA实现百度OCR文字识别功能
- C# String.Format格式说明
- JavaScript实现类的private、protected、public、static以及继承
- cocosbuilder
- iOS开发-Day26-UILabel&UIButton&UITextField