MySQL - Running MySQL in ANSI Mode
If you start mysqld with the --ansi option, the
following behavior of MySQL changes:
|| is string concatenation instead of OR.
You can have any number of spaces between a function
name and the `('. This forces all function names to be
treated as reserved words.
`"' will be an identifier quote character (like the
MySQL ``' quote character) and not a string quote
character. REAL will be a synonym for FLOAT instead of a
synonym of DOUBLE.
5.3 MySQL Differences Compared to ANSI SQL92
We try to make MySQL follow the ANSI SQL standard and
the ODBC SQL standard, but in some cases MySQL does some
-- is only a comment if followed by a white space.
For VARCHAR columns, trailing spaces are removed when
the value is stored.
In some cases, CHAR columns are silently changed to
Privileges for a table are not automatically revoked
when you delete a table. You must explicitly issue a
REVOKE to revoke privileges for a table.
NULL AND FALSE will evaluate to NULL and not to FALSE.
This is because we don't think it's good to have to
evaluate a lot of extra conditions in this case.
MySQL - Functionality Missing from MySQL
The following functionality is missing in the current
version of MySQL. For a prioritized list indicating when
new extensions may be added to MySQL, you should consult
the online MySQL TODO list. That is the latest version
of the TODO list in this manual.
MySQL - Sub-selects
The following will not yet work in MySQL:
SELECT * FROM table1 WHERE id IN (SELECT id FROM
SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM
SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM
table2 where table1.id=table2.id);
However, in many cases you can rewrite the query without
SELECT table1.* FROM table1,table2 WHERE
SELECT table1.* FROM table1 LEFT JOIN table2 ON
table1.id=table2.id where table2.id IS NULL
For more complicated subqueries you can often create
temporary tables to hold the subquery. In some cases,
however this option will not work. The most frequently
encountered of these cases arises with DELETE
statements, for which standard SQL does not support
joins (except in sub-selects). For this situation there
are two options available until subqueries are supported
The first option is to use a procedural programming
language (such as Perl or PHP) to submit a SELECT query
to obtain the primary keys for the records to be
deleted, and then use these values to construct the
DELETE statement (DELETE FROM ... WHERE ... IN (key1,
The second option is to use interactive SQL to contruct
a set of DELETE statements automatically, using the
MySQL extension CONCAT() (in lieu of the standard ||
operator). For example:
SELECT CONCAT('DELETE FROM tab1 WHERE pkid = ',
FROM tab1, tab2
WHERE tab1.col1 = tab2.col2;
You can place this query in a script file and redirect
input from it to the mysql command-line interpreter,
piping its output back to a second instance of the
prompt> mysql --skip-column-names mydb > myscript.sql |
MySQL only supports INSERT ... SELECT ... and REPLACE
... SELECT ... Independent sub-selects will probably be
available in Version 4.0. You can now use the function
IN() in other contexts, however.
Page Numbers : 1