Go to the first, previous, next, last section, table of contents.


How standards compatible is MySQL?

What extensions has MySQL to ANSI SQL92?

The following are useful extensions in MySQL that you probably will not find in other SQL databases. Be warned that if you use them, your code will not be portable to other SQL servers.

What functionality is missing in MySQL.

The following functionality is missing in the current version of MySQL. For the priority of new extensions you should consult: The MySQL Todo list. That is the latest version of the Todo list in this manual. See section List of things we want to add to MySQL in the future..

Sub-selects

The following will not work in MySQL:

SELECT * from table WHERE id IN (SELECT id from table2)

MySQL only supports INSERT ... SELECT... and REPLACE ... SELECT.... Independent sub-selects will be probably be available in 3.22.0. One can now use the function IN() in other context though.

SELECT INTO TABLE

MySQL doesn't yet support SELECT ... INTO TABLE.... Currently MySQL only supports SELECT ... INTO OUTFILE....

Transactions

Transactions are not supported. MySQL will shortly support atomic operations which are like transactions without rollback. With atomic operations you can make a bunch of insert/select/whatever commands and be guaranteed that no other thread will interfere. In this context you won't usually need rollback. Currently you can do this with the help of the LOCK TABLES/UNLOCK TABLES command. See section LOCK TABLES syntax

Triggers

Triggers are not supported. The planned update language will be able to handle stored procedures, but without triggers. Triggers usually slow everything down, even for queries where they aren't needed.

Foreign Keys

The FOREIGN KEY syntax in MySQL exists only for compatibility with other SQL vendors CREATE TABLE commands: it doesn't do anything. The FOREIGN KEY syntax without ON DELETE .. is mostly used for documentation purposes. Some ODBC applications may uses this to produce automatic WHERE clauses though, but this is usually easy to override. FOREIGN KEY is sometimes used as a constraint check, but this check is in practice unnecessary if one inserts rows in the tables in the right order.

In MySQL one can work around the problem that ON DELETE ... isn't implemented by adding the approative DELETE statement to the application when one deletes records from a table that has FOREIGN KEY. In practice this is as quick (in some case quicker) and much more portable than using FOREIGN KEY.

Foreign keys are something that makes life very complicated, because the foreign key definition must be stored in some database and then the whole 'nice approach' of using only files that can be moved, copied and removed will be destroyed.

In the near future we will extend FOREIGN KEYS so that at least the information will be saved and may be retrieved by mysqldump and ODBC.

Some reasons NOT to use FOREIGN KEYS

There are so many problems with the FOREIGN KEYs that we don't know where to start.

The only nice aspect of foreign key is that it gives ODBC and some other client programs the ability to see how a table is connected and use this to show connection diagrams and to help building applicatons.

MySQL will soon store the FOREIGN KEY definitions so that a client can ask and receive an answer how the original connection was made. The current .frm file format does not have any place for it.

Views

MySQL doesn't support views, but this is on the TODO.

-- as start of a comment

Some other SQL databases have -- as start comment. MySQL has # as the start comment character, even if the mysql command line tool removes all lines that starts with --. You can also use the C comment style /* this is a comment */ with MySQL. See section Comment syntax

MySQL will not support this degenerated comment style because we have had many problems with automatically generated SQL queries that use something like the following code:

UPDATE table_name SET credit=credit-!payment!

Where instead of !payment! we automaticly insert the value of the payment.

What do you think will happen when 'payment' is negative ?

Because 1--1 is legal in SQL, we think is terrible that '--' means start comment.

If you have a sql program in a textfile that contains -- comments you should use:

replace " --" " #" < text-with-funny-comments.sql | mysql database.

instead of the normal

mysql database < text-with-funny-comments.sql

You can also change the -- to # comments in the command file:

replace " --" " #" -- text-with-funny-comments.sql

Change them back with:

replace " #" " --" -- text-with-funny-comments.sql

What standards does MySQL follow?

Entry level SQL92. ODBC level 0-2.

What functions exist only for compatibility?

