How to Concatenate All Columns in a MySQL Table?

Concatenating All Columns in MySQL

In MySQL, the * wildcard keyword cannot be used in the CONCAT() function to concatenate all columns in a table. Instead, you must explicitly list each column name:

<code class="sql">SELECT CONCAT(col1, col2, col3, ...)
FROM yourtable;</code>

Alternatively, you can use the CONCAT_WS() function to skip null values:

<code class="sql">SELECT CONCAT_WS(',', col1, col2, col3, ...)
FROM yourtable;</code>

If you prefer, you can avoid manually specifying column names by utilizing a dynamic query. Retrieve the column names of your table:

<code class="sql">SELECT `column_name`
FROM `information_schema`.`columns`
WHERE `table_schema` = DATABASE()
AND `table_name` = 'yourtable';</code>

Use GROUP_CONCAT to obtain a comma-separated list of column names:

<code class="sql">GROUP_CONCAT(CONCAT('`', column_name, '`'))</code>

Now, build your dynamic query by concatenating elements:

<code class="sql">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;</code>

This query will set the @sql variable to a string similar to:

<code class="sql">SELECT CONCAT_WS('', col1, col2, col3, ...) AS all_columns FROM yourtable;</code>

Finally, execute this dynamic query:

<code class="sql">PREPARE stmt FROM @sql;
EXECUTE stmt;</code>

For an example with a SQLfiddle, consult the provided HTML source.

The above is the detailed content of How to Concatenate All Columns in a MySQL Table?. For more information, please follow other related articles on the PHP Chinese website!