MySQL如何实现select into 临时表的功能

最新推荐文章于 2024-08-17 02:07:17 发布

三爷麋了鹿

最新推荐文章于 2024-08-17 02:07:17 发布

阅读量4.3k

点赞数

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

本文链接：https://blog.csdn.net/u800820/article/details/121322445

版权

mysql 专栏收录该内容

19 篇文章 1 订阅

订阅专栏

本文讲述了在MySQL中遇到的使用临时表进行数据处理时的性能问题。由于MySQL不支持`SELECT INTO`语法，作者尝试了`CREATE TABLE AS SELECT * FROM`方法，但因GTID一致性限制而失败。最终，通过`CREATE`临时表并逐步插入数据、进行JOIN操作，解决了问题。在完成数据处理后，临时表被删除。这个过程展示了如何在MySQL中优化复杂的SQL语句。

摘要由CSDN通过智能技术生成

最近在编写sql语句时，遇到两次将数据放temp表，然后将两次的temp表进行inner join，再供后续insert数据时使用的场景。写完后发现执行耗时较长，需要优化，于是将一条长长的sql语句拆分成一个sql脚本，用临时表去暂存数据后再进行inner join。

select into 临时表

首先想到的是使用select into这个写法：

select * into temp_test from user where id=007;

写完在Navicat执行报错，发现MySQL居然是不支持select into这种写法的，没办法，只能转换思路。

这个时候我又想起来有一个create table as select * from old_table的用法，想着是不是可以通过select出来的数据直接创建一张临时表。
写完去Navicat执行，这次又报错了：

Statement violates GTID consistency: CREATE TABLE ... SELECT.

搜索资料发现，由于MySQL在5.6及更高的版本添加了enforce_gtid_consistency这个参数，默认设置为true, 只允许保证事务安全的语句被执行。

没招儿，还得用原始方法去实现。

create 临时表

由于供后续使用的字段不超过十个，不算多，于是通过create方式创建表，后续使用数据后再删除这个表，逻辑上这就成了一个临时表。
大致的写法如下：

USE database;
-- 设置变量
SET @testCode='T001';

-- 创建临时表
DROP TABLE IF EXISTS temp_test;
CREATE TABLE IF NOT EXISTS `temp_test`(
`name` VARCHAR(255),
`caption` VARCHAR(255),
`order` INT(11),
...
`entityId` BIGINT(20)
);

INSERT INTO temp_test
select item.name,item.caption,item.order,item.id from item item
inner join base base on base.id=item.baseid
where base.num='test01'
and base.id='T01'

select id into @itemid from temp_test;
update user set systemid=@itemid where `code`=@testCode;

...

INSERT INTO `base` (`userId`,`entityId`,`name`,`caption`, ...)
SELECT tpitem.entityId,tpitem.CONCAT('pre_',tpitem.name),tpitem.caption,tpitem.order,...
from
(
select * from temp_test test inner join temp_test2 test2 on test.entityid=test2.entityid
) tpitem
WHERE NOT EXISTS (SELECT 1 FROM item WHERE `code`=@testCode limit 1);

-- 删除临时表
DROP TABLE temp_test;