MySQL连接所有列

2021-11-20 00:00:00 database mysql

为什么我们不能在 MySQL 中使用 * 关键字进行连接?

Why can we not concatenate in MySQL using the * keyword?

SELECT concat(*) FROM table

或 

SELECT group_concat(*) FROM table

有没有其他方法可以在不显式使用列名的情况下访问列中的值?

Is there any other way we could access values in a column without explicitly using the columns name?

推荐答案

要连接表中的所有列，不能使用 * 关键字，但需要明确列出所有列:

To concatenate all columns in a table, you can't use the * keyword, but you need to explicitly list all columns:

SELECT CONCAT(col1, col2, col3, ....)
FROM yourtable

或者您可能想要使用将跳过空值的 CONCAT_WS:

or you might want to use CONCAT_WS that will skip null values:

SELECT CONCAT_WS(',', col1, col2, col3, ....)
FROM yourtable

如果您不想手动指定所有列名，您可以使用动态查询.此查询将返回表的所有列名:

If you don't want to specify all column names manually, you could use a dinamic query. This query will return all column names of your table:

SELECT `column_name` 
FROM   `information_schema`.`columns` 
WHERE  `table_schema`=DATABASE() 
       AND `table_name`='yourtable';

并使用 GROUP_CONCAT 您可以获得所有列名称的列表:

and using GROUP_CONCAT you can obtain a list of all column names:

GROUP_CONCAT(CONCAT('`', column_name, '`'))

引用，以逗号分隔的格式:

quoted, in a comma separated format:

`col1`,`col2`,`col3`,`col4`,...

所以现在我们拥有了动态创建查询的所有元素:

so now we have all the elements to create our query dinamically:

SELECT
  CONCAT(
    'SELECT CONCAT_WS(\'\',',
    GROUP_CONCAT(CONCAT('`', column_name, '`') ORDER BY column_name),
    ') AS all_columns FROM yourtable;')
FROM   `information_schema`.`columns` 
WHERE  `table_schema`=DATABASE() 
       AND `table_name`='yourtable'
INTO @sql;

此查询会将@sql 字符串设置为类似:

this query will set the @sql string to something like:

SELECT CONCAT_WS('', col1, col2, col3, ....) AS all_columns FROM yourtable

这段代码将执行它:

PREPARE stmt FROM @sql;
EXECUTE stmt;

请参阅 fiddle 此处.

Please see fiddle here.

相关文章