MySQL Cases-MySQL CTAS的变化create table as
ctas报错很常见，当前数据库为RDS上的版本为8.0.18
但是再另外一个环境执行却没有任何问题，GTID模式和强一致性也开启了，那么是为什么？
mysql> create table test as select * from x;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show variables like '%gtid%';
+----------------------------------+----------------------------------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------+----------------------------------------------------------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | 784c080a-0490-11ec-80c5-c2d49fafca8c:1-1077081,
f7ad72e2-3219-11ec-9a61-0e4925de1a7e:1-15437 |
| gtid_executed_compression_period | 0 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | 784c080a-0490-11ec-80c5-c2d49fafca8c:1-1077071 |
| session_track_gtids | OFF |
+----------------------------------+----------------------------------------------------------------------------------------------+
9 rows in set (0.01 sec)
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.25 |
+-----------+
1 row in set (0.00 sec)找到官方文档一篇文章，翻译其中关键信息如下
https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html#atomic-ddl-characteristics
https://dev.mysql.com/doc/refman/8.0/en/replication-features-create-select.html
https://dev.mysql.com/doc/refman/8.0/en/create-table-select.html
As of MySQL 8.0.21, on storage engines that support atomic DDL, the CREATE TABLE ... SELECT statement is logged as one transaction in the binary log when row-based replication is in use. Previously, it was logged as two transactions, one to create the table, and the other to insert data. A server failure between the two transactions or while inserting data could result in replication of an empty table. With the introduction of atomic DDL support, CREATE TABLE ... SELECT statements are now safe for row-based replication and permitted for use with GTID-based replication.
从MySQL 8.0.21开始，如果存储引擎（InnoDB）支持原子DDL，create table ... select（CTAS）在基于row模式复制时，CTAS语句作为一个事务记录在binlog中。之前的版本，它是被分为两个事务记录在binlog中的，一个是创建表操作，一个是数据插入操作。在这两个事务之间或插入数据时发生服务器故障可能导致复制空表。但是随着原子DDL支持的引入，CTAS语句现在对于row模式的复制是安全的，并且允许与基于GTID的复制一起使用。
On storage engines that support both atomic DDL and foreign key constraints, creation of foreign keys is not permitted in CREATE TABLE ... SELECT statements when row-based replication is in use. Foreign key constraints can be added later using ALTER TABLE.
如果存储引擎同时支持原子DDL和外键约束，当使用row模式复制时选择语句，那么CTAS中语句中不允许创建外键。表创建完成后续可以再使用alter table方式添加外键约束。
最开始我以为是如果表中有外键，不允许使用ctas，其实不对，测试如下：
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` int NOT NULL,
`c2` int DEFAULT NULL,
PRIMARY KEY (`c1`),
KEY `i_t1_c2` (`c2`),
CONSTRAINT `t1_foreign_key` FOREIGN KEY (`c2`) REFERENCES `t2` (`c1`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`c1` int NOT NULL,
`c2` int DEFAULT NULL,
PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> create table test as select * from t1;
Query OK, 1 row affected (0.10 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`c1` int NOT NULL,
`c2` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)When CREATE TABLE ... SELECT is applied as an atomic operation, a metadata lock is held on the table while data is inserted, which prevents concurrent access to the table for the duration of the operation.
CTAS创建表作为一个原子操作，插入数据时在表上保留元数据锁，这会在操作期间阻止对表的并发访问，后面找时间我们一起看按下，CTAS主要是什么级别的锁。
更多文章欢迎关注本人公众号，搜dbachongzi或扫二维码
作者：姚崇 Oracle OCM、MySQL OCP、Oceanbase OBCA、PingCAP PCTA认证，擅长基于Oracle、MySQL Performance Turning及多种关系型 NoSQL数据库。
腾讯云开发者
扫码关注腾讯云开发者
领取腾讯云代金券