mysql 8.0表空间_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/