MySQL 8.0表空間新特性簡單實驗

建立表空間(有沒有發現和oracle命令很像）：
mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
Query OK, 0 rows affected (0.35 sec)

mysql> select * from information_schema.innodb_tablespaces where name='ts1';
+-------+------+-------+------------+-----------+---------------+------------+---------------+-----------+----------------+----------------+---------------+
| SPACE | NAME | FLAG  | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | SERVER_VERSION | SPACE_VERSION |
+-------+------+-------+------------+-----------+---------------+------------+---------------+-----------+----------------+----------------+---------------+
|     7 | ts1  | 18432 | Any        |     16384 |             0 | General    |          4096 |    114688 |          65536 | 8.0.11         |             1 |
+-------+------+-------+------------+-----------+---------------+------------+---------------+-----------+----------------+----------------+---------------+
1 row in set (0.00 sec)


建立表時可以指定表空間:
mysql>  CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 Engine=InnoDB;
Query OK, 0 rows affected (0.18 sec)


刪除表:
mysql> DROP TABLE t1;
Query OK, 0 rows affected (0.07 sec)

但表空間檔案還在:
[root@test data]# ll -h ts1.ibd 
-rw-r----- 1 mysql mysql 112K Apr 27 16:15 ts1.ibd


需要
mysql> drop tablespace ts1;
Query OK, 0 rows affected (0.11 sec)


root@test data]# ll -h ts1.ibd 
ls: cannot access ts1.ibd: No such file or directory



mysql> CREATE TABLESPACE dsf add datafile 'dsf.ibd' Engine=InnoDB;
Query OK, 0 rows affected (0.17 sec)


表空間改名:
mysql> alter tablespace dsf rename to liuyb;
Query OK, 0 rows affected (0.09 sec)


mysql> select * from information_schema.innodb_tablespaces where name='dsf';
Empty set (0.00 sec)


mysql> select * from information_schema.innodb_tablespaces where name='liuyb';
+-------+-------+-------+------------+-----------+---------------+------------+---------------+-----------+----------------+----------------+---------------+
| SPACE | NAME  | FLAG  | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | SERVER_VERSION | SPACE_VERSION |
+-------+-------+-------+------------+-----------+---------------+------------+---------------+-----------+----------------+----------------+---------------+
|     8 | liuyb | 18432 | Any        |     16384 |             0 | General    |          4096 |    114688 |          65536 | 8.0.11         |             1 |
+-------+-------+-------+------------+-----------+---------------+------------+---------------+-----------+----------------+----------------+---------------+
1 row in set (0.00 sec)