MySQL Cookbook, 2nd Edition

Generating Unique Table Names

Problem

You need to create a table with a name that is guaranteed not to exist already.

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.

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 do not want to use a TEMPORARY table, you should make sure that each invocation of the script creates a uniquely named table and that it drops that table when it is no longer needed. To accomplish 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_" + ...