Geek asks and answers

How to dynamically select column names in mySQL

I want to select column names, but I do not know the table structure in advance, and this can change, so I can’t just hardcode the select statement with the column names. I also DO NOT want to select each column. Is there an easy way to do this?

My thoughts are some combination of these two queries, but my SQL is not so good.

SHOW COLUMNS FROM table_name;
SELECT * FROM table_name;

I tried using extra select, but that didn't work. Nothing seems to be happening, I am not getting an error. I just do not get results.

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

Maybe I need to connect? .. Anyway, any help would be great, thanks

+5
source share
1 answer

Try 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 problems:

, - ORDER BY .

, .

, .

. , , , , , .

+2

More articles:


All Articles