通过存储过程创建具有动态名称的表 [英] Create Tables with Dynamic Names via Stored Procedure

问题描述

我正在尝试使用动态名称创建表.我从中得到错误的代码是一段动态SQL.我不确定是什么问题.我是动态SQL和存储过程的新手.

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:

脚本行:4您的SQL语法有错误；查看手册 对应于您的MySQL服务器版本以获取正确的语法 在'CONCAT("mmis"，FORMAT(vesselID，0))，vesselID附近使用；

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;

我尝试过:

来自@Konerak的答案现在有效:

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;

推荐答案

您不能在准备好的语句中用?替换表名，然后让execute插入表名.

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

动态表名称通常是一个坏主意:最好制作1个大表，并为虚拟表名称"添加一个额外的(索引)列.

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".

如果您确实需要动态表，则必须将表名关联到PREPARE 本身.

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

假设您不是将所有用户放在一个表中，而是将它们放入动态表users_country中，该表包含列ID，Name，e-mail，country，...的users.具有列ID，Name，e-mail，...

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, ...

• 您无法轻松查询所有表.假设您想知道有多少用户是男性-您必须查询每个表，然后对结果进行UNION.不会更快.

• 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.

您仍然可以使用PARTITIONS物理拆分表，但是逻辑视图仍然相同.您认为使用单独表的任何优势通常都可以使用另一种方法来实现.

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.

这篇关于通过存储过程创建具有动态名称的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！