MySQL:从文本变量准备的语句不起作用

Question

我在 MySQL 中有一个存储过程，用于创建 SQL 查询，例如:

select
a.street,
a.bild,
a.korp,
a.flat,
a.serial_num,
nlog.value
from(  
    select 
    cli.street,
    cli.bild,
    cli.korp,
    cli.flat,
    dev.serial_num,
    mlog.value,
    max(mlog.id) as $max_id
    from mbus_clients as cli
    join mbus_devices as dev 
    on dev.id_client=cli.id
    join mbus_log as mlog
    on mlog.serial_num=dev.serial_num  
    where street like CONCAT($in_street,'%')
 AND bild=1 AND korp='t' AND flat=1
)as a
join mbus_log as nlog 
on a.$max_id = nlog.id;

此查询工作正常并存储在类型为:text(65535) 的 $query 变量中。当我尝试 PREPARE stmnt1 FROM 'SELECT' 时，效果很好，但是当我尝试 PREPARE stmnt1 FROM $query 时，mysql 说语法中有一些错误。有什么问题吗？

    DROP PROCEDURE IF EXISTS `mbusGetClientsByStreet`;;
    CREATE DEFINER = 'root'@'chl.rontel.ru' PROCEDURE `mbusGetClientsByStreet`(
        $in_street char(50),
        $in_amount int(11),
        $in_bild int(11),
        $in_korp char(1),
        $in_flat int(11)
        )
        NOT DETERMINISTIC
        CONTAINS SQL
        SQL SECURITY DEFINER
        COMMENT ''
    begin
    /*Скопин C.С. 01.09.2014
    получение списка клиентов по части названия улицы и полного адреса*/
        declare $query text(65535);
        set $query ="
        select
        a.street,
        a.bild,
        a.korp,
        a.flat,
        a.serial_num,
        nlog.value
        from(  
            select 
            cli.street,
            cli.bild,
            cli.korp,
            cli.flat,
            dev.serial_num,
            mlog.value,
            max(mlog.id) as $max_id
            from mbus_clients as cli
            join mbus_devices as dev 
            on dev.id_client=cli.id
            join mbus_log as mlog
            on mlog.serial_num=dev.serial_num  
            where street like CONCAT($in_street,\'%\')
        ";
        if $in_bild is NOT NULL  THEN

        SET $query = CONCAT($query,' AND bild=',$in_bild);
            if $in_korp is NOT NULL THEN

                set $query = CONCAT($query,' AND korp=\'',$in_korp,'\'');

            end if;
            if $in_flat is NOT NULL THEN
                set $query = CONCAT($query,' AND flat=',$in_flat);
            end if;
        end if;
        set $query=CONCAT($query,'
        )as a
        join mbus_log as nlog 
        on a.$max_id = nlog.id;
        ');
PREPARE stmt1 FROM $query;
        SELECT $query;
    end;

Answer 1

我的猜测是变量没有您期望的值。例如，如果 undefined variable ，那么查询中将有空位置。像这样的东西:

on a. = nlog.id;

这会产生错误。

或者，您可能有一个值，例如“max id”并且具有:

on a.max id = nlog.id

我建议您显式地将值放入 SQL 字符串中，然后在变量替换后打印出该字符串。这应该会使错误更清楚。