5 How standards-compatible is MySQL?

5.1 MySQL extensions to ANSI SQL92

MySQL includes some extensions 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. In some cases, you can write code that includes MySQL extensions, but is still portable, by using comments of the form /*! ... */. In this case, MySQL will parse and execute the code within the comment as it would any other MySQL statement, but other SQL servers will ignore the extensions. For example:

SELECT /*! STRAIGHT_JOIN */ col_name FROM table1,table2 WHERE ...

If you add a version number after the '!', the syntax will only be executed if the MySQL version is equal or newer than the used version number:

CREATE /*!32302 TEMPORARY */ TABLE (a int);

The above means that if you have 3.23.02 or newer, then MySQL will use the TEMPORARY keyword.

MySQL extensions are listed below:

• The field types MEDIUMINT, SET, ENUM and the different BLOB and TEXT types.
• The field attributes AUTO_INCREMENT, BINARY, UNSIGNED and ZEROFILL.
• All string comparisons are case insensitive by default, with sort ordering determined by the current character set (ISO-8859-1 Latin1 by default). If you don't like this, you should declare your columns with the BINARY attribute or use the BINARY cast, which causes comparisons to be done according to the ASCII order used on the MySQL server host.
• MySQL maps each database to a directory under the MySQL data directory, and tables within a database to filenames in the database directory. This has two implications:
  • Database names and table names are case sensitive in MySQL on operating systems that have case sensitive filenames (like most Unix systems). If you have a problem remembering table names, adopt a consistent convention, such as always creating databases and tables using lowercase names.
  • Database, table, index, column or alias names may begin with a digit (but may not consist solely of digits).
  • You can use standard system commands to backup, rename, move, delete and copy tables. For example, to rename a table, rename the `.ISD', `.ISM' and `.frm' files to which the table corresponds.
• In SQL statements, you can access tables from different databases with the db_name.tbl_name syntax. Some SQL servers provide the same functionality but call this User space. MySQL dosen't support tablespaces like in: create table ralph.my_table...IN my_tablespace.
• LIKE is allowed on numeric columns.
• Use of INTO OUTFILE and STRAIGHT_JOIN in a SELECT statement. See section 7.11 SELECT syntax.
• The SQL_SMALL_RESULT option in a SELECT statement.
• EXPLAIN SELECT to get a description on how tables are joined.
• Use of index names, indexes on a prefix of a field, and use of INDEX or KEY in a CREATE TABLE statement. See section 7.6 CREATE TABLE syntax.
• Use of TEMPORARY or IF NOT EXISTS with CREATE TABLE.
• Use of COUNT(DISTINCT list) where 'list' is more than one element.
• Use of CHANGE col_name, DROP col_name or DROP INDEX in an ALTER TABLE statement. See section 7.7 ALTER TABLE syntax.
• Use of IGNORE in an ALTER TABLE statement.
• Use of multiple ADD, ALTER, DROP or CHANGE clauses in an ALTER TABLE statement.
• Use of DROP TABLE with the keywords IF EXISTS.
• You can drop multiple tables with a single DROP TABLE statement.
• The LIMIT clause of the DELETE statement.
• The DELAYED clause of the INSERT and REPLACE statements.
• The LOW_PRIORITY clause of the INSERT, REPLACE, DELETE and UPDATE statements.
• Use of LOAD DATA INFILE. In many cases, this syntax is compatible with Oracle's LOAD DATA INFILE. See section 7.15 LOAD DATA INFILE syntax.
• The OPTIMIZE TABLE statement. See section 7.8 OPTIMIZE TABLE syntax.
• The SHOW statement. See section 7.20 SHOW syntax (Get information about tables, columns,...).
• Strings may be enclosed by either `"' or `'', not just by `''.
• Use of the escape `\' character.
• The SET OPTION statement. See section 7.24 SET OPTION syntax.
• You don't need to name all selected columns in the GROUP BY part. This gives better performance for some very specific, but quite normal queries. See section 7.3.13 Functions for use with GROUP BY clauses.
• To make it easier for users that come from other SQL environments, MySQL supports aliases for many functions. For example, all string functions support both ANSI SQL syntax and ODBC syntax.
• MySQL understands the || and && operators to mean logical OR and AND, as in the C programming language. In MySQL, || and OR are synonyms, as are && and AND. Because of this nice syntax, MySQL doesn't support the ANSI SQL || operator for string concatenation; use CONCAT() instead. Since CONCAT() takes any number of arguments, it's easy to convert use of the || operator to MySQL.
• CREATE DATABASE or DROP DATABASE. See section 7.4 CREATE DATABASE syntax.
• The % operator is a synonym for MOD(). That is, N % M is equivalent to MOD(N,M). % is supported for C programmers and for compatibility with PostgreSQL.
• The =, <>, <= ,<, >=,>, <<, >>, <=>, AND, OR or LIKE operators may be used in column comparisons to the left of the FROM in SELECT statements. For example:

  mysql> SELECT col1=1 AND col2=2 FROM tbl_name;
  

