在mysql中多次串联字符串中的变量

j5fpnvbx  于 2021-06-18  发布在  Mysql
关注(0)|答案(2)|浏览(377)

场景:我试图将一个字符串设置为一个变量,然后将其传递给一个准备好的语句。我正在使用“concat”函数来创建将要传递的字符串。
查询:

set @floatvar := 'test1'    
set @random_var2 := concat('SELECT ', @floatvar, ' AS Fields, COUNT(CASE WHEN ', @floatvar, ' IS NULL THEN 1 END) AS NullCount');

obs:我的查询有更多的部分要包含在字符串中,但即使只有这一部分,我也已经有麻烦了,所以我一步一步地做。
问题:当我尝试运行这个程序时,第2行出现语法错误(SQLerror1064)。我不知道为什么会这样。
问题:是什么导致了这个问题,如何解决?

nwsw7zdq

nwsw7zdq1#

set @floatvar := 'test1' ;

set @random_var2 := (select concat('SELECT ', @floatvar, ' AS Fields, COUNT(CASE WHEN ', @floatvar, ' IS NULL THEN 1 END) AS NullCount'));

select @random_var2;

给这个

SELECT test1 AS Fields, COUNT(CASE WHEN test1 IS NULL THEN 1 END) AS NullCount;

结果

ERROR 1054 (42S22): Unknown column 'test1' in 'field list'

如果你这么做

prepare sqlstmt = @random_var2;
execute sqlstmt;
deallocte prepare sq;stmt;

结果

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '= @random_var2' at line 1

最好在激发到动态sql之前检查prepared语句的工作情况,因为来自动态sql的错误消息可能没有帮助。

mbjcgjjk

mbjcgjjk2#

没有选择。。变成。。这样地:

SELECT 
 concat('SELECT ', @floatvar, ' AS Fields, COUNT(CASE WHEN ', @floatvar, ' IS NULL THEN 1 END) AS NullCount')
INTO @myquery;

样品

MariaDB [test]> SET @floatvar := 'test1'    ;
Query OK, 0 rows affected (0.002 sec)

MariaDB [test]> SELECT
    ->  concat('SELECT ', @floatvar, ' AS Fields, COUNT(CASE WHEN ', @floatvar, ' IS NULL THEN 1 END) AS NullCount')
    -> INTO @myquery;
Query OK, 1 row affected (0.000 sec)

MariaDB [test]>
MariaDB [test]> SELECT @myquery;
+--------------------------------------------------------------------------------+
| @myquery                                                                       |
+--------------------------------------------------------------------------------+
| SELECT test1 AS Fields, COUNT(CASE WHEN test1 IS NULL THEN 1 END) AS NullCount |
+--------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [test]>

相关问题