How to select column names dynamically in mySQL

问题:

I want to select column names but I don't know the table structure ahead of time and it may change so I can't just hard code the select statement with column names. I also do NOT want to select every column. Is there and easy way to do this?

My thoughts are it's some kind of combination of these two queries but my SQL is not that good.

SHOW COLUMNS FROM table_name;
SELECT * FROM table_name; 

I tried using a sub select but it didn't work. Nothing seems to happen, I don't get an error I just get no results

SELECT (SELECT column_name 
        FROM information_schema.columns 
        WHERE table_name ='table_name') 
FROM table_name;

Maybe I need to do a join?.. Anyway any help would be great, thanks

回答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.