MySQL报错之1786 - Statement violates GTID consistency: CREATE TABLE ... SELECT

一、报错截图

二、场景描述

使用create table xxx2 as select ... from xxx1语句时报错，这是由于MySQL在5.6及更高的版本添加了enforce_gtid_consistency 这个参数，默认设置为true，只允许能够保障事务安全，并且能够被日志记录的SQL语句被执行。

create table mx_temp_1(
	id int(10) primary key,
	username varchar(100),
	age int(3),
	sex varchar(10)
);

insert into  mx_temp_1 values(1,'张三','28','男');

create table mx_temp_2 as select * from mx_temp_1;

三、解决方案

将create table xxx2 as select ... from xxx1语句拆分为两端语句来执行，如下所示：

create table mx_temp_1(
	id int(10) primary key,
	username varchar(100),
	age int(3),
	sex varchar(10)
);

insert into  mx_temp_1 values(1,'张三','28','男');

create table mx_temp_2 like mx_temp_1;
insert into mx_temp_2 select * from mx_temp_1;