SELECT
Syntax
1086
SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15
To retrieve all rows from a certain offset up to the end of the result set, you can use some large
number for the second parameter. This statement retrieves all rows from the 96th row to the last:
SELECT * FROM tbl LIMIT 95,18446744073709551615;
With one argument, the value specifies the number of rows to return from the beginning of the result
set:
SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows
In other words,
LIMIT row_count
is equivalent to
LIMIT 0, row_count
.
For prepared statements, you can use placeholders (supported as of MySQL version 5.0.7). The
following statements will return one row from the
tbl
table:
SET @a=1;
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
EXECUTE STMT USING @a;
The following statements will return the second to sixth row from the
tbl
table:
SET @skip=1; SET @numrows=5;
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?';
EXECUTE STMT USING @skip, @numrows;
For compatibility with PostgreSQL, MySQL also supports the
LIMIT row_count OFFSET offset
syntax.
If
LIMIT
occurs within a subquery and also is applied in the outer query, the outermost
LIMIT
takes
precedence. For example, the following statement produces two rows, not one:
(SELECT ... LIMIT 1) LIMIT 2;
• A
PROCEDURE
clause names a procedure that should process the data in the result set. For an
example, see
Section 8.8.2, “Using
PROCEDURE ANALYSE
”
, which describes
ANALYSE
, a procedure
that can be used to obtain suggestions for optimal column data types that may help reduce table
sizes.
• The
SELECT ... INTO
form of
SELECT
enables the query result to be written to a file or stored in
variables. For more information, see
Section 13.2.8.1, “
SELECT ... INTO
Syntax”
.
• If you use
FOR UPDATE
with a storage engine that uses page or row locks, rows examined by the
query are write-locked until the end of the current transaction. Using
LOCK IN SHARE MODE
sets
a shared lock that permits other transactions to read the examined rows but not to update or delete
them. See
Section 14.2.7.3, “
SELECT ... FOR UPDATE
and
SELECT ... LOCK IN SHARE
MODE
Locking Reads”
.
Following the
SELECT
keyword, you can use a number of options that affect the operation of the
statement.
HIGH_PRIORITY
,
STRAIGHT_JOIN
, and options beginning with
SQL_
are MySQL
extensions to standard SQL.
• The
ALL
and
DISTINCT
options specify whether duplicate rows should be returned.
ALL
(the
default) specifies that all matching rows should be returned, including duplicates.
DISTINCT
specifies removal of duplicate rows from the result set. It is an error to specify both options.
DISTINCTROW
is a synonym for
DISTINCT
.
•
HIGH_PRIORITY
gives the
SELECT
higher priority than a statement that updates a table.
You should use this only for queries that are very fast and must be done at once. A
SELECT
HIGH_PRIORITY
query that is issued while the table is locked for reading runs even if there is an
update statement waiting for the table to be free. This affects only storage engines that use only
table-level locking (such as
MyISAM
,
MEMORY
, and
MERGE
).
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...