mysql創建數據庫,MYSQL 8 安装之02(創建數據庫及表空間)

創建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 |

+------------------------+------------------------+