2

I am working on a MySQL stored procedure.

I need to create a temp table and I want to access this temp table whenever I execute this stored procedure.

I know we can't access the temp table in another request in MySQL.

Is there a way to create a temp table like globally, or how can I access the temp table across the multiple requests?

4
  • 1
    If you need data persisted across sessions, are you really sure you need a temp table instead of a boring old ordinary one?
    – Mat
    Commented May 22, 2013 at 6:59
  • if is can be possible, i will go ahead to continue, otherwise i need to create a ordinary table.
    – venkat
    Commented May 22, 2013 at 7:16
  • 1
    The thing is you don't say how or when the data should be preserved or dropped from that "temp" table. So there's not really enough information AFAICT. Please edit your post to explain exactly what you're trying to achieve. An example use-case would make it clear for everybody.
    – Mat
    Commented May 22, 2013 at 7:17
  • @Venkat Can you differentiate normal table and temp table in your understanding?
    – Mannoj
    Commented May 22, 2013 at 10:56

3 Answers 3

1

It is not temporary, if you want to access it from multiple requests, because there is no way for mysql to know when to delete it. So you must create it manually and delete it manually. You can use a MEMORY engine, which will have similar characteristics to temp table.

1

If you have to create a table that is available to all and you have DB Connections that are persistently open, your could manage it as follows:

CREATE TEMP TABLE (One Time Setup)

Step 01: Create the following startup script:

cd /var/lib/mysql
echo "DROP DATABASE IF EXISTS globaltempDB;" > init_db.sql
echo "CREATE DATABASE globaltempDB;" >> init_db.sql
echo "CREATE TABLE globaltempDB.globaltemptable (" >> init_db.sql
echo "id bigint not null auto_increment," >> init_db.sql
echo "process_id int not null," >> init_db.sql
.
.
.
echo "dt DATETIME," >> init_db.sql
echo "PRIMARY KEY (id)," >> init_db.sql
echo "KEY process_id (process_id)," >> init_db.sql
echo "KEY dt (dt)" >> init_db.sql
echo ") ENGINE=InnoDB;" >> init_db.sql
chown mysql:mysql init_db.sql

Step 02: Add this to /etc/my.cnf

[mysqld]
init-file=/var/lib/mysql/init_db.sql

Step 03: Login to mysql and execute the script by hand

mysql> source /var/lib/mysql/init_db.sql

These steps handle the creation of the temp table when restarting mysqld.

STORED PROCEDURE

You need to retrieve the Connection ID using the CONNECTION_ID() function:

SET @ConnID = CONNECTION_ID();

Then, insert any data you want into the globaltempDB.globaltemptable and always access the needed data by using @ConnID. If you need to access data create by other connections, you have nothing stopping you from doing so.

PERIODIC MAINTENANCE

You may have to have to periodically cleanup the table based on datetime

EXAMPLE : To delete all data except stuff from the last 2 hours

DELETE FROM globaltempDB.globaltemptable WHERE dt < (NOW() - INTERVAL 2 HOUR);

You can set up a crontab job to do this every hour

You can also schedule a nightly job to defrag the table with this:

OPTIMIZE TABLE globaltempDB.globaltemptable;

Give it a Try !!!

0

You can't create a global temp table in MySQL. But you can create a non-temporary memory table. If the server shuts down, data will be lost, table itself will not. However, you can create an Event which drops the table after a while, for example after 30 minutes, or at a given time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.