1.8.1 MySQL Extensions to Standard SQL

MySQL Server supports some extensions that you probably will not
find in other SQL DBMSs. 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 following form:

/*! MySQL-specific code */

In this case, MySQL Server parses and executes the code within
the comment as it would any other SQL statement, but other SQL
servers will ignore the extensions. For example, MySQL Server
recognizes the STRAIGHT_JOIN keyword in the
following statement, but other servers will not:

SELECT /*! STRAIGHT_JOIN */ col1 FROM table1,table2 WHERE ...

If you add a version number after the
“!” character, the syntax within
the comment is executed only if the MySQL version is greater
than or equal to the specified version number. The
TEMPORARY keyword in the following comment is
executed only by servers from MySQL 3.23.02 or higher:

CREATE /*!32302 TEMPORARY */ TABLE t (a INT);

The following descriptions list MySQL extensions, organized by
category.

Organization of data on disk

MySQL Server maps each database to a directory under the
MySQL data directory, and maps tables within a database to
file names in the database directory. This has a few
implications:

You can use standard system commands to back up, rename,
move, delete, and copy tables that are managed by the
MyISAM storage engine. For example,
it is possible to rename a MyISAM
table by renaming the .MYD,
.MYI, and .frm
files to which the table corresponds. (Nevertheless, it
is preferable to use RENAME
TABLE or ALTER TABLE ...
RENAME and let the server rename the files.)

General language syntax

By default, strings can be enclosed by
“"” as well as
“'”. If the
ANSI_QUOTES SQL mode
is enabled, strings can be enclosed only by
“'” and the server
interprets strings enclosed by
“"” as identifiers.

“\” is the escape
character in strings.

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 Server doesn't
support tablespaces such as used in statements like
this: CREATE TABLE ralph.my_table ... IN
my_tablespace.

To make it easier for users who migrate from other SQL
environments, MySQL Server supports aliases for many
functions. For example, all string functions support
both standard SQL syntax and ODBC syntax.

MySQL Server understands the
|| and
&&
operators to mean logical OR and AND, as in the C
programming language. In MySQL Server,
|| and
OR are
synonyms, as are
&&
and AND.
Because of this nice syntax, MySQL Server doesn't
support the standard SQL
|| operator
for string concatenation; use
CONCAT() instead. Because
CONCAT() takes any number
of arguments, it is easy to convert use of the
|| operator
to MySQL Server.

String comparisons are case-insensitive by default, with
sort ordering determined by the collation of the current
character set, which is latin1
(cp1252 West European) 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 using the underlying character code values
rather than a lexical ordering.

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 expressions in the output
column list (to the left of the FROM)
in SELECT statements. For
example: