45.2k
2013-10-01 16:07:54
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>
热门帖子
热门github
6
caveat: INFORMATION_SCHEMA queries have pretty poor performance, so be careful this type of query isn't in the critical path for anything.
Like @Jan Koritak said below, this answer doesn't actually work if the title's columns that you want to remove are also a sub-string of the title for any columns you wish to keep. There is a better answer that is similar to this that can be found here.
This is way worse than just specifying the columns which is a known best practice.
This doesnt work if there are spaces in the column names. It should be updated to always surround the names in backticks
<column_name>This also won't work if the column to be ignored is the last one listed in the table structure (because the replace will not match the trailing coma).