MySQL Bugs: #3880: Enhance SELECT * syntax to leave out some columns

Bug #3880	Enhance SELECT * syntax to leave out some columns
Submitted:	25 May 2004 8:16	Modified:	25 May 2004 15:49
Reporter:	Mr. Venom	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:		OS:	Any (any)
Assigned to:		CPU Architecture:	Any

Description:
I think it would be handy to have a "leave out some column(s)" syntax.

How to repeat:
For example: when fetching results from one table _with a whole lot of columns_ into an other, I'd have to list all the needed columns like this:

CREATE TABLE transfer SELECT `column`,`column`...,`column` FROM import

Suggested fix:
Let's say I want to export all but one column (e.g. leave out the autoincrement uID and the timestamp when the record was added), it would be handy to have something like:

CREATE TABLE transfer SELECT * EXCEPT `uID`,`stamp` FROM import

OK, I _could_ drop the column afterwards on a CREATE but such a feature would do the trick of lowering traffic when doing SELECTs and e.g. php associative fetches. I'm too lazy to write names of only those 12 from 15 columns that are needed and use the asterisk instead, but still, the latter 3 columns might be BLOBS with some summarizing megs of non-used data travelling the net.

And one more: why does SELECT *,NULL work, but SELECT NULL,* does not?
That is, I wanted to port data from a table to an other. The latter one already has a primary key as the first column. So, doing "INSERT INTO latter SELECT NULL,* FROM first" seems quite logical. Ok, I guess, selecting NULL as first column (btw: SELECT NULL as `something`,* doesn't work either) is quite bogus, but keeping the primary key as the last column is neither a fine solution.

first: SELECT * EXCEPT `uID`,`stamp` FROM import is non-standard so, though it could be possibly implemented, it's very low in the our todo list.

second: neither SELECT *,NULL nor SELECT NULL,* is standard. It just happen that MySQL parser is forgiving in this regard and allows SELECT *,NULL. Having SELECT *,NULL and not having SELECT NULL,* is inconsistent, I agree. It will be fixed eventually, but it's also low-priority issue