3.27 Generating Unique Table Names
3.27.1 Problem
You need to create a table with a
name that is guaranteed not to exist already.
3.27.2 Solution
If you can create a
TEMPORARY table, it doesn't
matter if the name exists already. Otherwise, try to generate a value
that is unique to your client program and incorporate it into the
table name.
3.27.3 Discussion
MySQL is a multiple-client database server, so if a given script that
creates a transient table might be invoked by several clients
simultaneously, you must take care to keep multiple invocations of
the script from fighting over the same table name. If the script
creates tables using
CREATE TEMPORARY
TABLE, there is no problem because different
clients can create temporary tables having the same name without
clashing.
If you can't use CREATE
TEMPORARY TABLE because the
server version is older than 3.23.2, you should make sure that each
invocation of the script creates a uniquely named table. To do this,
incorporate into the name some value that is guaranteed to be unique
per invocation. A timestamp won't work, because
it's easily possible for two instances of a script
to be invoked within the same second. A random number may be somewhat
better. For example, in Java, you can use the
java.util.Random( ) class to create a table name
like this:
import java.util.Random;
import java.lang.Math;
Random rand = new Random ( );
int n = rand.nextInt ( ); // generate random number
n = Math.abs (n); // take absolute value
String tblName = "tmp_tbl_" + n;
Unfortunately, random numbers only reduce the possibility of name
clashes, they do not eliminate it. Process ID (PID) values are a
better source of unique values. PIDs are reused over time, but never
for two processes at the same time, so a given PID is guaranteed to
be unique among the set of currently executing processes. You can use
this fact to create unique table names as follows:
Perl:
my $tbl_name = "tmp_tbl_$$";
PHP:
$tbl_name = "tmp_tbl_" . posix_getpid ( );
Python:
import os
tbl_name = "tmp_tbl_%d" % os.getpid ( )
Note that even if you create a table name using a value like a PID
that is guaranteed to be unique to a given script invocation, there
may still be a chance that the table will exist. This can happen if a
previous 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 table if it
does exist by issuing the following statement:
DROP TABLE IF EXISTS tbl_name
Then you can go ahead and create the new table.
|
