Select all columns except one in MySQL?

mysql select wildcard
Tom Grochowicz picture Tom Grochowicz · Aug 12, 2008 · Viewed 359.5k times · Source

I'm trying to use a select statement to get all of the columns from a certain MySQL table except one. Is there a simple way to do this?

EDIT: There are 53 columns in this table (NOT MY DESIGN)

Answer

Mahomedalid picture Mahomedalid · Sep 30, 2009

Actually there is a way, you need to have permissions of course for doing this ...

SET @sql = CONCAT('SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), '<columns_to_omit>,', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<table>' AND TABLE_SCHEMA = '<database>'), ' FROM <table>');

PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

Replacing <table>, <database> and <columns_to_omit>

Related questions

Selecting all columns that start with XXX using a wildcard?

I have several columns in my databases with similar names. How do I select those based on the word they start with? Here's an example table layout: I tried selecting all info for a particular thing (food kind in this …

Read More
How to ignore characters on a MYSQL SELECT LIKE %...%

I have a table with a phone column, where data may have spaces, dots,dashes or + signs between the numbers. I need to do a search with LIKE wildcards that ignore all those characters, for example: a record may have …

Read More
MySQL - UPDATE query based on SELECT Query

I need to check (from the same table) if there is an association between two events based on date-time. One set of data will contain the ending date-time of certain events and the other set of data will contain the …

Read More