MySQL存储函数-动态/可变表和列名 [英] MySQL Stored Functions - Dynamic/Variable Table & Column Names

问题描述

今天早上，我正在学习MySQL中的存储过程/函数/触发器，尝试在查询中使用变量表和列名称时遇到了一些问题.

I'm learning stored procedures/functions/triggers in MySQL this morning and I'm having some problems trying to use variable table and column names in queries.

DELIMITER |
USE R2R |
DROP FUNCTION IF EXISTS getCategoryName |
CREATE FUNCTION getCategoryName(LMID INT, levelNum INT)
    RETURNS VARCHAR(255)
    BEGIN

        DECLARE levelName VARCHAR(255) DEFAULT makeLevelName(levelNum);
        DECLARE levelID INT;
        DECLARE levelNameID VARCHAR(255) DEFAULT CONCAT(levelName, 'ID');
        DECLARE ret VARCHAR(255);

        SELECT @levelNameID INTO levelID FROM LevelMaster WHERE id=LMID;
        SELECT description INTO ret FROM @levelName WHERE id=levelID;
        RETURN ret;
    END;
|
DROP FUNCTION IF EXISTS makeLevelName |
CREATE FUNCTION makeLevelName(levelNum INT)
    RETURNS VARCHAR(255)
    BEGIN

        DECLARE word VARCHAR(255);
        DECLARE ret VARCHAR(255);
        IF levelNum=2 THEN SET word='Two';
        ELSEIF levelNum=3 THEN SET word='Three';
        ELSEIF levelNum=4 THEN SET word='Four';
        ELSEIF levelNum=5 THEN SET word='Five';
        END IF;

        SET ret=CONCAT('Level', word);

        RETURN ret;
    END;
|
SELECT getCategoryName(347, 2) |

这是导致问题的第一个函数(getCategoryName)，我需要将标有@的两个变量用作表/列的名称-这两行:

It's the first function (getCategoryName) that's causing me the problems, I need the two variables marked with @ to be the table/column names - these two lines:

SELECT @levelNameID INTO levelID FROM LevelMaster WHERE id=LMID;
SELECT description INTO ret FROM @levelName WHERE id=levelID;

如果可能的话，我希望将此函数保留为函数而不是过程，但是如果唯一的方法，我将接受过程的答案.

I want to keep this function as a function rather than a procedure if possible, but would accept answers for a procedure if it's the only way.

感谢您的帮助，

理查德

推荐答案

将此用户/全局变量与PREPARE&执行:

Use User/Global Vars for this along with PREPARE & EXECUTE:

SET @columnName='myColumn';
SET @tableName='myTable';
SET @whatEver='requiredValue';

SET @query=CONCAT('SELECT ', @columnName, ' FROM ', @tableName, ' WHERE Column=', @whatEver);
PREPARE QUERY FROM @QUERY;
EXECUTE QUERY;

还没有测试过这个EXACT代码，但是遵循这些原则的代码还是可以的.还必须位于过程内，不能与函数或触发器一起使用，如果有人对此有疑问，请发表.

Haven't tested this EXACT code but something along these lines will work. Also has to be inside a Procedure, cannot be used with a function or trigger, if anyone has a soloution for that then please post.

这篇关于MySQL存储函数-动态/可变表和列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！