drop table if exists tbInnoDBPartition1;
create table tbInnoDBPartition1 (
id BIGINT auto_increment,
adddate datetime,
age int not null,
price decimal(18, 2),
title varchar(200) not NULL,
primary key(id, adddate)
) ENGINE = InnoDB PARTITION by range(year(adddate) * 100 + month(adddate))(
PARTITION p201311 values less than (201311) data DIRECTORY = '/home/test/MySQLTestData/InnoDB' index DIRECTORY = '/home/test/MySQLTestData/InnoDB',
PARTITION p201312 values less than (201312) data DIRECTORY = '/home/test/MySQLTestData/InnoDB' index DIRECTORY = '/home/test/MySQLTestData/InnoDB'
);drop table if exists tbMyISAMPartition1;
create table tbMyISAMPartition1 (
id BIGINT auto_increment,
adddate datetime,
age int not null,
price decimal(18, 2),
title varchar(200) not NULL,
primary key(id, adddate)
) ENGINE = MyISAM PARTITION by range(year(adddate) * 100 + month(adddate))(
PARTITION p201311 values less than (201311) data DIRECTORY = '/home/test/MySQLTestData/MyISAM' index DIRECTORY = '/home/test/MySQLTestData/MyISAM',
PARTITION p201312 values less than (201312) data DIRECTORY = '/home/test/MySQLTestData/MyISAM' index DIRECTORY = '/home/test/MySQLTestData/MyISAM'
);1、前者使用 InnoDB,提示 [Err] 1030 - Got error -1 from storage engine2、后者使用 MyISAM,提示目录没有权限:[Err] 1 - Can't create/write to file '/home/test/MySQLTestData/MyISAM/tbMyISAMPartition1#P#p201311.MYI' (Errcode: 13 - Permission denied)
但是我已经把这个目录权限设为 777 了:chmod 777 MyISAM
create table tbInnoDBPartition1 (
id BIGINT auto_increment,
adddate datetime,
age int not null,
price decimal(18, 2),
title varchar(200) not NULL,
primary key(id, adddate)
) ENGINE = InnoDB PARTITION by range(year(adddate) * 100 + month(adddate))(
PARTITION p201311 values less than (201311) data DIRECTORY = '/home/test/MySQLTestData/InnoDB' index DIRECTORY = '/home/test/MySQLTestData/InnoDB',
PARTITION p201312 values less than (201312) data DIRECTORY = '/home/test/MySQLTestData/InnoDB' index DIRECTORY = '/home/test/MySQLTestData/InnoDB'
);drop table if exists tbMyISAMPartition1;
create table tbMyISAMPartition1 (
id BIGINT auto_increment,
adddate datetime,
age int not null,
price decimal(18, 2),
title varchar(200) not NULL,
primary key(id, adddate)
) ENGINE = MyISAM PARTITION by range(year(adddate) * 100 + month(adddate))(
PARTITION p201311 values less than (201311) data DIRECTORY = '/home/test/MySQLTestData/MyISAM' index DIRECTORY = '/home/test/MySQLTestData/MyISAM',
PARTITION p201312 values less than (201312) data DIRECTORY = '/home/test/MySQLTestData/MyISAM' index DIRECTORY = '/home/test/MySQLTestData/MyISAM'
);1、前者使用 InnoDB,提示 [Err] 1030 - Got error -1 from storage engine2、后者使用 MyISAM,提示目录没有权限:[Err] 1 - Can't create/write to file '/home/test/MySQLTestData/MyISAM/tbMyISAMPartition1#P#p201311.MYI' (Errcode: 13 - Permission denied)
但是我已经把这个目录权限设为 777 了:chmod 777 MyISAM
-> id BIGINT auto_increment,
-> adddate datetime,
-> age int not null,
-> price decimal(18, 2),
-> title varchar(200) not NULL,
-> primary key(id, adddate)
-> ) ENGINE = InnoDB PARTITION by range(year(adddate) * 100 + month(adddate))(
-> PARTITION p201311 values less than (201311) data DIRECTORY = '/home/test/MySQLTestData/InnoDB' index DIRECTORY = '/home/test/MySQLTestData/InnoDB',
-> PARTITION p201312 values less than (201312) data DIRECTORY = '/home/test/MySQLTestData/InnoDB' index DIRECTORY = '/home/test/MySQLTestData/InnoDB'
-> );
Query OK, 0 rows affected (0.01 sec)
create table tbInnoDBPartition1 (
id BIGINT auto_increment,
adddate datetime,
age int not null,
price decimal(18, 2),
title varchar(200) not NULL,
primary key(id, adddate)
)
ENGINE = InnoDB PARTITION by range(year(adddate) * 100 + month(adddate))(
PARTITION p201311 values less than (201311) data DIRECTORY = '/home/test/MySQLTestData/InnoDB',
PARTITION p201312 values less than (201312) data DIRECTORY = '/home/test/MySQLTestData/InnoDB'
);
结果:
sql_mode
STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION表创建失败