Table Management - MySQL - page 134

Perl:
my $tbl_name = "tmp_tbl_$$" ;
Ruby:
tbl_name = "tmp_tbl_" + Process . pid . to_s
PHP:
$tbl_name = "tmp_tbl_" . posix_getpid ();
Python:
import os
tbl_name = "tmp_tbl_ %d " % os . getpid ()
The PID approach should not be used in contexts such as scripts run within multi‐
threaded web servers in which all threads share the same process ID.
Connection identifiers are another source of unique values. The MySQL server reuses
these numbers over time, but no two simultaneous connections to the server have the
same ID. To get your connection ID, execute this statement and retrieve the result:
SELECT CONNECTION_ID ();
It's possible to incorporate a connection ID into a table name within SQL by using
prepared statements. The following example illustrates this, referring to the table name
in the CREATE TABLE statement and a precautionary DROP TABLE statement:
SET @ tbl_name = CONCAT ( 'tmp_tbl_' , CONNECTION_ID ());
SET @ stmt = CONCAT ( 'DROP TABLE IF EXISTS ' , @ tbl_name );
PREPARE stmt FROM @ stmt ;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt ;
SET @ stmt = CONCAT ( 'CREATE TABLE ' , @ tbl_name , ' (i INT)' );
PREPARE stmt FROM @ stmt ;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt ;
Why execute the DROP TABLE ? Because if you create a table name using an identifier such
as a PID or connection ID guaranteed to be unique to a given script invocation, there
may still be a chance that the table already exists if an earlier invocation of the script
with the same PID created a table with the same name, but crashed before removing
the table. On the other hand, any such table cannot still be in use because it will have
been created by a process that is no longer running. Under these circumstances, it's safe
to remove the old table if it does exist before creating the new one.
Some MySQL APIs expose the connection ID directly without requiring any statement
to be executed. For example, in Perl DBI, use the mysql_thread_id attribute of your
database handle:
my $tbl_name = "tmp_tbl_" . $dbh -> { mysql_thread_id };