MySQL 临时表插入 [英] MySQL Temp table Insert

问题描述

我正在使用 MySQL 并尝试创建一个临时表.我将在 PHP 中执行 2 个 while 循环语句来填充临时表.首先，虽然我似乎无法让插入到临时表中工作.我已经尝试了许多不同的版本，有些使用#"作为表和各种东西(SQL 服务器和 MySQL 命令有区别吗?).这是我的最后一次尝试(P.S Select 语句本身可以正常工作).

I'm using MySQL and trying to create a temp table. I will be doing a 2 while loop statements in PHP to populate the temp table. Firstly though I can't seem to get the Insert into temp table to work. I've tried many different versions of this, some using '#' for the table and various things (are there differences in SQL server and MySQL commands?). Here's my last attempt (P.S the Select statement works fine on its own).

CREATE TEMPORARY TABLE temp
(
aID varchar(15) NOT NULL,
bID varchar(15) NOT NULL
)
INSERT INTO temp
SELECT aID, bID
FROM tags
WHERE placeID = "abc" AND tagID = "def";

感谢帮助！

另外，只是一个一般的问题……这个查询必须运行很多次.使用临时表会好还是会导致服务器问题?

Also, just a general Q...this query will have to be run many times. Will using temp tables be OK or cause the server issues?

推荐答案

正在研究 Code-Monk 所写的内容，请考虑以下事项:

working on what Code-Monk wrote, consider the following:

drop procedure if exists uspK;
DELIMITER $$
create procedure uspK ()
BEGIN
    drop temporary table if exists temp; -- could be some other random structure residue

    create temporary table temp
    SELECT aID, bID
    FROM tags
    WHERE placeID = "abc" AND tagID = "def";

    -- use the temp table somehow
    -- ...
    -- ...
    -- ...

    drop temporary table temp; -- otherwise it survives the stored proc call
END
$$ -- signify end of block
DELIMITER ; -- reset to default delimiter

测试存储过程

call uspK(); -- test it, no warnings on edge conditions

不该做什么

人们不会在以下方面找到很多运气.如果你这么认为，运行几次；

What not to do

One would not find much luck with the following. If you think so, run it a few times;

drop procedure if exists uspK;
DELIMITER $$
create procedure uspK ()
BEGIN
    -- drop temporary table if exists temp;

    create temporary table if not exists temp
    SELECT aID, bID
    FROM tags
    WHERE placeID = "abc" AND tagID = "def";

    -- use the temp table somehow
    -- ...
    -- ...
    -- ...

    -- drop temporary table temp; -- otherwise it survives the stored proc call
END
$$ -- signify end of block
DELIMITER ; -- reset to default delimiter

因为如果临时表不存在则创建临时表是不稳定的

在对简单的分隔符主题有些流利之前，不应开始编写存储过程.在此处名为分隔符的部分中介绍了它们.只是希望能避免在这么简单的事情上浪费你不必要的时间，而不是浪费大量的调试时间.

One should not embark into writing stored procs until somewhat fluent on the simple topic of DELIMITERS. Wrote about them in a section here called Delimiters. Just hoping to head you off from unnecessary wasted time on such a simple thing, than can waste a lot of debugging time.

此外，在您的问题以及该参考文献中，请记住，表的创建是 DDL，可以 占整体的很大比例分析(性能).与使用预先存在的表相比，它会减慢 proc 的速度.人们可能认为调用是即时的，但事实并非如此.因此，就性能而言，使用预先存在的表并将结果放入自己的分段 rowId 比承受 DDL 开销要快得多.

Also, here in your question, as well as in that reference, keep in mind that the creation of tables is DDL that can have a large percentage of the overall profiling (performance). It slows down a proc versus using a pre-existing table. One might think the call is instantaneous, but it is not. As such, for performance, using a pre-existing table with ones results put into their own segmented rowId is much faster than enduring DDL overhead.

这篇关于MySQL 临时表插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！