Create Tables with Dynamic Names via Stored Procedure

Question

I am trying to create tables with dynamic names. The code I am getting errors from is a piece of dynamic SQL. I am unsure what the issue is. I am new to dynamic SQL and stored procedures.

    PREPARE stmt FROM "CREATE TABLE `?` SELECT lat, lon, nfldtime FROM position_reports where mmsi = ? ORDER BY id DESC LIMIT 100";    

    EXECUTE stmt USING CONCAT("mmis", FORMAT(vesselID,0)), vesselID;

The Error I get while trying to create the procedure is:

Script line: 4 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONCAT("mmis", FORMAT(vesselID,0)), vesselID;

I tried this:

EDIT:

from @Konerak answer this now works:

set @s = CONCAT("CREATE TABLE mmsi", vesselID, " SELECT lat, lon, nfldtime FROM position_reports where mmsi = ", vesselID, " ORDER BY id DESC LIMIT 100");
prepare createTable from @s;
EXECUTE createTable;
DEALLOCATE PREPARE createTable;

Answer 1

You can't replace a tablename by a ? in a prepared statement and then have the execute insert the table name.

Dynamic table names are usually a bad idea: it is better to make 1 big table, and add an extra (indexed) column for the "virtual table name".

If you really need dynamic tables, you'll have to concat the table name into the PREPARE itself.

Pro's and con's of dynamic table names

Suppose that instead of putting all users inside one table, users with columns ID, Name, e-mail, country, ..., you put them into dynamic tables, users_country, with columns ID, Name, e-mail, ...

• You can't easily query all tables. Say you want to know how many of your users are male - you have to query each table, and UNION the results. Won't be faster.

• You can physically split the tables using PARTITIONS anyway, but your logical view is still the same. Any advantage you think you have using seperate tables can usually be attained using another method.