MySQL - 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
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 version number:
CREATE /*!32302 TEMPORARY */ TABLE (a int);
The above means that if you have Version 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, NULL,
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 a few
Database names and table names are case sensitive in
MySQL on operating systems that have case-sensitive
filenames (like most Unix systems).
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 doesn't support tablespaces as 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
The SQL_SMALL_RESULT option in a SELECT statement.
EXPLAIN SELECT to get a description on how tables are
Use of index names, indexes on a prefix of a field, and
use of INDEX or KEY in a CREATE TABLE statement.
Use of TEMPORARY or IF NOT EXISTS with CREATE TABLE.
Use of COUNT(DISTINCT list) where 'list' is more than
Use of CHANGE col_name, DROP col_name, or DROP INDEX,
IGNORE or RENAME in an ALTER TABLE statement.
Use of RENAME TABLE.
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
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.
The ANALYZE TABLE, CHECK TABLE, OPTIMIZE TABLE, and
REPAIR TABLE statements.
The SHOW statement.
Strings may be enclosed by either `"' or `'', not just
Use of the escape `\' character.
The SET OPTION statement.
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.
One can specify ASC and DESC with GROUP BY.
To make it easier for users who 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. Because CONCAT() takes any number of
arguments, it's easy to convert use of the || operator
CREATE DATABASE or DROP DATABASE.
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.
The REGEXP and NOT REGEXP extended regular expression
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()
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.
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
Page Numbers : 1