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.
MEDIUMINT
, SET
, ENUM
and the
different BLOB
and TEXT
types.
AUTO_INCREMENT
, BINARY
,
UNSIGNED
and ZEROFILL
.
BINARY
attribute.
INTO OUTFILE
and STRAIGHT_JOIN
in a SELECT
statement. See section SELECT syntax.
EXPLAIN SELECT
to get a description on how tables are joined.
INDEX
or KEY
in a CREATE TABLE
statement. See section CREATE TABLE syntax.
DROP column
or CHANGE column
in a ALTER TABLE
statement. 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.
"
instead of '
to enclose strings.
\
character.
SET OPTION
statement. See section SET OPTION syntax.
SELECT
part of a GROUP BY
statement, fields
or functions that do not appear in the GROUP BY
list. 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 BY
clause which is redundant in MySQL.
||
and &&
operators are synonyms for OR
and
AND
in MySQL, like in the C programming language.
Likewise |
and &
stands for bitwise OR
and
AND
. Because if this nice syntax, MySQL doesn't support
the ANSI SQL operator ||
for string concatenation, and one must
use CONCAT()
instead. As CONCAT()
takes any number
of arguments, it's easy to convert use of the ||
operator to
MySQL.
CREATE DATABASE
or DROP DATABASE
.
See section Create database syntax.
%
instead of mod(). %
is supported for C programmers and
for compatibility with postgreSQL.
=
, <>
, <=
,<
, >=
,>
, AND
,
OR
, or LIKE
in a column statement
LAST_INSERT_ID
.
See section How can I get the unique ID for the last inserted row?
REGEXP
or NOT REGEXP
.
CONCAT()
or CHAR()
with one or more than two arguments. In
MySQL they can take any number of arguments.
BIT_COUNT()
, ELT()
, FROM_DAYS()
, FORMAT()
,
IF()
, PASSWORD()
, ENCRYPT()
,
PERIOD_ADD()
, PERIOD_DIFF()
, TO_DAYS()
,
or WEEKDAY()
.
TRIM
to trim substrings. ANSI SQL only supports removal
of single characters.
STD()
, BIT_OR
and BIT_AND
group functions.
MIN()
or MAX()
as normal functions, not only group
functions.
REPLACE
instead of DELETE
+ 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 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.
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.
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.
There are so many problems with the FOREIGN KEY
s that we don't
know where to start.
INSERTING
and
UPDATING
records and in this case almost all FOREIGN KEY
checks 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
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
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 TABLE
to drop indexes. See section ALTER TABLE syntax.
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);
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:
LOCK TABLES ...
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.