This chapter describes how MySQL relates to the ANSI SQL standards. MySQL has many extensions to the ANSI SQL standards, and here you will find out what they are, and how to use them. You will also find information about functionality missing from MySQL, and how to work around some differences.
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 to or newer than the used
CREATE /*!32302 TEMPORARY */ TABLE (a int);
The above means that if you have Version 3.23.02 or newer, then MySQL
will use the
MySQL extensions are listed below:
ENUM, and the different
BINARYattribute or use the
BINARYcast, which causes comparisons to be done according to the ASCII order used on the MySQL server host.
db_name.tbl_namesyntax. Some SQL servers provide the same functionality but call this
User space. MySQL doesn't support tablespaces as in:
create table ralph.my_table...IN my_tablespace.
LIKEis allowed on numeric columns.
SELECTstatement. See section 7.19
SQL_SMALL_RESULToption in a
EXPLAIN SELECTto get a description on how tables are joined.
CREATE TABLEstatement. See section 7.7
IF NOT EXISTSwith
COUNT(DISTINCT list)where 'list' is more than one element.
DROP col_name, or
ALTER TABLEstatement. See section 7.8
RENAME TABLE. See section 7.9
CHANGEclauses in an
DROP TABLEwith the keywords
LIMITclause of the
DELAYEDclause of the
LOW_PRIORITYclause of the
LOAD DATA INFILE. In many cases, this syntax is compatible with Oracle's
LOAD DATA INFILE. See section 7.23
LOAD DATA INFILESyntax.
OPTIMIZE TABLE, and
SHOWstatement. See section 7.28
SET OPTIONstatement. See section 7.33
GROUP BYpart. This gives better performance for some very specific, but quite normal queries. See section 7.4.13 Functions for Use with
&&operators to mean logical OR and AND, as in the C programming language. In MySQL,
ORare synonyms, as are
AND. Because of this nice syntax, MySQL doesn't support the ANSI SQL
||operator for string concatenation; use
CONCAT()takes any number of arguments, it's easy to convert use of the
||operator to MySQL.
DROP DATABASE. See section 7.5
%operator is a synonym for
MOD(). That is,
N % Mis equivalent to
%is supported for C programmers and for compatibility with PostgreSQL.
LIKEoperators may be used in column comparisons to the left of the
SELECTstatements. For example:
mysql> SELECT col1=1 AND col2=2 FROM tbl_name;
LAST_INSERT_ID()function. See section 126.96.36.199
NOT REGEXPextended regular expression operators.
CHAR()with one argument or more than two arguments. (In MySQL, these functions can take any number of arguments.)
TRIM()to trim substrings. ANSI SQL only supports removal of single characters.
INSERT. See section 7.22
SELECT @a:=SUM(total),@b=COUNT(*),@a/@b AS avg FROM test_table; SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
If you start
mysqld with the
--ansi option, the following behavior
of MySQL changes:
||is string concatenation instead of
REALwill be a synonym for
FLOATinstead of a synonym of
SERIALIZABLE. See section 7.34
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.4.7 `--' as the Start of a Comment.
VARCHARcolumns, trailing spaces are removed when the value is stored. See section G Known errors and design deficiencies in MySQL.
CHARcolumns are silently changed to
VARCHARcolumns. See section 7.7.1 Silent Column Specification Changes.
REVOKEto revoke privileges for a table. See section 7.35
NULL AND FALSEwill evaluate to
NULLand 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.
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 H MySQL and 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); 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 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 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 by MySQL.
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
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
CONCAT() (in lieu of the standard
SELECT CONCAT('DELETE FROM tab1 WHERE pkid = ', tab1.pkid, ';') FROM tab1, tab2 WHERE tab1.col1 = tab2.col2;
You can place this query in a script file and redirect input from it to
mysql command-line interpreter, piping its output back to a
second instance of the interpreter:
prompt> mysql --skip-column-names mydb < myscript.sql | mysql mydb
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
other contexts, however.
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. See section 7.21.1
INSERT ... SELECT Syntax.
INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE tblTemp1.fldOrder_ID > 100;
Alternatively, you can use
SELECT INTO OUTFILE... or
TABLE ... SELECT to solve your problem.
As MySQL does nowadays support transactions, the following
discussion is only valid if you are only using the non-transaction-safe
table types. See section 7.31
The question is often asked, by the curious and the critical, ``Why is MySQL not a transactional database?'' or ``Why does MySQL not support transactions?''
MySQL has made a conscious decision to support another paradigm for data integrity, ``atomic operations.'' It is our thinking and experience that atomic operations offer equal or even better integrity with much better performance. We, nonetheless, appreciate and understand the transactional database paradigm and plan, within the next few releases, to introduce transaction-safe tables on a per table basis. We will be giving our users the possibility to decide if they need the speed of atomic operations or if they need to use transactional features in their applications.
How does one use the features of MySQL to maintain rigorous integrity and how do these features compare with the transactional paradigm?
First, in the transactional paradigm, if your applications are written in a way that is dependent on the calling of ``rollback'' instead of ``commit'' in critical situations, then transactions are more convenient. Moreover, transactions ensure that unfinished updates or corrupting activities are not committed to the database; the server is given the opportunity to do an automatic rollback and your database is saved.
MySQL, in almost all cases, allows you to solve for potential problems by including simple checks before updates and by running simple scripts that check the databases for inconsistencies and automatically repair or warn if such occurs. Note that just by using the MySQL log or even adding one extra log, one can normally fix tables perfectly with no data integrity loss.
Moreover, fatal transactional updates can be rewritten to be
atomic. In fact,we will go so far as to say that all integrity problems
that transactions solve can be done with
LOCK TABLES or atomic updates,
ensuring that you never will get an automatic abort from the database,
which is a common problem with transactional databases.
Not even transactions can prevent all loss if the server goes down. In such cases even a transactional system can lose data. The difference between different systems lies in just how small the time-lap is where they could lose data. No system is 100% secure, only ``secure enough.'' Even Oracle, reputed to be the safest of transactional databases, is reported to sometimes lose data in such situations.
To be safe with MySQL, you only need to have backups and have the update logging turned on. With this you can recover from any situation that you could with any transactional database. It is, of course, always good to have backups, independent of which database you use.
The transactional paradigm has its benefits and its drawbacks. Many
users and application developers depend on the ease with which they can
code around problems where an abort appears to be, or is necessary, and they
may have to do a little more work with MySQL to either think
differently or write more. If you are new to the atomic operations
paradigm, or more familiar or more comfortable with transactions, do not
jump to the conclusion that MySQL has not addressed these
issues. Reliability and integrity are foremost in our minds. Recent
estimates indicate that there are more than 1,000,000
currently running, many of which are in production environments. We
hear very, very seldom from our users that they have lost any data, and
in almost all of those cases user error is involved. This is, in our
opinion, the best proof of MySQL's stability and reliability.
Lastly, in situations where integrity is of highest importance,
MySQL's current features allow for transaction-level or better
reliability and integrity. If you lock tables with
LOCK TABLES, all
updates will stall until any integrity checks are made. If you only obtain
a read lock (as opposed to a write lock), then reads and inserts are
still allowed to happen. The new inserted records will not be seen by
any of the clients that have a
READ lock until they release their read
INSERT DELAYED you can queue inserts into a local queue,
until the locks are released, without having the client wait for the insert
to complete. See section 7.21.2
INSERT DELAYED syntax.
``Atomic,'' in the sense that we mean it, is nothing magical. It only means
that you can be sure that while each specific update is running, no other
user can interfere with it, and there will never be an automatic
rollback (which can happen on transaction based systems if you are not
very careful). MySQL also guarantees that there will not be
any dirty reads. You can find some example of how to write atomic updates
in the commit-rollback section. See section 5.6 How to Cope Without
We have thought quite a bit about integrity and performance, and we believe that our atomic operations paradigm allows for both high reliability and extremely high performance, on the order of three to five times the speed of the fastest and most optimally tuned of transactional databases. We didn't leave out transactions because they are hard to do. The main reason we went with atomic operations as opposed to transactions is that by doing this we could apply many speed optimizations that would not otherwise have been possible.
Many of our users who have speed foremost in their minds are not at all
concerned about transactions. For them transactions are not an
issue. For those of our users who are concerned with or have wondered
about transactions vis-a-vis MySQL, there is a ``MySQL
way'' as we have outlined above. For those where safety is more
important than speed, we recommend them to use the
InnoDB tables for all their critical
data. See section 8 MySQL Table Types.
One final note: We are currently working on a safe replication schema that we believe to be better than any commercial replication system we know of. This system will work most reliably under the atomic operations, non-transactional, paradigm. Stay tuned.
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 H MySQL and the future (The TODO).
Note that foreign keys in SQL are not used to join tables, but are used
mostly for checking referential integrity (foreign key constraints). If
you want to get results from multiple tables from a
statement, you do this by joining tables:
SELECT * from table1,table2 where table1.id = table2.id;
See section 7.20
JOIN Syntax. See section 9.5.6 Using Foreign Keys.
FOREIGN KEY syntax in MySQL exists only for compatibility
with other SQL vendors'
CREATE TABLE commands; it doesn't do
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
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
In MySQL, you can work around the problem of
... 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. At a later stage we will
implement the foreign key constraints for application that can't easily be
coded to avoid them.
There are so many problems with foreign key constraints that we don't know where to start:
UPDATEstatements, and in this case almost all
FOREIGN KEYconstraint checks are useless because you usually insert records in the right tables in the right order, anyway.
FOREIGN KEY ... ON DELETErules when one codes an application. It's not unusual that one loses a lot of important information just because a wrong or misused
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 applications.
MySQL will soon store
FOREIGN KEY definitions so that a
client can ask for and receive an answer about how the original
connection was made. The current `.frm' file format does not have
any place for it. At a later stage we will implement the foreign key
constraints for application that can't easily be coded to avoid them.
MySQL doesn't yet support views, but we plan to implement these to about 4.1.
Views are mostly useful for letting users access a set of relations as one table (in read-only mode). Many SQL databases don't allow one to update any rows in a view, but you have to do the updates in the separate tables.
As MySQL is mostly used in applications and on web system where the application writer has full control on the database usage, most of our users haven't regarded views to be very important. (At least no one has been interested enough in this to be prepared to finance the implementation of views).
One doesn't need views in MySQL to restrict access to columns as MySQL has a very sophisticated privilege system. See section 6 The MySQL Access Privilege System.
Some other SQL databases use `--' to start comments. MySQL
has `#' as the start comment character, even if the
command-line tool removes all lines that start with `--'.
You can also use the C comment style
/* this is a comment */ with
See section 7.38 Comment Syntax.
MySQL Version 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
payment is negative?
1--1 is legal in SQL, we think it is terrible that
`--' means start comment.
In MySQL Version 3.23 you can, however, use:
1-- This is a comment
The following discussion only concerns you if you are running a MySQL version earlier than Version 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
Entry level SQL92. ODBC levels 0-2.
The following mostly applies only for
HEAP tables. If you only use transaction-safe tables (
InnoDB tables) in an an update, you can do
ROLLBACK also with MySQL.
See section 7.31
The problem with handling
ROLLBACK efficiently with
the above table types would require a completely different table layout
than MySQL uses today. The table type would also need extra
threads that do automatic cleanups on the tables, and the disk usage
would be much higher. This would make these table types about 2-4 times
slower than they are today.
For the moment, we prefer implementing the SQL server language (something
like stored procedures). With this you would very seldom really need
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 at TcX had 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, you can do any kind of
COMMIT action with
LOCK TABLES. To support
ROLLBACK with the above table
types, 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
isamlog could be used for this purpose), but it
would be much more difficult to implement
To avoid using
ROLLBACK, you can use the following strategy:
LOCK TABLES ...to lock all the tables you want to access.
UNLOCK TABLESto 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:
For example, when we are doing updates to some customer information, we
update only the customer data that has changed and test only that none of
the changed data, or data that depend on the changed data, has 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
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
In many cases, users have wanted
TABLES for the purpose of managing unique identifiers for some tables. This
can be handled much more efficiently by using an
and either the SQL function
LAST_INSERT_ID() or the C API function
mysql_insert_id(). See section 188.8.131.52
At MySQL AB, 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
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.