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:
Category:MySQL Server Severity:S4 (Feature request)
Version: OS:Any (any)
Assigned to: CPU Architecture:Any

[25 May 2004 8:16] Mr. Venom
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.
[25 May 2004 14:00] Mr. Venom
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.
[25 May 2004 15:49] Sergei Golubchik
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