- 創建DB
[root@XAG8 data]# mysql.login
Enter password:
root@127.0.0.1 : (none)【11:12:44】5 SQL->CREATE DATABASE TESTDB;
root@127.0.0.1 : (none)【11:12:47】6 SQL->SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| NULL |
+------------+
root@127.0.0.1 : (none)【11:13:02】7 SQL->use TESTDB;
root@127.0.0.1 : TESTDB【11:13:30】8 SQL->SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| testdb |
+------------+
[root@XAG8 testdb]# pwd
/usr/local/mysql/data/testdb
[root@XAG8 testdb]# ls
#創建通用表空間
root@127.0.0.1 : TESTDB【11:22:31】18 SQL->
create tablespace testdb_ud ADD DATAFILE '/usr/local/mysql/data/testdb_ud.ibd' Engine=InnoDB;
#向通用表空間添加表
root@127.0.0.1 : TESTDB【11:39:33】27 SQL->
create table t1 (aaa int,bbb varchar(10)) tablespace testdb_ud;
- 創建分區表
create tablespace testdb201907 add datafile '/usr/local/mysql/data/testdb201907.ibd' Engine=InnoDB;
create tablespace testdb201908 add datafile '/usr/local/mysql/data/testdb201908.ibd' Engine=InnoDB;
create tablespace testdb201909 add datafile '/usr/local/mysql/data/testdb201909.ibd' Engine=InnoDB;
create tablespace testdb201910 add datafile '/usr/local/mysql/data/testdb201910.ibd' Engine=InnoDB;
root@127.0.0.1 : TESTDB【05:09:20】49 SQL->
create table t2_part
(
id int,
creat_time datetime
) ENGINE=InnoDB PARTITION BY RANGE(TO_DAYS(creat_time))
(
PARTITION pl907 VALUES LESS THAN (TO_DAYS('2019-08-01')),
PARTITION p1908 VALUES LESS THAN (TO_DAYS('2019-09-01')),
PARTITION p1909 VALUES LESS THAN (TO_DAYS('2019-10-01'))
);
root@127.0.0.1 : TESTDB【06:00:27】114 SQL->
create table t3_part
(
id int,
creat_date date
) ENGINE=InnoDB PARTITION BY RANGE columns(creat_date)
(
PARTITION pl907 VALUES LESS THAN ('2019-08-01'),
PARTITION p1908 VALUES LESS THAN ('2019-09-01'),
PARTITION p1909 VALUES LESS THAN ('2019-10-01')
);
root@127.0.0.1 : TESTDB【06:17:34】139 SQL->create table t5_part
(
id int,
creat_time datetime
) ENGINE=InnoDB PARTITION BY RANGE(TO_DAYS(creat_time))
(
PARTITION pl907 VALUES LESS THAN (TO_DAYS('2019-08-01')) TABLESPACE testdb201907,
PARTITION p1908 VALUES LESS THAN (TO_DAYS('2019-09-01')) TABLESPACE testdb201908,
PARTITION p1909 VALUES LESS THAN (TO_DAYS('2019-10-01')) TABLESPACE testdb201909
);
ERROR 1478 (HY000): InnoDB : A partitioned table is not allowed in a shared tablespace.
#在MySQL 5.7.24中不支持将表分区添加到共享表空间，并在MySQL 8.0.13中删除了。
#共享表空间包括InnoDB系统表空间和通用表空间。
root@127.0.0.1 : TESTDB【05:45:45】96 SQL->
SELECT a.NAME AS space_name, b.NAME AS table_name FROM INFORMATION_SCHEMA.INNODB_TABLESPACES a,INFORMATION_SCHEMA.INNODB_TABLES b WHERE a.SPACE=b.SPACE AND a.NAME LIKE 'testdb%';
+------------------------+------------------------+
| space_name | table_name |
+------------------------+------------------------+
| testdb_ud | testdb/t1 |
| testdb/t2_part?p?pl907 | testdb/t2_part#p#pl907 |
| testdb/t2_part?p?p1908 | testdb/t2_part#p#p1908 |
| testdb/t2_part?p?p1909 | testdb/t2_part#p#p1909 |
| testdb/t3_part?p?pl907 | testdb/t3_part#p#pl907 |
| testdb/t3_part?p?p1908 | testdb/t3_part#p#p1908 |
| testdb/t3_part?p?p1909 | testdb/t3_part#p#p1909 |
+------------------------+------------------------+
