Table Management - MySQL - page 133

• Some APIs support persistent connections or connection pools. These prevent
temporary tables from being dropped as you expect when your script ends because
the connection remains open for reuse by other scripts. Your script has no control
over when the connection closes. This means it can be prudent to execute the fol‐
lowing statement prior to creating a temporary table, just in case it's still in existence
from a previous execution of the script:
DROP TEMPORARY TABLE IF EXISTS tbl_name
The TEMPORARY keyword is useful here if the temporary table has already been
dropped, to avoid dropping any permanent table that has the same name.
4.4. Generating Unique Table Names
Problem
You need to create a table with a name guaranteed not to exist.
Solution
If you create a TEMPORARY table, it doesn't matter whether a permanent table with that
name exists. Otherwise, try to generate a value that is unique to your client program
and incorporate it into the table name.
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, take care that multiple invo‐
cations of the script do not fight 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 cannot or do not want to use a TEMPORARY table, make sure that each invocation
of the script creates a uniquely named table and drops the table when it is no longer
needed. To accomplish this, incorporate into the name some value guaranteed to be
unique per invocation. A timestamp won't work if it's possible for two instances of a
script to be invoked within the timestamp resolution. A random number may be better,
but random numbers only reduce the possibility of name clashes, not eliminate it. Pro‐
cess 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. Use this fact to create unique table names
as follows.