How to select column names dynamically in mySQL

Solution 1:

Try this SQLFiddle:

CREATE TABLE atable (
  prefix1 VARCHAR(10)
  ,prefix2 VARCHAR(10)
  ,notprefix3 INT
  ,notprefix4 INT
);

INSERT INTO atable VALUES ('qwer qwer', 'qwerqwer', 1, 1);
INSERT INTO atable VALUES ('qwer qwer', 'asdfaasd', 1, 1);
INSERT INTO atable VALUES ('qwer qwer', 'qrt vbb', 1, 1);
INSERT INTO atable VALUES ('qwer qwer', 'sdfg sdg', 1, 1);

SELECT CONCAT('SELECT ', GROUP_CONCAT(c.COLUMN_NAME), ' FROM atable;')
INTO @query
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = 'atable'
  AND c.COLUMN_NAME LIKE 'prefix%'
ORDER BY c.ORDINAL_POSITION;

PREPARE stmt FROM @query;

EXECUTE stmt;

Some issues:

You will likely want some kind of ORDER BY on your result set.

There's a limit to what you can do in terms of joins and things.

You move validation to runtime where it's more likely to be missed by testing.

You are hoping to be able to handle schema changes easily. This technique will only handle schema changes of a certain type you can foresee, and others you will probably have to change this code anyway.