MySQL如何创建外部表

MySQL如何创建外部表

本文实验版本MySQL 8。

MySQL中所谓的外部表，就是将innodb表创建在变量datadir代表的数据目录之外。(跟Oracle外部表完全不是一个东西)

出于扩容，空间管理，IO优化等原因。

这里可以使用如下3种方式创建外部表：

• 使用 DATA DIRECTORY 子句

• 使用 CREATE TABLE ... TABLESPACE 语法

• 在外部通用表空间中创建表

DATA DIRECTORY方式

如果使用DATA DIRECTORY子句创建，前提是开启File-Per-Table表空间属性，即启动innodb_file_per_table变量(默认启用)。

以下是关闭File-Per_table表空间下，使用DATA DIRECTORY报错示例：

(root@localhost 10:50:10) [(none)](8)> set GLOBAL innodb_file_per_table=0;
(root@localhost 10:50:30) [(none)](8)> CREATE TABLE zkm.t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/mysqldata1/';
ERROR 1031 (HY000): Table storage engine for 't1' doesn't have this option
Warning (Code 1478): InnoDB: DATA DIRECTORY requires innodb_file_per_table.
Error (Code 1031): Table storage engine for 't1' doesn't have this option

从 MySQL 8.0.21 开始，使用DATA DIRECTORY子句在数据目录外创建的表和表分区仅限于InnoDB。

由于实例崩溃恢复需要确定表空间文件位置目录，因此DATA DIRECTORY指定的数据目录外的位置则必须在提前定义好，无法随意指定，不然mysql它本身不知道都有哪些目录。

[root@dev-app80 ~]# mkdir /mysqldata_tmp 
[root@dev-app80 ~]# chown mysql:mysql /mysqldata_tmp

(root@localhost 11:10:57) [(none)](8)> set GLOBAL innodb_file_per_table = 1;
Query OK, 0 rows affected (0.00 sec)
(root@localhost 11:20:53) [(none)](8)> CREATE TABLE zkm.t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/mysqldata_tmp/';
ERROR 3121 (HY000): The DATA DIRECTORY location must be in a known directory.
Error (Code 3121): The DATA DIRECTORY location must be in a known directory.
Error (Code 1031): Table storage engine for 't1' doesn't have this option

那么如何提前定义好DATA DIRECTORY指定的数据目录外的位置，比如上边的/mysqldata_tmp，这里通过innodb_directories来指定，不过该变量是只读的需要设置重启生效。

[root@dev-app80 ~]# vi /etc/my.cnf
[mysqld]添加
innodb_directories="/mysqldata_tmp"
重启mysql服务。

(root@localhost 14:00:07) [(none)](9)> CREATE TABLE zkm.t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/mysqldata_tmp/';
Query OK, 0 rows affected (0.05 sec)

[root@dev-app80 ~]# ll /mysqldata_tmp/zkm/
total 112
-rw-r----- 1 mysql mysql 114688 Jun 30 14:03 t1.ibd

CREATE TABLE ... TABLESPACE方式

create table ... tablespace需要和data directory结合，可以不需要开启File-Per-Table表空间属性，即不需要启动innodb_file_per_table变量(虽然是默认启动)，为此需要指定 "innodb_file_per_table" 为表空间名称。

(root@localhost 14:34:57) [(none)](9)> set GLOBAL innodb_file_per_table=0;
Query OK, 0 rows affected (0.00 sec)

(root@localhost 14:34:59) [(none)](9)> CREATE TABLE zkm.t2 (c1 INT PRIMARY KEY) TABLESPACE = NoExistsTbs DATA DIRECTORY = '/mysqldata_tmp/';
ERROR 3510 (HY000): Tablespace NoExistsTbs doesn't exist.
(root@localhost 14:35:33) [(none)](9)> CREATE TABLE zkm.t2 (c1 INT PRIMARY KEY) TABLESPACE = innodb_file_per_table DATA DIRECTORY = '/mysqldata_tmp/';
Query OK, 0 rows affected (0.02 sec)

[root@dev-app80 zkm]# ll /mysqldata_tmp/zkm/
total 224
-rw-r----- 1 mysql mysql 114688 Jun 30 14:30 t1.ibd
-rw-r----- 1 mysql mysql 114688 Jun 30 14:33 t2.ibd

使用通用表空间创建外部表

同理通用表空间使用的非datadir新目录，需要出现在innodb_directories来指定，该变量是只读的需要设置重启生效。

我们设置一个新的目录/test_share_tbs，显示CREATE TABLESPACE并进行测试，默认的ENGINE = InnoDB，也可显示指定必须是ENGINE = InnoDB为子句(create tablespace test_share_tbs add datafile '/test_share_tbs/comm_tbs01.ibd' engine=innodb;)。

[root@dev-app80 zkm]# mkdir /test_share_tbs
[root@dev-app80 zkm]# chown mysql:mysql /test_share_tbs/
[root@dev-app80 zkm]# vi /etc/my.cnf
innodb_directories="/mysqldata_tmp;/test_share_tbs"

重启mysql服务。

(root@localhost 14:53:16) [(none)](8)> select @@innodb_directories,@@default_storage_engine;
+--------------------------------+--------------------------+
| @@innodb_directories           | @@default_storage_engine |
+--------------------------------+--------------------------+
| /mysqldata_tmp;/test_share_tbs | InnoDB                   |
+--------------------------------+--------------------------+
1 row in set (0.00 sec)
(root@localhost 15:01:18) [(none)](8)> create tablespace test_share_tbs add datafile '/test_share_tbs/comm_tbs01.ibd';
Query OK, 0 rows affected (0.18 sec)

[root@dev-app80 zkm]# ll /test_share_tbs/
total 112
-rw-r----- 1 mysql mysql 114688 Jun 30 15:01 comm_tbs01.ibd

接下来将外部表创建的时候指定新的表空间test_share_tbs。防偷防爬。

(root@localhost 15:04:21) [(none)](8)> CREATE TABLE zkm.t3 (c1 INT PRIMARY KEY) TABLESPACE = test_share_tbs;
Query OK, 0 rows affected (0.04 sec)

至此。