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
| 最近在详细看mysql分区,输入 SHOW VARIABLES LIKE '%partition%';
返回为:
variable_name| value|
这么看貌似没有开启分区;
SHOW VARIABLES LIKE '%version%'
+
| Variable_name | Value |
+
| innodb_version | 5.6.14 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.14 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86 |
| version_compile_os | Win32 |
+
我记得mysql 5.0开始就支持分区 公司DBA说是5.1
我到公司数据库查看一下
variable_name | value|
have_partitioning | YES |
SHOW VARIABLES LIKE '%version%'
对比一下版本号不一样,纠结在此展开....到mysql官网查到
SHOW PLUGINS;
mysql> SHOW PLUGINS;
+
| Name | Status | Type | Library | License |
+
|<strong> partition | ACTIVE | STORAGE ENGINE | NULL | GPL</strong> |
+
是开启的
或者用
SELECT
PLUGIN_NAME AS NAME,
PLUGIN_VERSION AS VERSION,
PLUGIN_STATUS AS STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_TYPE='STORAGE ENGINE';
+
| NAME | VERSION | STATUS |
+
| binlog | 1.0 | ACTIVE |
| CSV | 1.0 | ACTIVE |
| MEMORY | 1.0 | ACTIVE |
| MyISAM | 1.0 | ACTIVE |
| MRG_MYISAM | 1.0 | ACTIVE |
| ARCHIVE | 3.0 | ACTIVE |
| BLACKHOLE | 1.0 | ACTIVE |
| FEDERATED | 1.0 | DISABLED |
| InnoDB | 5.6 | ACTIVE |
| PERFORMANCE_SCHEMA | 0.1 | ACTIVE |
| partition | 1.0 | ACTIVE |
+
以上纠结在于开始创建表结构有问题才会导致这一些列问题
其实也可以直接写一个正确的表结构创建一下;呵呵
TEST:
CREATE TABLE Atest2(
col1 INT NULL,
col2 INT NULL,
col3 INT NULL,
col4 INT NULL
)
ENGINE=INNODB
PARTITION BY HASH(`col3`)
PARTITIONS 4;
CREATE TABLE Atest1(
col1 INT NULL,
col2 INT NULL,
col3 INT NULL,
col4 INT NULL
)
ENGINE=INNODB
PARTITION BY HASH(`col3`)
PARTITIONS 4;
SHOW PLUGINS;
CREATE TABLE by_year (
d DATE
)
PARTITION BY RANGE (YEAR(d))
(
PARTITION P1 VALUES LESS THAN (2001),
PARTITION P2 VALUES LESS THAN (2002),
PARTITION P3 VALUES LESS THAN (2003),
PARTITION P4 VALUES LESS THAN (MAXVALUE)
)
INSERT INTO by_year(d) VALUE
('2001-01-20'),
('2002-01-20'),
('2003-01-20'),
(DATE_FORMAT(NOW(),'%Y-%m-%d'))
|
