MySQL存储过程动态更改表名 [英] MySQL Stored Procedure dynamic change name of table

问题描述

我想在 sql 查询中动态更改表的名称.例如我有下一个存储过程:

I wanna change dynamicaly name of table in sql query. For example I have next stored procedure:

CREATE PROCEDURE NewProc(IN tableName varchar(64),IN message text)
BEGIN
    INSERT INTO tableName VALUES (message);
END;

我需要在运行时更改tableName，我可以做还是不做?谢谢.

I need to change tableName in runtime, Can I to do it or not? Thanks.

推荐答案

必须使用动态 SQL 才能准备和执行 一个 SQL 字符串，用于实现您所描述的内容.

You must use dynamic SQL to prepare and execute an SQL string, to achieve what you describe.

动态表名(或列名，或SQL关键字等)必须在准备前插入到SQL字符串中.您不能对这些动态元素使用查询参数.

Dynamic table names (or column names, or SQL keywords, etc.) must be interpolated into the SQL string before prepare. You can't use query parameters for these dynamic elements.

在将表名插入到 SQL 查询中时，请小心避免 SQL 注入漏洞.例如，您应该通过在 信息架构.

Be careful to avoid SQL injection vulnerabilities when you interpolate the table name into your SQL query. For example, you should check that the table name exists by looking it up in the information schema.

我同意@OMG Ponies 的评论——这是一种代码味道有多个具有相同结构的表，以便您对完全相同的列进行完全相同的插入.代码异味并不能保证您的设计很糟糕，但值得考虑.

I agree with the comment from @OMG Ponies -- it's a code smell that you have multiple tables with identical structure such that you want to do the exact same insert to the exact same column. Code smells aren't a guarantee that you've got a bad design, but it's worth considering.

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