MySQL Create table as select无法执行

最新推荐文章于 2025-04-17 13:05:16 发布

菜爷面馆

最新推荐文章于 2025-04-17 13:05:16 发布

阅读量2.2k

点赞数 1

分类专栏：存储文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/2301_76787421/article/details/134227442

版权

存储专栏收录该内容

19 篇文章

订阅专栏

本文介绍了在MySQL5.7中使用CreatetableasSelect遇到的GTID一致性错误，解释了GTID的概念以及MySQL5.7对enforce_gtid_consistency参数的改变。提供了解决方案，包括将原语拆分为两部分以确保事务安全和完整备份。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、前言

作为一个专业的DBA，在进行表结构变更或数据变更前做好备份那是必须的，今天在新系统上(MySQL版本是5.7) 使用Create table as Select时直接报 Statement violates GTID consistency: CREATE TABLE ... SELECT,瞬间懵逼了，看提示信息说是违反了GTID一致性。

二、GTID介绍

GTID（Global Transaction ID）,即全局事务标识符，它由UUID+TID构成，其中UUID指的是MySQL实例唯一标识，在/var/lib/mysql/auto.cnf文件定义（类似 server-uuid=ee9a3626-e24e-11eb-b13d-00163e0ab4aa )，TID代表该实例上已经提交事务的数量。

根据GTID可以知道事务最初是在哪个实例上提交的，可以更简单实现主从复制且更安全。

注：下次写一篇传统的主从复制和GTID主从复制的文章。

三、报错原因及解决方法

MySQL5.6及更低版本系统参数enforce_gtid_consistency 默认值为OFF，MySQL5.7及更高版本默认值为ON，当设置为ON时，MySQL只允许能够保障事务安全并且能够被日志记录的语句才会被执行，而像create table as select 或create temporarytable 这种一条语句中包括事务和非事务是不被允许执行的。

解决方法

1、直接enforce_gtid_consistency 为OFF,但不建议这么做。

2、Create table A as select * from B 拆成两部分 create table like A;insert into A as select * from B .

以前做单表备份时一直是用create table A as select ,但是这种备份其实是有些问题的，创建的新表中并没有旧表的primary key,Extra,auto_increment这些属性，如果误操作要恢复会有些麻烦，而拆成两条语句做备份就不存在这个问题。