Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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 May 22 '13 at 6:59
    
if is can be possible, i will go ahead to continue, otherwise i need to create a ordinary table. –  venkat May 22 '13 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 May 22 '13 at 7:17
    
@Venkat Can you differentiate normal table and temp table in your understanding? –  Mannoj May 22 '13 at 10:56

3 Answers 3

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 !!!

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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