Accessing a temporary table multiple times in MySql

mysql temporary table doesn't exist
mysql check if temporary table exists
mysql 5.7 temporary tables
mysql temporary table performance
mysql can't reopen table
mysql update temporary table
mysql reuse temporary table
mysql rename temporary table

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:

  1. Namespacing. You can create multiple temporary tables with the same name in separate sessions.
  2. 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.

Use Temporary Table multiple time in MySQL Solutions, We help IT Professionals succeed at work. GET ACCESS. SEE HOW IT WORKS. Use Temporary Table multiple time in MySQL. In MySQL, a temporary table is a special type of table that allows you to store a temporary result set, which you can reuse several times in a single session. A temporary table is very handy when it is impossible or expensive to query data that requires a single SELECT statement with the JOIN clauses. In this case, you can use a temporary table to store the immediate result and use another query to process it. A MySQL temporary table has the following specialized features: A temporary table

Known bug (feature) - read http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html

Here's how i work around the problem...

drop table if exists employees;
create table employees
(
emp_id smallint unsigned not null auto_increment primary key,
name varchar(255) not null,
boss_id smallint unsigned null,
key (boss_id)
)
engine = innodb;

insert into employees (name, boss_id) values
('f00',null), 
  ('ali later',1), 
  ('megan fox',1), 
      ('jessica alba',3), 
      ('eva longoria',3), 
         ('keira knightley',5), 
            ('liv tyler',6), 
            ('sophie marceau',6);


drop procedure if exists employees_hier;

delimiter #

create procedure employees_hier
(
in p_emp_id smallint unsigned
)
begin

declare v_done tinyint unsigned default(0);
declare v_dpth smallint unsigned default(0);

create temporary table hier(
 boss_id smallint unsigned, 
 emp_id smallint unsigned, 
 depth smallint unsigned
)engine = memory;

insert into hier select boss_id, emp_id, v_dpth from employees where emp_id = p_emp_id;

/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */

create temporary table emps engine=memory select * from hier;

while not v_done do

    if exists( select 1 from employees e inner join hier on e.boss_id = hier.emp_id and hier.depth = v_dpth) then

        insert into hier select e.boss_id, e.emp_id, v_dpth + 1 
            from employees e inner join emps on e.boss_id = emps.emp_id and emps.depth = v_dpth;

        set v_dpth = v_dpth + 1;            

        truncate table emps;
        insert into emps select * from hier where depth = v_dpth;

    else
        set v_done = 1;
    end if;

end while;

select 
 e.emp_id,
 e.name as emp_name,
 p.emp_id as boss_emp_id,
 p.name as boss_name,
 hier.depth
from 
 hier
inner join employees e on hier.emp_id = e.emp_id
left outer join employees p on hier.boss_id = p.emp_id;

drop temporary table if exists hier;
drop temporary table if exists emps;

end #

delimiter ;

-- call this sproc from your php

call employees_hier(1);

B.4.6.2 TEMPORARY Table Problems, TEMPORARY tables are supported only by the InnoDB , MEMORY , MyISAM reopen table error also occurs if you refer to a temporary table multiple times in a​  SELECT statements that select from and insert into the same table, MySQL creates an internal temporary table to hold the rows from the SELECT, then inserts those rows into the target table. See Section 13.2.5.1, “INSERT SELECT Statement” . Evaluation of multiple-table UPDATE statements.

For this problem, where the temporary table is only to be read, I create a second temporary table as a copy of the first then use this in the query:

CREATE TEMPORARY TABLE t2 as (SELECT * FROM dates_with_entries);

Then

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 t2) 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
); 

MySQL Bugs: #98458: CTE referencing temporary table, multiple , Bug #98458, CTE referencing temporary table, multiple references, Allowing temporary tables to be referenced multiple times in same query  If you run the code in a PHP script, the temporary table will be destroyed automatically when the script finishes executing. If you are connected to the MySQL database server through the MySQL client program, then the temporary table will exist until you close the client or manually destroy the table.

I tried "WITH" for this problem but apparently it is not supported by MySQL on my server yet. I came across this solution that seems like working. It uses "CREATE VIEW" to replicate "WITH" in MySQL

http://guilhembichot.blogspot.com/2013/11/with-recursive-and-mysql.html

MySQL Temporary Table, In MySQL, a temporary table is a special type of table that allows you to store a temporary result set, which you can reuse several times in a single session. SELECT statements that select from and insert into the same table, MySQL creates an internal temporary table to hold the rows from the SELECT, then inserts those rows into the target table. See Section 13.2.5.1, “INSERT SELECT Statement” . Evaluation of multiple-table UPDATE statements.

Make Your MySQL Queries More Efficient with Temporary Tables, Knowing how to use temporary tables in your stored procedures is a great way to save time and effort, particularly when dealing with multiple  To create a Temporary Table in MySQL, we add the TEMPORARY keyword in the CREATE TABLE statement. Copy and paste the following SQL to your SQLyog free Community Edition query window. Note that the SQL needs to end with semi-colon if you have multiple queries in the query window.

MySQL - create temporary table, To create a Temporary Table in MySQL, we add the TEMPORARY keyword in the with MySQL) and connect to your MySQL database, a database session is created This error also occurs if you refer to a temporary table multiple times in a  If a TEMPORARY is created with the same name as an existing non-TEMPORARY table, the non-TEMPORARY table is hidden until the TEMPORARY table is dropped, even if the tables use different storage engines. There are known issues in using temporary tables with replication.

Inserting Stored Procedure Results into Temporary Table, Learn how to insert the results of a stored procedure into a temporary table in SQL to use the OPENROWSET statement, which allows you to access remote data from OPENROWSET is a one-time connection and data retrieval method, so it of returning a single result set (if multiple sets are provided, only the first result  If you have a temporary table and the replica crashes in between accesses to the temporary table, when you restart replication the temporary table no longer exists, and you are in trouble. This is only a problem with statement-based replication, which is how MySQL replication works until version 5.1, which is currently in beta.

Comments
  • Show some sample SQL for how you create and use the temporary table.
  • Ok so the only way to achieve my goal is using a hack by defining a real table.. Well I guess I will have to go with that idea if I don't want any code duplication..
  • If you already know what the query is going to look like, create a second temporary table with identical schema to the first temporary table, then do a INSERT INTO dates_with_entries2 SELECT * FROM dates_with_entires, and finally, in your ultimate query, refer to the first temporary table in one spot, and the second temporary table in the other.