MySQL - 在更新时使用动态 sql

Question

我正在使用 MySQL 5.6 并尝试执行如下所示的简单过程。

drop procedure if exists procsBook;

DELIMITER $$

CREATE PROCEDURE procsBook()

main:begin

DECLARE abc VARCHAR(256);

DECLARE def VARCHAR(256);

DECLARE field_name2 VARCHAR(256);

DECLARE field_names VARCHAR(256);

DECLARE bkid BIGINT(20);

SET abc:= NULL;

SET def:= NULL;

SET field_name2:= NULL;

SET field_names:= NULL;

SET bkid:= 0;

select a.field_name,b.column_name into abc,def from  table1 a join table2 b where a.field_label=b.field_label and b.table_name='Table13' and b.section_name='English' group by a.field_name limit 1;

IF(abc is not null)
then

IF (SUBSTRING(abc,7,1)=1)

THEN

SELECT book1,bookId INTO field_names,bkid FROM shelf1 WHERE rowstate=0;

SET @vSelectQury = CONCAT('update shelf2 set def = ' , field_names, ' where book_id = ', bkid);
PREPARE vDynamicSSql FROM @vSelectQury;

EXECUTE vDynamicSSql;

DEALLOCATE PREPARE vDynamicSSql;

END IF;

END IF;

END $$
DELIMITER ;

调用:

call procsBook();

这会引发以下错误:

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 'where book_id = 22' at line 1

请问有什么帮助吗？

Answer 1

我认为您需要在 field_names 周围添加引号。

SET @vSelectQury = CONCAT('update shelf2 set ', def, ' = \'' , field_names, '\' where book_id = ', bkid); PREPARE vDynamicSSql FROM @vSelectQury;

(或者如果您的设置不同，您可能需要更改 ' 字符的转义)

已更新您不应直接使用 def，而应在本例中使用 def 的值。