MySQL concat() 创建要在查询中使用的列名？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/985842/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL concat() to create column names to be used in a query?
提问by markus
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.
我想以列名的第一部分是字符串而第二部分是作为另一个查询结果的数字的方式连接列名。
For example:
例如：
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.
这可以以某种方式完成。这样它不会给我错误，但我没有得到预期的结果，似乎连接不起作用。
回答by Chris Vest
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 statementslet you build and execute arbitrary SQL statements from strings.
我之前说过这是不可能的，但我错了。我自己最终需要这样的东西，所以我环顾四周，发现服务器端准备好的语句允许您从字符串构建和执行任意 SQL 语句。
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
;
回答by Arjan
If the number of columns is fixed, then a non-dynamic approach could be:
如果列数是固定的，那么非动态方法可能是：
select
case mytable.mycolumn
when 1 then column1 -- or: when 'a' then columna
when 2 then column2
when ...
else ...
end as my_semi_dynamic_column
from ...
回答by Darryl Hein
I don't believe you can do this with CONCAT()and CONCAT_WS(). I'd recommend using the langauge you are working with the create the field names. Doing it this way would be pretty scary, depending on where the data in the database came from.
我不相信你可以用CONCAT()and做到这一点CONCAT_WS()。我建议使用您正在使用的语言来创建字段名称。这样做会非常可怕，这取决于数据库中的数据来自哪里。
回答by Steve
I would suggest looking at information_schema. The following code is untested but should theoretically work. Obviously replace your table name with an appropriate table name or link to information_schema.tables and use the table_type in your where clause
我建议查看information_schema。以下代码未经测试，但理论上应该有效。显然，用适当的表名或链接到 information_schema.tables 替换你的表名，并在你的 where 子句中使用 table_type
select concat('column', column_name) from information_schema.columns where table_name ='your table name'
回答by denizvatan
You can easily use following query:
您可以轻松使用以下查询：
SELECT group_concat( COLUMN_NAME) FROM information_schema.columns WHERE table_name ='your table name';
