MySQL concat() 创建要在查询中使用的列名?

2021-11-20 00:00:00 concat mysql

我想以列名的第一部分是字符串而第二部分是作为另一个查询结果的数字的方式连接列名.

I would like to concatenate column names in a way that the first part of the column name is a string and the second part is a number which is the result of another query.

例如:

SELECT CONCAT('column', mytable.mycolumn) FROM table ...

这可以以某种方式完成吗?这样它不会给我错误，但我没有得到预期的结果，而且连接似乎不起作用.

Can this be done in some way. This way it doesn't give me errors but I don't get the expected result and it seems the concatenation doesn't work.

推荐答案

我之前说过不能这样做，但我错了.我最终自己需要这样的东西，所以我环顾四周，发现 服务器端准备好的语句让您可以从字符串构建和执行任意 SQL 语句.

I previously said that this couldn't be done, but I was wrong. I ended up needing something like this myself so I looked around, and discovered that server-side prepared statements let you build and execute arbitrary SQL statements from strings.

这是我刚刚用来证明这个概念的一个例子:

Here is an example I just did to prove the concept:

set @query := (
  select concat(
    "select",
      group_concat(concat("\n  1 as ", column_name) separator ','),
    "\nfrom dual")
  from information_schema.columns
  where table_name = 'columns')
;
prepare s1 from @query
;
execute s1
;
deallocate prepare s1
;

相关文章