Description:
As an extension to the SQL standard which I think could make MySQL faster for developers and also possible faster for processing, i would like to suggest a field negation syntax that goes with the wildcard * character when selecting columns.
This is going to become more useful as the size of the tables increases.
Well documented is the fact that Lazy programmers select all the fields in table, rather than just the required fields. Sometimes, for many valid reasons you may want all the fields except one or two to copy a record, or you may have a large table with changing columns while developing that means you want to design more general functions.
Therefore I suggest we introduce the field negation systax, where by when using a select statement we can use the wildcard character and also exclude fields we do not want.
For example:
select *,!F1,!f2 From test
We select all the fields from test except f1 or f2.
This would be especially useful with insert...select where often you may want to duplicate a record, except the primary key or other UNIQUE columns.
If you have a long table, you need to list all the fields and replace them with literal values in the select. Again if the table structure changes, this query will need rewriting.
using the above, we could select all the fields except the primary key and insert the record directly:
Table Test
f1 Primary Key Auto increment
f2 int
f3 char
e.t.c
This would therefore become valid.... how much easier that is, no need for a temporary table and no doubt it would be MUCH faster!
Insert into test select *,!f1 from test
I think it should also be faster to parse the query. Parsing all the field names must take plenty of computing juice, and then the server has to check each one exists.
This way you would only need to check the excluded ones exist (or maybe you don’t even need to check this, if they are not needed and they don’t exist its no problem. Ok maybe that’s a bit far.)
In summary I think this idea looks like it has some potential in sever areas:
1. Improving functionality by allowing queries that were not possible before.
2. Allowing MySQL to have a more general syntax when required, so more tasks are able to be accomplished with less work.
3. Some actions to be performed faster without the need to create temporary tables.
4. Faster query writing for large tables where most columns are needed.
5. Possible faster parsing for larger tables.
Charlie
How to repeat:
Not a bug
