Table of Contents
The discussion here describes restrictions that apply to the use of MySQL features such as subqueries or views.
Some of the restrictions noted here apply to all stored routines; that is, both to stored procedures and stored functions. Some of restrictions apply only to stored functions, and not to stored procedures.
All of the restrictions for stored functions also apply to triggers.
Stored routines cannot contain arbitrary SQL statements. The following statements are disallowed:
The table-maintenance statements CHECK
TABLES
and OPTIMIZE TABLES
. This
restriction is lifted beginning with MySQL 5.0.17.
The locking statements LOCK TABLES
,
UNLOCK TABLES
.
LOAD DATA
and LOAD
TABLE
.
SQL prepared statements (PREPARE
,
EXECUTE
, DEALLOCATE
PREPARE
). Implication: You cannot use dynamic SQL
within stored routines (where you construct dynamically
statements as strings and then execute them). This restriction
is lifted as of MySQL 5.0.13 for stored procedures; it still
applies to stored functions and triggers.
In addition, SQL statements that are not permitted within prepared statements are also not permitted in stored routines. See Section 12.7, “SQL Syntax for Prepared Statements”, for a list of statements supported in prepared statements. Statements not listed there are not supported for SQL prepared statements and thus are also not supported for stored routines unless noted otherwise in Chapter 18, Stored Procedures and Functions.
For stored functions (but not stored procedures), the following additional statements or operations are disallowed:
Statements that do explicit or implicit commit or rollback.
Statements that return a result set. This includes
SELECT
statements that do not have an
INTO
clause and var_list
SHOW
statements. A function can
process a result set either with SELECT ... INTO
or by using a
cursor and var_list
FETCH
statements. See
Section 18.2.7.3, “SELECT ... INTO
Statement”.
FLUSH
statements.
Before MySQL 5.0.10, stored functions created with
CREATE FUNCTION
must not contain references
to tables, with limited exceptions. They may include some
SET
statements that contain table
references, for example SET a:= (SELECT MAX(id) FROM
t)
, and SELECT
statements that
fetch values directly into variables, for example
SELECT i INTO var1 FROM t
.
Recursive statements. That is, stored functions cannot be used recursively.
Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.
ALTER VIEW
.
Note that although some restrictions normally apply to stored
functions and triggers but not to stored procedures, those
restrictions do apply to stored procedures if they are invoked
from within a stored function or trigger. For example, although
you can use FLUSH
in a stored procedure, such a
stored procedure cannot be called from a stored function or
trigger.
It is possible for the same identifier to be used for a routine parameter, a local variable, and a table column. Also, the same local variable name can be used in nested blocks. For example:
CREATE PROCEDURE p (i INT) BEGIN DECLARE i INT DEFAULT 0; SELECT i FROM t; BEGIN DECLARE i INT DEFAULT 1; SELECT i FROM t; END; END;
In such cases the identifier is ambiguous and the following precedence rules apply:
A local variable takes precedence over a routine parameter or table column
A routine parameter takes precedence over a table column
A local variable in an inner block takes precedence over a local variable in an outer block
The behavior that variables take precedence over table columns is non-standard.
Use of stored routines can cause replication problems. This issue is discussed further in Section 18.5, “Binary Logging of Stored Routines and Triggers”.
INFORMATION_SCHEMA
does not have a
PARAMETERS
table until MySQL 6.0, so
applications that need to acquire routine parameter information at
runtime must use workarounds such as parsing the output of
SHOW CREATE
statements or the
param_list
column of the
mysql.proc
table. param_list
contents can be processed from within a stored routine, unlike the
output from SHOW
.
There are no stored routine debugging facilities.
Before MySQL 5.0.17, CALL
statements cannot be
prepared. This true both for server-side prepared statements and
for SQL prepared statements.
UNDO
handlers are not supported.
FOR
loops are not supported.
To prevent problems of interaction between server threads, when a client issues a statement, the server uses a snapshot of routines and triggers available for execution of the statement. That is, the server calculates a list of procedures, functions, and triggers that may be used during execution of the statement, loads them, and then proceeds to execute the statement. This means that while the statement executes, it will not see changes to routines performed by other threads.
For triggers, the following additional statements or operations are disallowed:
Triggers currently are not activated by foreign key actions.
The RETURN
statement is disallowed in
triggers, which cannot return a value. To exit a trigger
immediately, use the LEAVE
statement.
Triggers are not allowed on tables in the
mysql
database.
Server-side cursors are implemented beginning with the C API in
MySQL 5.0.2 via the
mysql_stmt_attr_set()
function. A
server-side cursor allows a result set to be generated on the
server side, but not transferred to the client except for those
rows that the client requests. For example, if a client executes a
query but is only interested in the first row, the remaining rows
are not transferred.
In MySQL, a server-side cursor is materialized into a temporary
table. Initially, this is a MEMORY
table, but
is converted to a MyISAM
table if its size
reaches the value of the max_heap_table_size
system variable. (Beginning with MySQL 5.0.14, the same
temporary-table implementation also is used for cursors in stored
routines.) One limitation of the implementation is that for a
large result set, retrieving its rows through a cursor might be
slow.
Cursors are read only; you cannot use a cursor to update rows.
UPDATE WHERE CURRENT OF
and DELETE
WHERE CURRENT OF
are not implemented, because updatable
cursors are not supported.
Cursors are non-holdable (not held open after a commit).
Cursors are asensitive.
Cursors are non-scrollable.
Cursors are not named. The statement handler acts as the cursor ID.
You can have open only a single cursor per prepared statement. If you need several cursors, you must prepare several statements.
You cannot use a cursor for a statement that generates a result
set if the statement is not supported in prepared mode. This
includes statements such as CHECK TABLES
,
HANDLER READ
, and SHOW BINLOG
EVENTS
.
In MySQL 5.0 before 5.0.36, if you compare a
NULL
value to a subquery using
ALL
, ANY
, or
SOME
, and the subquery returns an empty
result, the comparison might evaluate to the non-standard
result of NULL
rather than to
TRUE
or FALSE
.
A subquery's outer statement can be any one of:
SELECT
, INSERT
,
UPDATE
, DELETE
,
SET
, or DO
.
Subquery optimization for IN
is not as
effective as for the =
operator or for the
IN(
operator.
value_list
)
A typical case for poor IN
subquery
performance is when the subquery returns a small number of
rows but the outer query returns a large number of rows to be
compared to the subquery result.
The problem is that, for a statement that uses an
IN
subquery, the optimizer rewrites it as a
correlated subquery. Consider the following statement that
uses an uncorrelated subquery:
SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2);
The optimizer rewrites the statement to a correlated subquery:
SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);
If the inner and outer queries return
M
and N
rows, respectively, the execution time becomes on the order of
O(
,
rather than
M
×N
)O(
as it would be for an uncorrelated subquery.
M
+N
)
An implication is that an IN
subquery can
be much slower than a query written using an
IN(
operator that lists the same values that the subquery would
return.
value_list
)
In general, you cannot modify a table and select from the same table in a subquery. For example, this limitation applies to statements of the following forms:
DELETE FROM t WHERE ... (SELECT ... FROM t ...); UPDATE t ... WHERE col = (SELECT ... FROM t ...); {INSERT|REPLACE} INTO t (SELECT ... FROM t ...);
Exception: The preceding prohibition does not apply if you are
using a subquery for the modified table in the
FROM
clause. Example:
UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...) AS _t ...);
Here the prohibition does not apply because the result from a
subquery in the FROM
clause is stored as a
temporary table, so the relevant rows in t
have already been selected by the time the update to
t
takes place.
Row comparison operations are only partially supported:
For
,
expr
IN
(subquery
)expr
can be an
n
-tuple (specified via row
constructor syntax) and the subquery can return rows of
n
-tuples.
For
,
expr
op
{ALL|ANY|SOME}
(subquery
)expr
must be a scalar value and
the subquery must be a column subquery; it cannot return
multiple-column rows.
In other words, for a subquery that returns rows of
n
-tuples, this is supported:
(val_1
, ...,val_n
) IN (subquery
)
But this is not supported:
(val_1
, ...,val_n
)op
{ALL|ANY|SOME} (subquery
)
The reason for supporting row comparisons for
IN
but not for the others is that
IN
is implemented by rewriting it as a
sequence of
=
comparisons and AND
operations. This approach cannot be used for
ALL
, ANY
, or
SOME
.
Row constructors are not well optimized. The following two expressions are equivalent, but only the second can be optimized:
(col1, col2, ...) = (val1, val2, ...) col1 = val1 AND col2 = val2 AND ...
Subqueries in the FROM
clause cannot be
correlated subqueries. They are materialized (executed to
produce a result set) before evaluating the outer query, so
they cannot be evaluated per row of the outer query.
The optimizer is more mature for joins than for subqueries, so in many cases a statement that uses a subquery can be executed more efficiently if you rewrite it as a join.
An exception occurs for the case where an
IN
subquery can be rewritten as a
SELECT DISTINCT
join. Example:
SELECT col FROM t1 WHERE id_col IN (SELECT id_col2 FROM t2 WHERE condition
);
That statement can be rewritten as follows:
SELECT DISTINCT col FROM t1, t2 WHERE t1.id_col = t2.id_col AND condition
;
But in this case, the join requires an extra
DISTINCT
operation and is not more
efficient than the subquery.
Possible future optimization: MySQL does not rewrite the join order for subquery evaluation. In some cases, a subquery could be executed more efficiently if MySQL rewrote it as a join. This would give the optimizer a chance to choose between more execution plans. For example, it could decide whether to read one table or the other first.
Example:
SELECT a FROM outer_table AS ot WHERE a IN (SELECT a FROM inner_table AS it WHERE ot.b = it.b);
For that query, MySQL always scans
outer_table
first and then executes the
subquery on inner_table
for each row. If
outer_table
has a lot of rows and
inner_table
has few rows, the query
probably will not be as fast as it could be.
The preceding query could be rewritten like this:
SELECT a FROM outer_table AS ot, inner_table AS it WHERE ot.a = it.a AND ot.b = it.b;
In this case, we can scan the small table
(inner_table
) and look up rows in
outer_table
, which will be fast if there is
an index on (ot.a,ot.b)
.
Possible future optimization: A correlated subquery is evaluated for each row of the outer query. A better approach is that if the outer row values do not change from the previous row, do not evaluate the subquery again. Instead, use its previous result.
Possible future optimization: A subquery in the
FROM
clause is evaluated by materializing
the result into a temporary table, and this table does not use
indexes. This does not allow the use of indexes in comparison
with other tables in the query, although that might be useful.
Possible future optimization: If a subquery in the
FROM
clause resembles a view to which the
merge algorithm can be applied, rewrite the query and apply
the merge algorithm so that indexes can be used. The following
statement contains such a subquery:
SELECT * FROM (SELECT * FROM t1 WHERE t1.t1_col) AS _t1, t2 WHERE t2.t2_col;
The statement can be rewritten as a join like this:
SELECT * FROM t1, t2 WHERE t1.t1_col AND t2.t2_col;
This type of rewriting would provide two benefits:
It avoids the use of a temporary table for which no
indexes can be used. In the rewritten query, the optimizer
can use indexes on t1
.
It gives the optimizer more freedom to choose between
different execution plans. For example, rewriting the
query as a join allows the optimizer to use
t1
or t2
first.
Possible future optimization: For IN
,
= ANY
, <> ANY
,
= ALL
, and <> ALL
with uncorrelated subqueries, use an in-memory hash for a
result or a temporary table with an index for larger results.
Example:
SELECT a FROM big_table AS bt WHERE non_key_field IN (SELECT non_key_field FROMtable
WHEREcondition
)
In this case, we could create a temporary table:
CREATE TABLE t (key (non_key_field)) (SELECT non_key_field FROMtable
WHEREcondition
)
Then, for each row in big_table
, do a key
lookup in t
based on
bt.non_key_field
.
View processing is not optimized:
It is not possible to create an index on a view.
Indexes can be used for views processed using the merge algorithm. However, a view that is processed with the temptable algorithm is unable to take advantage of indexes on its underlying tables (although indexes can be used during generation of the temporary tables).
Subqueries cannot be used in the FROM
clause of
a view. This limitation will be lifted in the future.
There is a general principle that you cannot modify a table and select from the same table in a subquery. See Section F.3, “Restrictions on Subqueries”.
The same principle also applies if you select from a view that selects from the table, if the view selects from the table in a subquery and the view is evaluated using the merge algorithm. Example:
CREATE VIEW v1 AS SELECT * FROM t2 WHERE EXISTS (SELECT 1 FROM t1 WHERE t1.a = t2.a); UPDATE t1, v2 SET t1.a = 1 WHERE t1.b = v2.b;
If the view is evaluated using a temporary table, you
can select from the table in the view
subquery and still modify that table in the outer query. In this
case the view will be stored in a temporary table and thus you are
not really selecting from the table in a subquery and modifying it
“at the same time.” (This is another reason you might
wish to force MySQL to use the temptable algorithm by specifying
ALGORITHM = TEMPTABLE
in the view definition.)
You can use DROP TABLE
or ALTER
TABLE
to drop or alter a table that is used in a view
definition (which invalidates the view) and no warning results
from the drop or alter operation. An error occurs later when the
view is used.
A view definition is “frozen” by certain statements:
If a statement prepared by PREPARE
refers
to a view, the view contents seen each time the statement is
executed later will be the contents of the view at the time it
was prepared. This is true even if the view definition is
changed after the statement is prepared and before it is
executed. Example:
CREATE VIEW v AS SELECT 1; PREPARE s FROM 'SELECT * FROM v'; ALTER VIEW v AS SELECT 2; EXECUTE s;
The result returned by the EXECUTE
statement is 1, not 2.
If a statement in a stored routine refers to a view, the view contents seen by the statement are its contents the first time that statement is executed. For example, this means that if the statement is executed in a loop, further iterations of the statement see the same view contents, even if the view definition is changed later in the loop. Example:
CREATE VIEW v AS SELECT 1; delimiter // CREATE PROCEDURE p () BEGIN DECLARE i INT DEFAULT 0; WHILE i < 5 DO SELECT * FROM v; SET i = i + 1; ALTER VIEW v AS SELECT 2; END WHILE; END; // delimiter ; CALL p();
When the procedure p()
is called, the
SELECT
returns 1 each time through the
loop, even though the view definition is changed within the
loop.
With regard to view updatability, the overall goal for views is
that if any view is theoretically updatable, it should be
updatable in practice. This includes views that have
UNION
in their definition. Currently, not all
views that are theoretically updatable can be updated. The initial
view implementation was deliberately written this way to get
usable, updatable views into MySQL as quickly as possible. Many
theoretically updatable views can be updated now, but limitations
still exist:
Updatable views with subqueries anywhere other than in the
WHERE
clause. Some views that have
subqueries in the SELECT
list may be
updatable.
You cannot use UPDATE
to update more than
one underlying table of a view that is defined as a join.
You cannot use DELETE
to update a view that
is defined as a join.
There exists a shortcoming with the current implementation of
views. If a user is granted the basic privileges necessary to
create a view (the CREATE VIEW
and
SELECT
privileges), that user will be unable to
call SHOW CREATE VIEW
on that object unless the
user is also granted the SHOW VIEW
privilege.
That shortcoming can lead to problems backing up a database with mysqldump, which may fail due to insufficient privileges. This problem is described in Bug#22062.
The workaround to the problem is for the administrator to manually
grant the SHOW VIEW
privilege to users who are
granted CREATE VIEW
, since MySQL doesn't grant
it implicitly when views are created.
Views do not have indexes, so index hints do not apply. Use of index hints when selecting from a view is disallowed.
XA transaction support is limited to the InnoDB
storage engine.
The MySQL XA implementation is for “external XA,”
where a MySQL server acts as a Resource Manager and client
programs act as Transaction Managers. “Internal XA”
is not implemented. This would allow individual storage engines
within a MySQL server to act as RMs, and the server itself to act
as a TM. Internal XA is required for handling XA transactions that
involve more than one storage engine. The implementation of
internal XA is incomplete because it requires that a storage
engine support two-phase commit at the table handler level, and
currently this is true only for InnoDB
.
For XA START
, the JOIN
and
RESUME
clauses are not supported.
For XA END
, the SUSPEND [FOR
MIGRATE]
clause is not supported.
The requirement that the bqual
part of
the xid
value be different for each XA
transaction within a global transaction is a limitation of the
current MySQL XA implementation. It is not part of the XA
specification.
If an XA transaction has reached the PREPARED
state and the MySQL server is killed (for example, with
kill -9 on Unix) or shuts down abnormally, the
transaction can be continued after the server restarts. However,
if the client reconnects and commits the transaction, the
transaction will be absent from the binary log even though it has
been committed. This means the data and the binary log have gone
out of synchrony. An implication is that XA cannot be used safely
together with replication.
It is possible that the server will roll back a pending XA
transaction, even one that has reached the
PREPARED
state. This happens if a client
connection terminates and the server continues to run, or if
clients are connected and the server shuts down gracefully. (In
the latter case, the server marks each connection to be
terminated, and then rolls back the PREPARED
XA
transaction associated with it.) It should be possible to commit
or roll back a PREPARED
XA transaction, but
this cannot be done without changes to the binary logging
mechanism.
Identifiers are stored in mysql
database
tables (user
, db
, and so
forth) using utf8
, but identifiers can
contain only characters in the Basic Multilingual Plane (BMP).
Supplementary characters are not allowed in identifiers.
The ucs2
character sets has the following
restrictions:
It cannot be used as a client character set, which means
that it does not work for SET NAMES
or
SET CHARACTER SET
. (See
Section 9.1.4, “Connection Character Sets and Collations”.)
It is currently not possible to use LOAD DATA
INFILE
to load data files that use this
character set.
FULLTEXT
indexes cannot be created on a
column that this character set. However, you can perform
IN BOOLEAN MODE
searches on the column
without an index.
The REGEXP
and
RLIKE
operators work in byte-wise fashion, so they are not
multi-byte safe and may produce unexpected results with
multi-byte character sets. In addition, these operators
compare characters by their byte values and accented
characters may not compare as equal even if a given collation
treats them as equal.
This section lists current limits in MySQL 5.0.
The maximum number of tables that can be referenced in a single join is 61. This also applies to the number of tables that can be referenced in the definition of a view.
There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table. The exact limit depends on several interacting factors, listed in the following discussion.
Every table has a maximum row size of 65,535 bytes. This maximum applies to all storage engines, but a given engine might have additional constraints that result in a lower effective maximum row size.
The maximum row size constrains the number of columns
because the total width of all columns cannot exceed this
size. For example, utf8
characters
require up to three bytes per character, so for a
CHAR(255) CHARACTER SET utf8
column, the
server must allocate 255 × 3 = 765 bytes per value.
Consequently, a table cannot contain more than 65,535 / 765
= 85 such columns.
Storage for variable-length columns includes length bytes,
which are assessed against the row size. For example, a
VARCHAR(255) CHARACTER SET utf8
column
takes two bytes to store the length of the value, so each
value can take up to 767 bytes.
BLOB
and TEXT
columns
count from one to four plus eight bytes each toward the
row-size limit because their contents are stored separately.
Declaring columns NULL
can reduce the
maximum number of columns allowed. NULL
columns require additional space in the row to record
whether or not their values are NULL
.
For MyISAM
tables, each
NULL
column takes one bit extra, rounded
up to the nearest byte. The maximum row length in bytes can
be calculated as follows:
row length = 1 + (sum of column lengths
) + (number of NULL columns
+delete_flag
+ 7)/8 + (number of variable-length columns
)
delete_flag
is 1 for tables with
static row format. Static tables use a bit in the row record
for a flag that indicates whether the row has been deleted.
delete_flag
is 0 for dynamic
tables because the flag is stored in the dynamic row header.
These calculations do not apply for
InnoDB
tables, for which storage size is
no different for NULL
columns than for
NOT NULL
columns.
The following statement to create table
t1
succeeds because the columns require
32,765 + 2 bytes and 32,766 + 2 bytes, which falls within
the maximum row size of 65,535 bytes:
mysql>CREATE TABLE t1
->(c1 VARCHAR(32765) NOT NULL, c2 VARCHAR(32766) NOT NULL);
Query OK, 0 rows affected (0.01 sec)
The following statement to create table
t2
fails because the columns are
NULL
and require additional space that
causes the row size to exceed 65,535 bytes:
mysql>CREATE TABLE t2
->(c1 VARCHAR(32765) NULL, c2 VARCHAR(32766) NULL);
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
Each table has an .frm
file that
contains the table definition. The .frm
file size limit is fixed at 64KB. If a table definition
reaches this size, no more columns can be added. The
expression that checks information to be stored in the
.frm
file against the limit looks like
this:
if (info_length+(ulong) create_fields.elements*FCOMP+288+ n_length+int_length+com_length > 65535L || int_count > 255)
The relevant factors in this expression are:
info_length
is space needed for
“screens.” This is related to MySQL's
Unireg heritage.
create_fields.elements
is the number
of columns.
FCOMP
is 17.
n_length
is the total length of all
column names, including one byte per name as a
separator.
int_length
is related to the list of
values for SET and ENUM columns.
com_length
is the total length of
column and table comments.
Thus, using long column names can reduce the maximum number
of columns, as can the inclusion of ENUM
or SET
columns, or use of column or table
comments.
Individual storage engines might impose additional restrictions that limit table column count. Examples:
InnoDB
allows no more than 1000
columns.
InnoDB
restricts row size to
something less than half a database page (approximately
8000 bytes), not including VARBINARY
,
VARCHAR
, BLOB
, or
TEXT
columns.
Different InnoDB
storage formats
(COMPRESSED
,
REDUNDANT
) use different amounts of
page header and trailer data, which affects the amount
of storage available for rows.
The following limitations apply only to the Windows platform:
The number of open file descriptors on Windows is limited to a maximum of 2048, which may limit the ability to open a large number of tables simultaneously. This limit is due to the compatibility functions used to open files on Windows that use the POSIX compatibility layer.
This limitation will also cause problems if you try to set
max_open_files
to a value greater than the
2048 file limit.
On Windows 32-bit platforms it is not possible to use more than 2GB of RAM within a single process, including MySQL. This is because the physical address limit on Windows 32-bit is 4GB and the default setting within Windows is to split the virtual address space between kernel (2GB) and user/applications (2GB).
To use more memory than this you will need to use a 64-bit version of Windows.
When using MyISAM
tables, you cannot use
aliases within Windows link to the data files on another
volume and then link back to the main MySQL
datadir
location.
This facility is often used to move the data and index files
to a RAID or other fast solution, while retaining the main
.FRM
files in the default data
directory configured with the datadir
option.
The timers within MySQL used on Windows are of a lower
precision than the timers used on Linux. For most situations
you may not notice a difference, but the delay implied by a
call to SLEEP()
on Windows
and Linux may differ slightly due to the differences in
precision.
There is no 64-bit OLEDB Provider for ODBC (MSDASQL) in any 64-bit Windows operating system up to and including Windows Vista. In practical terms this means that you can't use the MySQL ODBC driver from ADO and other users of OLEDB.