MySQL连接所有列 [英] MySQL concatenating all columns
问题描述
为什么我们不能使用*关键字连接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`,...
所以现在我们有了所有的元素查询dinamically:
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.
这篇关于MySQL连接所有列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!