MySQL concat()创建要在查询中使用的列名? [英] MySQL concat() to create column names to be used in a query?

问题描述

我想以这样的方式连接列名:列名的第一部分是一个字符串，第二部分是一个数字，这是另一个查询的结果.

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
;

这篇关于MySQL concat()创建要在查询中使用的列名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！