select all columns except one

Question

I need to select all the columns from a table "ticket" except one column "depname" The following prepared statement does not work as expected.

PREPARE stmt1 FROM 'SELECT ? from magon.ticket limit 2';

select GROUP_CONCAT(COLUMN_NAME) into @clm from COLUMNS  where TABLE_SCHEMA = 'magon' and TABLE_NAME = 'ticket' and COLUMN_NAME NOT IN ('depname')  GROUP BY TABLE_SCHEMA, TABLE_NAME;

execute stmt1 using @clm;

It returns the column list twice (due to limit 2) but not the actual data.

score 5 · Accepted Answer · answered Apr 02 '13 at 11:34

Unfortunately you can't parameterized column name as well as table name because they are identifiers. But you can concatenate the values in the string,

SET @colName = (SELECT  GROUP_CONCAT(COLUMN_NAME) 
                FROM    COLUMNS  
                WHERE   TABLE_SCHEMA = 'magon' AND 
                        TABLE_NAME = 'ticket' AND 
                        COLUMN_NAME NOT IN ('depname')  
                GROUP   BY TABLE_SCHEMA, TABLE_NAME);
PREPARE stmt1 FROM CONCAT('SELECT ', @colName ,' FROM magon.ticket LIMIT 2');
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

MySQL PREPARE Syntax

select all columns except one

1 Answers1