• The LAST_INSERT_ID() function. See section 20.4.29 mysql_insert_id().
• The REGEXP and NOT REGEXP extended regular expression operators.
• CONCAT() or CHAR() with one argument or more than two arguments. (In MySQL, these functions can take any number of arguments.)
• The BIT_COUNT(), CASE, ELT(), FROM_DAYS(), FORMAT(), IF(), PASSWORD(), ENCRYPT(), md5(), ENCODE(), DECODE(), PERIOD_ADD(), PERIOD_DIFF(), TO_DAYS(), or WEEKDAY() functions.
• Use of TRIM() to trim substrings. ANSI SQL only supports removal of single characters.
• The GROUP BY functions STD(), BIT_OR() and BIT_AND().
• Use of REPLACE instead of DELETE + INSERT. See section 7.14 REPLACE syntax.
• The FLUSH flush_option statement.

5.2 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 things differently:

• -- is only a comment if followed by a white space. See section 5.3.7 `--' as the start of a comment.
• For VARCHAR columns, trailing spaces are removed when the value is stored. See section E Known errors and design deficiencies in MySQL.
• In some cases, CHAR columns are silently changed to VARCHAR columns. See section 7.6.1 Silent column specification changes.
• Privileges for a table is not automatically revoked when you delete a table. You must explicitly issue a REVOKE to revoke privileges for a table. See section 7.25 GRANT and REVOKE syntax.

5.3 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. See section F List of things we want to add to MySQL in the future (The TODO).

5.3.1 Sub-selects

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 INSERT ... SELECT ... and REPLACE ... SELECT ... Independent sub-selects will be probably be available in 3.24.0. You can now use the function IN() in other contexts, however.

5.3.2 SELECT INTO TABLE

MySQL doesn't yet support the Oracle SQL extension: SELECT ... INTO TABLE .... MySQL supports instead the ANSI SQL syntax INSERT INTO ... SELECT ..., which is basically the same thing.

Alternatively, you can use SELECT INTO OUTFILE... or CREATE TABLE ... SELECT to solve your problem.

5.3.3 Transactions

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 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 prevent interference from other threads by using the LOCK TABLES and UNLOCK TABLES commands. See section 7.23 LOCK TABLES/UNLOCK TABLES syntax.

5.3.4 Stored procedures and triggers

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 section F List of things we want to add to MySQL in the future (The TODO).

5.3.5 Foreign Keys

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 statement, you do this by joining tables!

SELECT * from table1,table2 where table1.id = table2.id;

See section 7.12 JOIN syntax. See section 8.3.5 Using 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 use this to produce automatic WHERE clauses, but this is usually easy to override. FOREIGN KEY is sometimes used as a constraint check, but this check is unnecessary in practice if rows are inserted into the tables in the right order. MySQL only supports these clauses because some applications require them to exist (regardless of whether or not they work!).

In MySQL, you can work around the problem of ON DELETE ... not being implemented by adding the appropriate DELETE statement to an application when you delete records from a table that has a foreign key. In practice this is as quick (in some cases quicker) and much more portable than using foreign keys.

In the near future we will extend the FOREIGN KEY implementation so that at least the information will be saved in the table specification file and may be retrieved by mysqldump and ODBC.

5.3.5.1 Reasons NOT to use foreign keys

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

• Foreign keys make life very complicated, because the foreign key definitions must be stored in a database and implementing them would destroy the whole ``nice approach'' of using files that can be moved, copied and removed.
• The speed impact is terrible for INSERT and UPDATE statements, and in this case almost all FOREIGN KEY checks are useless because you usually insert records in the right tables in the right order, anyway.
• There is also a need to hold locks on many more tables when updating one table, because the side effects can cascade through the entire database. It's MUCH faster to delete records from one table first and subsequently delete them from the other tables.
• You can no longer restore a table by doing a full delete from the table and then restoring all records (from a new source or from a backup).
• If you have foreign keys you can't dump and restore tables unless you do so in a very specific order.
• It's very easy to do ``allowed'' circular definitions that make the tables impossible to recreate each table with a single create statement, even if the definition works and is usable.

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 to use this to show connection diagrams and to help in building applicatons.

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

5.3.6 Views

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

5.3.7 `--' as the start of a comment

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

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 !payment!:

