MySQL8.0新特性~General tablespaces

通用表空间创建语法

InnoDB and NDB:[ADD DATAFILE 'file_name'][AUTOEXTEND_SIZE [=] value]InnoDB only:[FILE_BLOCK_SIZE = value][ENCRYPTION [=] {'Y' | 'N'}]NDB only:USE LOGFILE GROUP logfile_group[EXTENT_SIZE [=] extent_size][INITIAL_SIZE [=] initial_size][MAX_SIZE [=] max_size][NODEGROUP [=] nodegroup_id][WAIT][COMMENT [=] 'string']InnoDB and NDB:[ENGINE [=] engine_name]Reserved for future use:[ENGINE_ATTRIBUTE [=] 'string']

创建通用表空间

例1：不指定数据文件路径

可以不指定数据文件路径创建通用表空间，这时候数据文件会默认创建在datadir路径下。

创建表空间：

mysql> create tablespace tbs1 add datafile 'tbs1.ibd';
Query OK, 0 rows affected (0.03 sec)mysql> show variables like 'datadir';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| datadir       | /data/8801/data/ |
+---------------+------------------+
1 row in set (0.01 sec)mysql> select * from information_schema.innodb_tablespaces t join information_schema.innodb_datafiles d on t.SPACE=d.SPACE where t.NAME ='tbs1'\G
*************************** 1. row ***************************SPACE: 4NAME: tbs1FLAG: 18432ROW_FORMAT: AnyPAGE_SIZE: 16384ZIP_PAGE_SIZE: 0SPACE_TYPE: GeneralFS_BLOCK_SIZE: 4096FILE_SIZE: 114688ALLOCATED_SIZE: 65536
AUTOEXTEND_SIZE: 0SERVER_VERSION: 8.0.25SPACE_VERSION: 1ENCRYPTION: NSTATE: normalSPACE: 0x34PATH: tbs1.ibd
1 row in set (0.00 sec)

数据文件在datadir下

-rw-r----- 1 mysql mysql 114688 Jun 28 14:59 tbs1.ibd
[root@VM-20-8-centos data]# pwd
/data/8801/data

例2： 省略add datafile语句

MySQL 8.0.14版本之后，创建表空间时可以省略add datafile语句。这时MySQL会隐式创建具有唯一文件名的表空间数据文件。

mysql> create tablespace tbs2;
Query OK, 0 rows affected (0.02 sec)mysql> select * from information_schema.innodb_tablespaces t join information_schema.innodb_datafiles d on t.SPACE=d.SPACE where t.NAME ='tbs2'\G
*************************** 1. row ***************************SPACE: 5NAME: tbs2FLAG: 18432ROW_FORMAT: AnyPAGE_SIZE: 16384ZIP_PAGE_SIZE: 0SPACE_TYPE: GeneralFS_BLOCK_SIZE: 4096FILE_SIZE: 114688ALLOCATED_SIZE: 65536
AUTOEXTEND_SIZE: 0SERVER_VERSION: 8.0.25SPACE_VERSION: 1ENCRYPTION: NSTATE: normalSPACE: 0x35PATH: 27a191cd-351d-11ef-aaae-5254009ccf5d.ibd
1 row in set (0.00 sec)

查看数据文件
[root@VM-20-8-centos data]# ll
total 189072
-rw-r----- 1 mysql mysql 114688 Jun 28 15:08 27a191cd-351d-11ef-aaae-5254009ccf5d.ibd

例3：创建datadir路径外的表空间

通用表空间数据文件可以放置在数据目录之外的位置，但是路径必须是innodb_directories的值或者附加到innodb_directories值的变量（innodb_data_home_dir、innodb_undo_directory和datadir）之一。

innodb_directories参数的默认值为NULL。但是innodb_data_home_dir、innodb_undo_directory和datadir定义的目录会会附加到innodb_directories参数值上。

mysql> show variables like 'innodb_directories';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_directories |       |
+--------------------+-------+
1 row in set (0.00 sec)mysql> show variables like 'innodb_data_home_dir';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_data_home_dir |       |
+----------------------+-------+
1 row in set (0.00 sec)mysql> show variables like 'innodb_undo_directory';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_undo_directory | ./    |
+-----------------------+-------+
1 row in set (0.00 sec)mysql> show variables like 'datadir';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| datadir       | /data/8801/data/ |
+---------------+------------------+
1 row in set (0.00 sec)

修改innodb_directories变量参数需要修改MySQL配置文件，并重新启动MySQL服务。
例：创建datadir路径外的表空间

