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. F List of things we want to add to MySQL in the future (The TODO).
The following will not yet work in MySQL:
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2); SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);
However, in many cases you can rewrite the query without a sub select:
SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id; SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id where table2.id IS NULL
For more complicated sub queries you can create temporary tables to hold the sub query.
MySQL only supports
MySQL doesn't yet support the Oracle SQL extension:
Alternatively, you can use
Transactions are not supported. MySQL shortly will support atomic
operations, which are like transactions without rollback. With atomic
operations, you can execute a group of
A stored procedure is a set of SQL commands that can be compiled and stored in the server. Once this has been done, clients don't need to keep reissuing the entire query but can refer to the stored procedure. This provides better performance because the query has to be parsed only once and less information needs to be sent between the server and the client. You can also raise the conceptual level by having libraries of functions in the server.
A trigger is a stored procedure that is invoked when a particular event occurs. For example, you can install a stored procedure that is triggered each time a record is deleted from a transaction table and that automatically deletes the corresponding customer from a customer table when all his transactions are deleted.
The planned update language will be able to handle stored procedures, but without triggers. Triggers usually slow down everything, even queries for which they are not needed.
To see when MySQL might get stored procedures, see F List of things we want to add to MySQL in the future (The TODO).
Note that foreign keys in SQL are not used to join tables, but are used
mostly for checking referential integrity. If you want to get results from
multiple tables from a
SELECT * from table1,table2 where table1.id = table2.id;
In MySQL, you can work around the problem of
In the near future we will extend the
There are so many problems with
The only nice aspect of
MySQL will soon store
MySQL doesn't support views, but this is on the TODO.
Some other SQL databases use `--' to start comments. MySQL
has `#' as the start comment character, even if the
MySQL 3.23.3 and above supports the `--' comment style
only if the comment is followed by a space. This is because this
degenerate comment style has caused many problems with automatically
generated SQL queries that have used something like the following code,
where we automatically insert the value of the payment for
UPDATE tbl_name SET credit=credit-!payment!
What do you think will happen when the value of
In MySQL 3.23 you can however use:
The following discussing only concerns you if you are running an MySQL version earlier than 3.23:
If you have a SQL program in a text file that contains `--' comments you should use:
shell> replace " --" " #" < text-file-with-funny-comments.sql \ | mysql database
instead of the usual:
shell> mysql database < text-file-with-funny-comments.sql
You can also edit the command file ``in place'' to change the `--' comments to `#' comments:
shell> replace " --" " #" -- text-file-with-funny-comments.sql
Change them back with this command:
shell> replace " #" " --" -- text-file-with-funny-comments.sql