变量表名 MySQL 存储函数 [英] Variable Table Name MySQL Stored Function

问题描述

我有几个维护记录树结构的 MySQL 表.每条记录都有一个 ID 和一个父字段.我想编写一个存储函数来获取父 ID，给定一个记录 ID.

I have several MySQL tables that maintains a tree structure of records. Each record have an ID and a Parent field. I want to write a stored function to get the parent ID, given a record ID.

以下是我第一次尝试，不正确.我的问题是我不知道如何使用变量表名.

The following is my first attempt, and it's incorrect. My problem is I do not know how to use variable table name.

delimiter $$

create function parent(
    tableName varchar(15),
    nodeId    int
) returns int
begin
    declare p int;
    select parent into p from tableName where id=nodeId;
    return p;
end$$

请帮忙.谢谢！

推荐答案

经过一番研究，显然存储函数在这种情况下不起作用，因为存储函数无法执行动态 SQL.我将实现更改为存储过程.

After some research, apparently a stored function will not work in this case due to the fact stored functions cannot execute dynamic SQL. I change my implementation to a stored procedure.

delimiter $$

create procedure parent(tableName varchar(15), nodeId int)
begin
    set @s := concat('select parent from ', tableName, ' where id =', nodeId);
    prepare query from @s;
    execute query;
    deallocate prepare query;
end$$

delimiter ;

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