两个链接访问MySQL临时表_在MySql中多次访问临时表

最新推荐文章于 2022-11-02 18:16:30 发布

雷永鹏

最新推荐文章于 2022-11-02 18:16:30 发布

阅读量180

点赞数

文章标签：两个链接访问MySQL临时表

本文链接：https://blog.csdn.net/weixin_42522389/article/details/113120600

版权

在MySQL中，遇到使用临时表作为中间结果持有者时，无法在同一会话中多次访问的问题。文章讨论了如何在不使用存储过程和游标的情况下，寻找替代临时表的方法。建议在临时表不可用时，创建一个实际的表，以实现相同的功能。临时表提供了命名空间和自动清理的优势，但如果只需要单次运行，可以创建一个唯一命名的真实表并在完成后删除。

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

I have tried to use a temporary table as an intermediate result holder for a SELECT statement. The problem is though that I can't access the temp table multiple times in other queries statement which I hoped would be possible i.e. makes the temp table useless.

Is there an alternative to temporary tables in MySql that allows me to extract my SQL statement.

I can't use store procedures (can't access them from the web-framework version used in the company) and I don't want to use a cursor.

Edit:

Well my code looks somewhat like this:

Temp table creation:

CREATE TEMPORARY TABLE dates_with_entries (

seq INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

datum VARCHAR(32)

);

INSERT INTO dates_with_entries (datum) SELECT datum AS Datum from project_times

WHERE user_id = 20 GROUP BY datum ORDER BY datum desc LIMIT 13;

Then the code where I use my temp table looks somewhat like this (I simpled it down to the problem I have..)

SELECT

...

FROM (SELECT entrie_date AS datum FROM dates_with_entries ) AS sub_result

INNER JOIN project_times

ON sub_result.datum = project_times.datum AND project_times.user_id = 20

LEFT JOIN works AS w ON project_times.work_id = w.id

LEFT JOIN sub_projects AS sp ON sp.id = w.sub_project_id

LEFT JOIN projects AS p ON p.id = sp.project_id

GROUP BY datum

UNION(

SELECT

FROM (SELECT entrie_date AS datum FROM dates_with_entries ) AS sub_result

INNER JOIN project_times AS pt ON pt.datum = sub_result.datum

INNER JOIN works AS w on w.id = pt.work_id

INNER JOIN sub_projects AS sp on w.sub_project_id = sp.id

INNER JOIN projects AS p ON sp.project_id = p.id

WHERE pt.user_id = 20

);

The numbers will be later on replaced by ruby, this is only for testing the SQL statement.

解决方案

One way around this is to simply create a "real" table, rather than a temporary table.

The advantages that temporary tables offer:

Namespacing. You can create multiple

temporary tables with the same name

in separate sessions.

Automatic cleanup. You don't need explicitly drop the table when you're done with

it. It goes away when you end your session

If #1 is crucial for you, then you probably need to stick with temporary tables. Otherwise, if only one instance of this program runs at a time, or if you create the table name dynamically to make it unique, I recommend that you choose an appropriately unique name for this table and create it as a "real" table, then drop it when you're done.