我用如下语句创建了一个分区表 然后成功往表中插入数据,但是在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');
解决方案 »
- SELECT COLUMN_NAME from information_schema 怎么会多出来几个字段?
- 关于关系代数和SQL的问题。
- Java链接Mysql的小疑问
- sql语句插入多条信息 那种方法好?
- 存储过程输出参数为null??
- mysql建表错误 error number 1289
- 怎么样update一组数据中id值最小的那个值!
- SQL查询问题
- 修改数据库名的语法是怎样的
- 两个相关联的表,P_NO(编号)为工资表外键,下面的语句对吗?
- 更换慢查询日志文件是否需要重启数据库
- cause: 连线被拒,请检查主机名称和埠号,并确定 postmaster 可以接受 TCP/IP 连线。
估计是忽略了
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]