MySQL:多列值作为1个逗号分隔的字符串? [英] MySQL : multiple column values as 1 comma-separated string?

问题描述

假设我有一个选择查询，例如:

Suppose I have a select query like :

SELECT * FROM tablename

和表中的列:field1，field2和field3

and in the table are columns : field1, field2 and field3

有人知道是否有可能获得仅包含1行和1个字段的结果集，并用逗号分隔列的值，例如:

Does anyone know if it's possible to get a resultset with only 1 row with 1 field, with comma separated values of the columns, like this :

"fieldvalue1, fieldvalue2, fieldvalue3"

问题是我事先不知道表的列名...

Problem is that I don't know the column names of the table in advance...

另一个问题是，准备好的语句不是要走的路，因为所有这些都应在触发器内完成，而MySQL不允许触发器内进行动态游标/选择.

Another problem is that a prepared statement is not the way to go, since all this should be done from within a trigger, and MySQL doesn't allow dynamic cursors/selects inside a trigger.

推荐答案

我进行了一些研究，仅在GROUP_CONCAT正确地确定了列名的情况下才进行.但是问题是，

I have done some research and only came as far as GROUP_CONCATenating the column names correctly. But the problem is, that

SELECT (SELECT GROUP_CONCAT( cols.column_name) FROM (SELECT column_name FROM information_schema.columns WHERE table_name='test_table') as cols) FROM test_table

将为每个表行返回一个相同的包含列名的连接字符串，而不是将其作为外部select语句的列名进行评估并返回实际值.

will return one and the same concatenated string containing the column names, once for each table row, instead of evaluating it as the column names for the outer select statement and returning the actual values.

从我在所有讨论此类问题的论坛上所阅读的(有很多)来看，如果没有准备好的声明，实际上是没有办法使它起作用的.

From what I have read in all of the forums discussing this kind of question (and there were many), there is really no way of getting this to work without prepared statements.

我只能想到另一种方法，那就是在每个表上都有一个专用列，您可以在其中将INSERT或UPDATE上的各个列值连接在一起，因此您可以简单地选择一个字段而不是完整的字段集.

I can only think of one other way to do this, and that would be having a dedicated column on each table, where you concatenate the individual column values on INSERT or UPDATE, so you can simply select this one field instead of the full set of fields.

这篇关于MySQL:多列值作为1个逗号分隔的字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！