Go to the first, previous, next, last section, table of contents.
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 `.MYD', `.MYI'
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.12 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.7 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.8 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.16 LOAD DATA INFILE syntax.
-
The
OPTIMIZE TABLE statement.
See section 7.9 OPTIMIZE TABLE syntax.
-
The
SHOW statement.
See section 7.21 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.25 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.4.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.5 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.15 REPLACE syntax.
-
The
FLUSH flush_option statement.
-
The possiblity to set variables in a statement with
:=:
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 behaviour
of MySQL changes.
-
|| is string concatenation instead of OR.
-
One can have any number of spaces between a function name and the '('. This
makes also all function names reserved words.
-
" will be a identifier quote character (like the MySQL `
quote character) and not a string quote character.
-
REAL will be a synonym for FLOAT instead of a synonym of
DOUBLE.
We try to make MySQL follow the ANSI SQL standard and the
ODBC SQL standard, but in some cases MySQL does some things
differently:
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).
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 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
the DELETE statement (DELETE FROM ... WHERE ... IN (key1,
key2, ...)).
The second option is to use interactive SQL to contruct a set of
DELETE statements automatically, using the MySQL
extension CONCAT() (in lieu of the standard || operator).
For example:
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
the 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 be probably
be available in 3.24.0. You can now use the function IN() in
other contexts, however.
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.
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, in the next few releases,
on introducing 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 commited 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 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 are that there are more than 1,000,000 mysqld servers
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 do
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 relaease their read
locks. With INSERT DELAYED you can queue insert into a local queue,
until the locks are released, without having to have the client to wait
for the insert to complete.
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 that 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.
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.
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 F List of things we want to add to MySQL in the future (The TODO).
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.13 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.
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.
MySQL doesn't support views, but this is on the TODO.
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.29 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
Entry level SQL92. ODBC level 0-2.
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:
-
Use
LOCK TABLES ... to lock all the tables you want to access.
-
Test conditions.
-
Update if everything is okay.
-
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.
| 