Limitations of BLOB and TEXT types

If you want to GROUP BY or ORDER BY on a BLOB or TEXT field, you must make the field into a fixed length object. The standard way to do this is with the SUBSTRING functions. If you don't do this only the first max_sort_length (default=1024) will considered when sorting.

SELECT comment from table order by substring(comment,20);

How to cope without COMMIT-ROLLBACK

MySQL doesn't support COMMIT-ROLLBACK. The problem with COMMIT-ROLLBACK is that for it work efficiently it would require a completely different table layout than MySQL uses today. MySQL would also need extra threads that do automatic cleanups on the tables and the disk space needed would be much higher. This would make MySQL about 2-4 times slower than it is today. One of the reasons that MySQL is so much faster than almost all other SQL databases (typical times are at least 2-3 times faster) is the lack of COMMIT-ROLLBACK.

For the moment, we are much more in favor of implementing the SQL server language (stored procedures). With this you very seldom really need COMMIT-ROLLBACK, and you can do many more things without losing any speed.

Loops that need transactions can normally be coded with the help of LOCK TABLES, and one doesn't need cursors when one can update records on the fly.

We have transactions and cursors on the TODO but not quite prioritised. If it is implemented it will be as a option to CREATE TABLE. That means that COMMIT-ROLLBACK will only work on those tables and only those tables will be slower.

We at TcX have a greater need for a very fast database than a 100% general database. Whenever we find a way to implement these features without any speed loss we will probably do it. For the moment there are many more important things to do. Check the TODO for how we prioritise things at the moment. Customers with higher levels of support can alter this, so things may be reprioritised.

The current problem is actually ROLLBACK. Without ROLLBACK you can do anything with LOCK TABLES. To support ROLLBACK MySQL would have to be changed to store all old records that were updated and revert everything back to the starting point if ROLLBACK was issued. For simple cases this isn't that hard to do (the current isamlog could be used for this), but if one wants to have ROLLBACK with ALTER/DROP/CREATE TABLE it would make everything much harder to implement.

To avoid using ROLLBACK one can do:

  1. LOCK TABLES ...
  2. Test conditions.
  3. Update if everything is ok.
  4. UNLOCK TABLES

This is usually much faster, but not always. The only thing this doesn't handle if someone does a kill on the process.

One can also use functions to update things in one operation. By doing all updates relatively and/or only updating those fields that actually have changed one can get a very efficient application.

For example, when we are doing updates on some customer information, we only update the customer data that has changed and only test that none of the changed data, or data that depends on the changed data, has changed in the original row. The test for change is down with the WHERE clause in the UPDATE statement. If the record wasn't updated we give the client a message: "Some of the data you have changed has been changed by another user", and then we show the old row versus the new row in a window. The user can then decide which version of the customer record he should use.

This gives us something like 'column locking' but actually even better, because we only update some of the columns with relative information. This means that a typical update statement looks something like:

UPDATE tablename SET pay_back=pay_back+'relative change'

UPDATE customer set customer_date='current_date', address='new address',
phone='new phone', money_he_owes_us=money_he_owes+'new_money' where
customer_id=id and address='old address' and phone='old phone';

As you can see, this is very efficient and even if another client has changed the 'money_he_owes_us' or 'pay_back' amount this will still work.

In many cases, users have wanted ROLLBACK and/or LOCK TABLES to manage unique identifiers for some tables. This can be handled much more efficiently by using an AUTO_INCREMENT column and the MySQL API function mysql_insert_id. See section How can I get the unique ID for the last inserted row?

At TcX we have never had any need for row level locking as we have always been able to code around it. I know some cases that really need row locking, but they are very few. If you want to have row level locking you can do something like:

UPDATE table_name SET row_flag=1 WHERE id=ID;

MySQL returns affected rows = 1 if the row was found and row_flag wasn't 1 in the original document. On the TODO there is GET_LOCK and RELEASE_LOCK for those that want to implement application level locking.


Go to the first, previous, next, last section, table of contents.


Casa de Bender