MySQL如何创建外部表
MySQL如何创建外部表
本文实验版本MySQL 8。
MySQL中所谓的外部表，就是将innodb表创建在变量datadir代表的数据目录之外。（跟Oracle外部表完全不是一个东西）
出于扩容，空间管理，IO优化等原因。
这里可以使用如下3种方式创建外部表：
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)
至此。