UPDATE tbl_name SET credit=credit-!payment!

What do you think will happen when the value of payment is negative?

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

In MySQL 3.23 you can however use: 1-- This is a comment

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

5.4 What standards does MySQL follow?

Entry level SQL92. ODBC level 0-2.

5.5 How to cope without COMMIT/ROLLBACK

MySQL doesn't support COMMIT-ROLLBACK. The problem is that handling COMMIT-ROLLBACK efficiently 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 usage would be much higher. This would make MySQL about 2-4 times slower than it is today. MySQL is much faster than almost all other SQL databases (typically at least 2-3 times faster). One of the reasons for this is the lack of COMMIT-ROLLBACK.

For the moment, we are much more for implementing the SQL server language (something like stored procedures). With this you would very seldom really need COMMIT-ROLLBACK. This would also give much better performance.

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

We have transactions and cursors on the TODO but not quite prioritized. If we implement these, it will be as an option to CREATE TABLE. That means that COMMIT-ROLLBACK will work only on those tables, so that a speed penalty will be imposed on those table only.

We at TcX have a greater need for a real 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 prioritize things at the moment. (Customers with higher levels of support can alter this, so things may be reprioritized.)

The current problem is actually ROLLBACK. Without ROLLBACK, you can do any kind of COMMIT action 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 purpose), but it would be much more difficult to implement ROLLBACK for ALTER/DROP/CREATE TABLE.

To avoid using ROLLBACK, you can use the following strategy:

1. Use LOCK TABLES ... to lock all the tables you want to access.
2. Test conditions.
3. Update if everything is okay.
4. Use UNLOCK TABLES to release your locks.

This is usually a much faster method than using transactions with possible ROLLBACKs, although not always. The only situation this solution doesn't handle is when someone kills the threads in the middle of an update. In this case, all locks will be released but some of the updates may not have been executed.

You can also use functions to update records in a single operation. You can get a very efficient application by using the following techniques:

• Modify fields relative to their current value
• Update only those fields that actually have changed

For example, when we are doing updates to some customer information, we update only the customer data that have changed and test only that none of the changed data, or data that depend on the changed data, have changed compared to the original row. The test for changed data is done 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 have been changed by another user". Then we show the old row versus the new row in a window, so the user can decide which version of the customer record he should use.

This gives us something that is similar to ``column locking'' but is actually even better, because we only update some of the columns, using values that are relative to their current values. This means that typical UPDATE statements look something like these:

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_us+'new_money'
  WHERE
    customer_id=id AND address='old address' AND phone='old phone';

As you can see, this is very efficient and works even if another client has changed the values in the pay_back or money_he_owes_us columns.

In many cases, users have wanted ROLLBACK and/or LOCK TABLES for the purpose of managing unique identifiers for some tables. This can be handled much more efficiently by using an AUTO_INCREMENT column and either the SQL function LAST_INSERT_ID() or the C API function mysql_insert_id(). See section 20.4.29 mysql_insert_id().

At TcX, we have never had any need for row-level locking because we have always been able to code around it. Some cases really need row locking, but they are very few. If you want row-level locking, you can use a flag column in the table and do something like this:

UPDATE tbl_name SET row_flag=1 WHERE id=ID;

MySQL returns 1 for the number of affected rows if the row was found and row_flag wasn't already 1 in the original row.

You can think of it as MySQL changed the above query to:

UPDATE tbl_name SET row_flag=1 WHERE id=ID and row_flag <> 1;

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