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.
ENUMand the different
SELECTstatement. See section SELECT syntax.
EXPLAIN SELECTto get a description on how tables are joined.
CREATE TABLEstatement. See section CREATE TABLE syntax.
CHANGE columnin a
ALTER TABLEstatement. See section ALTER TABLE syntax.
LOAD DATA INFILE. This syntax is in many cases compatible with Oracles
LOAD DATA INFILE. See section LOAD DATA INFILE syntax.
'to enclose strings.
SET OPTIONstatement. See section SET OPTION syntax.
SELECTpart of a
GROUP BYstatement, fields or functions that do not appear in the
GROUP BYlist. In MySQL this means 'any matching value'. By using this one can get a much higher performance by avoiding sorting and grouping unnecessary items. This is often used in this context:
SELECT order.custid,customer.name,max(payments) from order,customer WHERE order.custid = customer.custid GROUP BY order.custid;In ANSI SQL you would have to add the customer.name in the
GROUP BYclause which is redundant in MySQL.
&&operators are synonyms for
ANDin MySQL, like in the C programming language. Likewise
&stands for bitwise
AND. Because if this nice syntax, MySQL doesn't support the ANSI SQL operator
||for string concatenation, and one must use
CONCAT()takes any number of arguments, it's easy to convert use of the
||operator to MySQL.
DROP DATABASE. See section Create database syntax.
%instead of mod().
%is supported for C programmers and for compatibility with postgreSQL.
LIKEin a column statement
LAST_INSERT_ID. See section How can I get the unique ID for the last inserted row?
CHAR()with one or more than two arguments. In MySQL they can take any number of arguments.
TRIMto trim substrings. ANSI SQL only supports removal of single characters.
MAX()as normal functions, not only group functions.
INSERT. See section REPLACE syntax
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..
The following will not work in MySQL:
SELECT * from table WHERE id IN (SELECT id from table2)
MySQL only supports
INSERT ... SELECT... and
... SELECT.... Independent sub-selects will be probably be available in
3.22.0. One can now use the function
IN() in other context
MySQL doesn't yet support
SELECT ... INTO TABLE.... Currently
MySQL only supports
SELECT ... INTO OUTFILE....
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 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 KEY syntax in MySQL exists only for compatibility
with other SQL vendors
CREATE TABLE commands: it doesn't do anything.
FOREIGN KEY syntax without
ON DELETE .. is mostly used
for documentation purposes. Some ODBC applications may uses this to
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
... 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 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
There are so many problems with the
FOREIGN KEYs that we don't
know where to start.
UPDATINGrecords and in this case almost all
FOREIGN KEYchecks are useless because one usually inserts records in the right tables in the right order.
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.
MySQL doesn't support views, but this is on the TODO.
Some other SQL databases have
-- as start comment. MySQL
# as the start comment character, even if the
command line tool removes all lines that starts with
You can also use the C comment style
/* this is a comment */ with
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
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
# comments in the command file:
replace " --" " #" -- text-with-funny-comments.sql
Change them back with:
replace " #" " --" -- text-with-funny-comments.sql
Entry level SQL92. ODBC level 0-2.
GRANT. See section GRANT syntax. (Compatibility function). This always succeeds. You should use the MySQL privilege tables. See section How does the privilege system work?
CREATE INDEX. See section CREATE INDEX syntax (Compatibility function). This always succeeds. You should create your index with
CREATE TABLE. See section CREATE TABLE syntax. You can also use
ALTER TABLE. See section ALTER TABLE syntax.
DROP INDEX. See section DROP INDEX syntax (Compatibility function). This always succeeds. You can use
ALTER TABLEto drop indexes. See section ALTER TABLE syntax.
If you want to
GROUP BY or
ORDER BY on a
TEXT field, you must make the field into a fixed length
object. The standard way to do this is with the
functions. If you don't do this only the first
(default=1024) will considered when sorting.
SELECT comment from table order by substring(comment,20);
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
you can do anything with
LOCK TABLES. To support
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
ALTER/DROP/CREATE TABLE it would make
everything much harder to implement.
To avoid using
ROLLBACK one can do:
LOCK 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
TABLES to manage unique identifiers for some tables. This can be
handled much more efficiently by using an
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;
affected rows = 1 if the row was found and row_flag
wasn't 1 in the original document. On the TODO there is
RELEASE_LOCK for those that want to implement application level
Go to the first, previous, next, last section, table of contents.