SQL variable in a statement, select all table fields in a single result *

Here is a short SQL statement that could save you a lot of time if you are trying to get an unknown set of fields from a given table in a single result.

SET @fields = (SELECT GROUP_CONCAT( `COLUMN_NAME`)
FROM `information_schema`.`COLUMNS`
WHERE `TABLE_SCHEMA` = ‘YOURDB’ AND `TABLE_NAME` = ‘YORTABLE’ GROUP BY `TABLE_NAME` )
COLLATE utf8_general_ci;
set @sql = CONCAT(‘SELECT GROUP_CONCAT(‘,@fields, ‘) FROM YORTABLE LIMIT 1’);
PREPARE test_sql FROM @sql;
execute test_sql;

Categories