创建分区表时,指定了数据文件目录和索引文件目录,但是创建完成并插入几万行记录后,只看到数据文件目录下有文件生成,索引目录下没有任务文件生成。drop table if exists tbpartition1_1;
create table tbpartition1_1 (
id INTEGER auto_increment,
adddate datetime,
age int not null,
price decimal(18, 2),
title varchar(100) not NULL,
primary key(id, adddate)
) PARTITION by range(year(adddate) * 100 + month(adddate))(
PARTITION p201311 values less than (201311) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201312 values less than (201312) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201401 values less than (201401) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201402 values less than (201402) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201403 values less than (201403) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201404 values less than (201404) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201405 values less than (201405) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201406 values less than (201406) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201407 values less than (201407) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201408 values less than (201408) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201409 values less than (201409) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201410 values less than (201410) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201411 values less than (201411) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201412 values less than (201412) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201501 values less than (201501) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201502 values less than (201502) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201503 values less than (201503) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201504 values less than (201504) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201505 values less than (201505) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201506 values less than (201506) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201507 values less than (201507) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201508 values less than (201508) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201509 values less than (201509) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201510 values less than (201510) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201511 values less than (201511) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201512 values less than (201512) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201601 values less than (201601) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index'
);
create table tbpartition1_1 (
id INTEGER auto_increment,
adddate datetime,
age int not null,
price decimal(18, 2),
title varchar(100) not NULL,
primary key(id, adddate)
) PARTITION by range(year(adddate) * 100 + month(adddate))(
PARTITION p201311 values less than (201311) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201312 values less than (201312) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201401 values less than (201401) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201402 values less than (201402) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201403 values less than (201403) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201404 values less than (201404) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201405 values less than (201405) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201406 values less than (201406) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201407 values less than (201407) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201408 values less than (201408) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201409 values less than (201409) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201410 values less than (201410) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201411 values less than (201411) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201412 values less than (201412) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201501 values less than (201501) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201502 values less than (201502) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201503 values less than (201503) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201504 values less than (201504) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201505 values less than (201505) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201506 values less than (201506) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201507 values less than (201507) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201508 values less than (201508) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201509 values less than (201509) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201510 values less than (201510) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201511 values less than (201511) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201512 values less than (201512) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index',
PARTITION p201601 values less than (201601) data DIRECTORY = 'F:/PartitionData/data' index DIRECTORY = 'F:/PartitionData/index'
);
解决方案 »
- MYSQL中文时间比较
- 【救命 !】 用命令行把字段 gbk_chinese_ci转换gbk bin 后 都变乱码了
- windows任务计划执行mysql存储过程
- MySQL当表数据量极大时[单张表800万条记录],如何提高查询及更新速度?
- mysql中 一个表的一个列 可以有多个属性值吗?
- 怎么停止触发器
- 急问,如何修改mysql的字符集?
- 登录mysql时出错:ERROR 2003:Can't connect to MySQL server on 'localhost'<10061>
- 使用wmdumper将维基百科导入mysql出问题
- mysql奇怪乱码
- 关于MySql非root用户删除数据库
- 插入数据库报错,请问怎么解决
知道了,MySQL官网文档:
On Windows, the DATA DIRECTORY and INDEX DIRECTORY options are not supported for individual partitions or subpartitions of MyISAM tables (Bug #30459). However, you can use DATA DIRECTORY for individual partitions or subpartitions of InnoDB tables.