如果在未定义路径中创建通用表空间，由于不是已知目录，会发生如下ERROR 3121 (HY000)错误

mysql> create tablespace tb3 add datafile '/data/general/tb3.ibd';
ERROR 3121 (HY000): The directory does not exist or is incorrect.

修改配置文件，重启数据库， 创建目录并赋权

vi my.cnf
innodb_directories=/data/generalmkdir -p /data/general
chown mysql.mysql -R /data

mysql> create tablespace tb3 add datafile '/data/general/tb3.ibd';
Query OK, 0 rows affected (0.03 sec)mysql> select * from information_schema.innodb_tablespaces t join information_schema.innodb_datafiles d on t.SPACE=d.SPACE where t.NAME ='tb3'\G
*************************** 1. row ***************************SPACE: 6NAME: tb3FLAG: 18432ROW_FORMAT: AnyPAGE_SIZE: 16384ZIP_PAGE_SIZE: 0SPACE_TYPE: GeneralFS_BLOCK_SIZE: 4096FILE_SIZE: 114688ALLOCATED_SIZE: 65536
AUTOEXTEND_SIZE: 0SERVER_VERSION: 8.0.25SPACE_VERSION: 1ENCRYPTION: NSTATE: normalSPACE: 0x36PATH: /data/general/tb3.ibd
1 row in set (0.00 sec)

例4：创建InnoDB通用表空间的限制

可以在数据目录（datadir）中创建通用表空间。
但是为了避免与隐式创建的每个表一个文件的表空间发生冲突，不支持在数据目录（datadir）的子目录中创建InnoDB通用表空间。

如果在数据目录（datadir）的子目录中创建InnoDB通用表空间会报ERROR 3121 (HY000)错误。

mysql> create tablespace sqlplusdb_tb1 add datafile '/data/8801/data/tb4.ibd';Query OK, 0 rows affected (0.02 sec)mysql> 
mysql> 
mysql> create tablespace sqlplusdb_tb1 add datafile '/data/8801/data/test/tb5.ibd';
ERROR 1813 (HY000): Tablespace 'sqlplusdb_tb1' exists.
mysql> 

通用表空间中的表

例1：创建表指定通用表空间

和Oracle一样，MySQL在创建表时可以指定表空间也可以修改表的表空间。

mysql> use testdb;
Database changed
mysql> CREATE TABLE test_t1 (id INT) TABLESPACE tbs1 ;
Query OK, 0 rows affected (0.03 sec)mysql> SHOW CREATE TABLE  test_t1\G
*************************** 1. row ***************************Table: test_t1
Create Table: CREATE TABLE `test_t1` (`id` int DEFAULT NULL
) /*!50100 TABLESPACE `tbs1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)mysql> select name as 'table_name',    space_type from    information_schema.innodb_tables where   name like '%test_t1%' \G
*************************** 1. row ***************************
table_name: test/t1
space_type: Single
*************************** 2. row ***************************
table_name: testdb/test_t1
space_type: General
2 rows in set (0.01 sec)

例2：修改表的表空间

通过ALTER TABLE语句的TABLESPACE选项可以修改表的表空间，将表在通用表空间、独立表空间或系统表空间之间进行移动。

例2-1：修改到不同的通用表空间中

通过ALTER TABLE tbl_name TABLESPACE [=] tablespace_name修改表到不同的通用表空间中

例：

mysql> ALTER TABLE test_t1 tablespace=tbs2;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

例2-2：修改表从独立表空间或系统表空间到通用表空间

mysql> select  name as 'table_name',   space_type from    information_schema.innodb_tables where name ='test/t1' \G
*************************** 1. row ***************************
table_name: test/t1
space_type: Single
1 row in set (0.00 sec)mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> alter table t1 tablespace=tbs1;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> select  name as 'table_name',   space_type from    information_schema.innodb_tables where name ='test/t1' \G
*************************** 1. row ***************************
table_name: test/t1
space_type: General
1 row in set (0.00 sec)

例2-3：修改表从通用表空间或系统表空间到独立表空间

可以通过如下命令修改表从通用表空间或系统表空间到独立表空间。

ALTER TABLE tbl_name TABLESPACE [=] innodb_file_per_table;

例2-4：修改表从通用表空间或独立表空间到系统表空间

可以通过如下命令修改表从通用表空间或独立表空间到系统表空间。
ALTER TABLE tbl_name TABLESPACE [=] innodb_system;