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)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15498/viewspace-2154462/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15498/viewspace-2154462/