There are numerous other fixes, enhancements, and additions planned for future versions of MySQL. In this section, we’ll take a brief look at some of these, particularly those that are of interest with regard to optimizing database schemas and queries or speeding up general performance.
Full (Outer) Joins
Full joins may be supported in MySQL 5.0 or 5.1. Full joins, which return a NULL for any column in one table that isn’t matched in the other, are discussed in Chapter 5.
User variables in MySQL 5.0 have already been changed in that the names are no longer case sensitive. Prior to this, @MYVAR and @myvar were treated as separate variables; beginning with version 5.0, they’ll be regarded as the same variable.
Another planned change is to allow user variables to be updated in UPDATE statements. For example, if this is done, the following would be possible:
UPDATE mytable SET @myvar := col1 + col2;
Currently, this can be done only in a SELECT query. It’s also likely that user variables will eventually be usable in statements having GROUP BY clauses, like so:
SELECT id, @count := COUNT(*) FROM products GROUP BY category_id;
Two new functions for working with SET columns are planned. These are ADD_TO_SET() and REMOVE_FROM_SET() . Suppose that we have a users table containing a SET column defined as
Were this function to be added, it would then be possible to update the column definition with something like this:
ALTER TABLE users
ALTER TABLE users
without the need to reiterate all the elements in the set.
The SQL standard provides for three functions that allow you to find out very quickly whether or not any one, some, or all of a set of values is true:
Here are some examples, noting that the query SELECT price > 100 FROM products; will return a set of 1s and 0s, that is, TRUE and FALSE values:
# If this query returns 1 (TRUE) then we know only one product has a pric e
According to the SQL standard, these functions should work only with sets of Boolean values; however, this could work in MySQL with other types of sets as well, since MySQL interprets 0 as FALSE and any other number or string value as TRUE.
blog comments powered by Disqus