我用如下语句创建了一个分区表 然后成功往表中插入数据,但是在D:\data的相应目录下并没有发现文件,查看mysql的默认的数据库文件路径,发现文件在默认路径下。我用的是mysql 5.1.25 ,请问各位指定目录能成功吗,要如何设置才可以?
建库脚本如下:
CREATE TABLE test(
CM_CAPTURE_ID INT AUTO_INCREMENT,
TIME_STAMP TIMESTAMP,
`YEAR` int DEFAULT NULL,
`MONTH` int DEFAULT NULL,
`DAY` int DEFAULT NULL,
`HOUR` int DEFAULT NULL,
`MINUTE` int DEFAULT NULL,
PRIMARY KEY (CM_CAPTURE_ID,MONTH)
)ENGINE=MYISAM PARTITION BY LIST( `MONTH`) ( PARTITION p1 VALUES in (1) DATA DIRECTORY = 'D:\data\data1' INDEX DIRECTORY = 'D:\data\index1', PARTITION p2 VALUES in (2) DATA DIRECTORY = 'D:\data\data2' INDEX DIRECTORY = 'D:\data\index2', PARTITION p3 VALUES in (3) DATA DIRECTORY = 'D:\data\data3' INDEX DIRECTORY = 'D:\data\index3',PARTITION p4 VALUES in (4) DATA DIRECTORY = 'D:\data\data4' INDEX DIRECTORY = 'D:\data\index4',PARTITION p5 VALUES in (5) DATA DIRECTORY = 'D:\data\data5' INDEX DIRECTORY = 'D:\data\index5',PARTITION p6 VALUES in (6) DATA DIRECTORY = 'D:\data\data6' INDEX DIRECTORY = 'D:\data\index6',PARTITION p7 VALUES in (7) DATA DIRECTORY = 'D:\data\data7' INDEX DIRECTORY ='D:\data\index',PARTITION p8 VALUES in (8) DATA DIRECTORY = 'D:\data\data8' INDEX DIRECTORY = 'D:\data\index8',PARTITION p9 VALUES in (9) DATA DIRECTORY = 'D:\data\data9' INDEX
DIRECTORY = 'D:\data\index9',PARTITION p10 VALUES in (10) DATA DIRECTORY = 'D:\data\data10' INDEX DIRECTORY = 'D:\data\index10',PARTITION p11 VALUES in (11) DATA DIRECTORY = 'D:\data\data11' INDEX DIRECTORY = 'D:\data\index11',PARTITION p12 VALUES in (12) DATA DIRECTORY = 'D:\data\data12' INDEX DIRECTORY = 'D:\data\index12');
建库脚本如下:
CREATE TABLE test(
CM_CAPTURE_ID INT AUTO_INCREMENT,
TIME_STAMP TIMESTAMP,
`YEAR` int DEFAULT NULL,
`MONTH` int DEFAULT NULL,
`DAY` int DEFAULT NULL,
`HOUR` int DEFAULT NULL,
`MINUTE` int DEFAULT NULL,
PRIMARY KEY (CM_CAPTURE_ID,MONTH)
)ENGINE=MYISAM PARTITION BY LIST( `MONTH`) ( PARTITION p1 VALUES in (1) DATA DIRECTORY = 'D:\data\data1' INDEX DIRECTORY = 'D:\data\index1', PARTITION p2 VALUES in (2) DATA DIRECTORY = 'D:\data\data2' INDEX DIRECTORY = 'D:\data\index2', PARTITION p3 VALUES in (3) DATA DIRECTORY = 'D:\data\data3' INDEX DIRECTORY = 'D:\data\index3',PARTITION p4 VALUES in (4) DATA DIRECTORY = 'D:\data\data4' INDEX DIRECTORY = 'D:\data\index4',PARTITION p5 VALUES in (5) DATA DIRECTORY = 'D:\data\data5' INDEX DIRECTORY = 'D:\data\index5',PARTITION p6 VALUES in (6) DATA DIRECTORY = 'D:\data\data6' INDEX DIRECTORY = 'D:\data\index6',PARTITION p7 VALUES in (7) DATA DIRECTORY = 'D:\data\data7' INDEX DIRECTORY ='D:\data\index',PARTITION p8 VALUES in (8) DATA DIRECTORY = 'D:\data\data8' INDEX DIRECTORY = 'D:\data\index8',PARTITION p9 VALUES in (9) DATA DIRECTORY = 'D:\data\data9' INDEX
DIRECTORY = 'D:\data\index9',PARTITION p10 VALUES in (10) DATA DIRECTORY = 'D:\data\data10' INDEX DIRECTORY = 'D:\data\index10',PARTITION p11 VALUES in (11) DATA DIRECTORY = 'D:\data\data11' INDEX DIRECTORY = 'D:\data\index11',PARTITION p12 VALUES in (12) DATA DIRECTORY = 'D:\data\data12' INDEX DIRECTORY = 'D:\data\index12');
估计是忽略了
Beginning with MySQL 5.1.23, table-level DATA DIRECTORY and INDEX DIRECTORY options are ignored for partitioned tables. (Bug#32091)
These options work only when you are not using the --skip-symbolic-links option. Your operating system must also have a working, thread-safe realpath() call. See Section 7.6.1.2, “Using Symbolic Links for Tables on Unix”, for more complete information. If a MyISAM table is created with no DATA DIRECTORY option, the .MYD file is created in the database directory. By default, if MyISAM finds an existing .MYD file in this case, it overwrites it. The same applies to .MYI files for tables created with no INDEX DIRECTORY option. As of MySQL 5.1.23, to suppress this behavior, start the server with the --keep_files_on_create option, in which case MyISAM will not overwrite existing files and returns an error instead.
+-----------+
| version() |
+-----------+
| 4.1.7 |
+-----------+
1 row in set (0.00 sec)mysql> create table t_wwwwa (
-> id int primary key,
-> col1 int
-> ) DATA DIRECTORY ='/var/lib/mysql/csdn' ;
Query OK, 0 rows affected (0.01 sec)mysql>
[code=BatchFile][root@xxxxxxx mysql]# ll csdn
total 4
-rw-rw---- 1 mysql mysql 0 Jul 7 23:30 t_wwwwa.MYD
[root@xxxxxxx mysql]# ll test/t*
lrwxrwxrwx 1 mysql mysql 31 Jul 7 23:30 test/t_wwwwa.MYD -> /var/lib/mysql/csdn/t_wwwwa.MYD
-rw-rw---- 1 mysql mysql 1024 Jul 7 23:30 test/t_wwwwa.MYI
-rw-rw---- 1 mysql mysql 8586 Jul 7 23:30 test/t_wwwwa.frm
[root@xxxxxxx mysql]#[/code]