mysql动态添加表名 列名,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) |

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.

Thanks for you help,

Richard

解决方案

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;

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.