SELECT * with column-exclusion

[mbrouillet]

The following has been my Mysql dream evolution for many years. Warning: I'm talking about not being SQL syntax compliant.

Many times, I wish to SELECT all columns, except a few specific ones. For instance, if I SELECT * FROM a table that contains a description field with multiline text in some records, the table output gets cluttered with \n. I may not need that field for my current request. Other example is when having a binary field : a SELECT * FROM is generally completely unusable. (I've resorted to never use binaries for this reason).

My dream would be a syntax to list all-columns-except-a-few. For instance, this could be
SELECT *!column1!column2!column3 FROM...
or
SELECT *~column1~column2~column3 FROM...

Since mycli already has the list of columns for auto-completion, it seems feasible to pre-parse the query and replace the *!column1!column2!column3 by a list of all columns except the ones specified. Is this appealing to anyone ?

[dark7god]

Oh please yes, this would be a game changer!

[rolandwalker]

Hi! While I agree that this is cool and would see daily use, it is also out-of-scope for what mycli does, as a client alone. The other extensions to SQL such as $|' and .|` describe operations which happen entirely on the clientside.

To implement this, one would need to query the serverside behind the scenes to get the list of columns, and construct a different SQL query. It would probably be fragile.

However, some dialects of SQL do allow constructs like SELECT * EXCEPT (col). If it ever comes to MySQL, then it will be possible.