如何用动态表名编写一个mysql函数? [英] How to write a mysql function with dynamic table name?

问题描述

我正在尝试编写一个执行以下操作的sql函数: 1-获取连接中使用的表名作为参数.

I'm trying to write a my sql function doing the following things: 1- get the table name used in join as a parameter.

但是我得到了mysql语法错误

but I get mysql syntax error

1064 - 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 'table DETERMINISTIC 
  BEGIN select `r`.`id` AS `id`, (case ' at line 2

这是我的查询

DELIMITER $$
CREATE FUNCTION getTranslation (tablename varchar(50),entity varchar(20),itemid int,lang char(3)) 
RETURNS table
DETERMINISTIC
BEGIN 
select 
    `r`.`id` AS `id`,
    (case
        when isnull(`t`.`descr`) then `r`.`descr_ml`
        else `t`.`descr`
    end) AS `descr`
from
    (tablename `r`
    left join `g001_translation` `t` ON ((`t`.`item_id` = `r`.`id`)))
END$$
DELIMITER ;

顺便说一下，我选择的部分可以与静态表名一起正常工作.

I the select part works fine with static table name by the way.

推荐答案

首先，如@eggyal所述，这不是解决问题的最佳方法.但这可以通过使用准备好的语句来完成.即

First up as mentioned by @eggyal this isn't the best way to go about things. But it can be done by using prepared statements. I.e.

DROP PROCEDURE IF EXISTS `exampleOfPrepareStatement`;

CREATE DEFINER = `user`@`%` PROCEDURE `exampleOfPrepareStatement`(inTableName VARCHAR(100))
    MODIFIES SQL DATA
    SQL SECURITY INVOKER
BEGIN

    SET @hr1 = CONCAT('
        INSERT INTO `',inTableName,'` (
            -- fields (can use parameters same as table name if needed)
        )
        -- either VALUES () or SELECT here
    ');

    -- Prepare, execute, deallocate
    PREPARE hrStmt1 FROM @hr1;
    EXECUTE hrStmt1;
    DEALLOCATE PREPARE hrStmt1;

END;

您当然可以根据需要添加字段名称等，或者使用SELECT或UPDATE等.这不是理想的选择，但是可以满足您的需求.

You can of course add in field names etc. as needed, or use a SELECT or UPDATE etc. This is not ideal, but will do what you are looking for.

在对具有不同字段名称(/表名称)的多个表执行相同维护之前，我不得不在某些地方使用此功能，因此与其重复编写20次相同的函数，不如使用这种类型的然后可以调用存储过程进行索引等操作.

I have had to use this in some places before where the same maintenance is being performed on multiple tables which have different field names ( / table names ) and so instead of writing the same function 20 times, instead I use this type of stored procedure which can then be called to do the indexing etc.

正如@eggyal所提到的，虽然这可能会按照您的要求进行，但可能并没有您所需要的.如果您可以提供更多信息，则可能会得到更好的解决方案.

As also mentioned by @eggyal , while this may do as you ask, it might not do as you need. If you can provide more information then you may get a better solution.

这篇关于如何用动态表名编写一个mysql函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！