12.10.3. Information Functions

The BENCHMARK() function executes the
expression expr repeatedly
count times. It may be used to
time how quickly MySQL processes the expression. The result
value is always 0. The intended use is
from within the mysql client, which
reports query execution times:

The time reported is elapsed time on the client end, not CPU
time on the server end. It is advisable to execute
BENCHMARK() several times, and to
interpret the result with regard to how heavily loaded the
server machine is.

Returns the connection ID (thread ID) for the connection.
Every connection has an ID that is unique among the set of
currently connected clients.

mysql> SELECT CONNECTION_ID();
-> 23786

CURRENT_USER,
CURRENT_USER()

Returns the username and hostname combination for the MySQL
account that the server used to authenticate the current
client. This account determines your access privileges.
Within a stored routine that is defined with the
SQL SECURITY DEFINER characteristic,
CURRENT_USER() returns the creator of the
routine. The return value is a string in the
utf8 character set.

The example illustrates that although the client specified a
username of davida (as indicated by the
value of the USER() function), the server
authenticated the client using an anonymous user account (as
seen by the empty username part of the
CURRENT_USER() value). One way this might
occur is that there is no account listed in the grant tables
for davida.

DATABASE()

Returns the default (current) database name as a string in
the utf8 character set. If there is no
default database, DATABASE() returns
NULL. Within a stored routine, the
default database is the database that the routine is
associated with, which is not necessarily the same as the
database that is the default in the calling context.

mysql> SELECT DATABASE();
-> 'test'

If there is no default database,
DATABASE() returns
NULL.

FOUND_ROWS()

A SELECT statement may include a
LIMIT clause to restrict the number of
rows the server returns to the client. In some cases, it is
desirable to know how many rows the statement would have
returned without the LIMIT, but without
running the statement again. To obtain this row count,
include a SQL_CALC_FOUND_ROWS option in
the SELECT statement, and then invoke
FOUND_ROWS() afterward:

The second SELECT returns a number
indicating how many rows the first SELECT
would have returned had it been written without the
LIMIT clause. (If the preceding
SELECT statement does not include the
SQL_CALC_FOUND_ROWS option, then
FOUND_ROWS() may return a different
result when LIMIT is used than when it is
not.)

The row count available through
FOUND_ROWS() is transient and not
intended to be available past the statement following the
SELECT SQL_CALC_FOUND_ROWS statement. If
you need to refer to the value later, save it:

If you are using SELECT
SQL_CALC_FOUND_ROWS, MySQL must calculate how many
rows are in the full result set. However, this is faster
than running the query again without
LIMIT, because the result set need not be
sent to the client.

SQL_CALC_FOUND_ROWS and
FOUND_ROWS() can be useful in situations
when you want to restrict the number of rows that a query
returns, but also determine the number of rows in the full
result set without running the query again. An example is a
Web script that presents a paged display containing links to
the pages that show other sections of a search result. Using
FOUND_ROWS() allows you to determine how
many other pages are needed for the rest of the result.

The use of SQL_CALC_FOUND_ROWS and
FOUND_ROWS() is more complex for
UNION statements than for simple
SELECT statements, because
LIMIT may occur at multiple places in a
UNION. It may be applied to individual
SELECT statements in the
UNION, or global to the
UNION result as a whole.

The intent of SQL_CALC_FOUND_ROWS for
UNION is that it should return the row
count that would be returned without a global
LIMIT. The conditions for use of
SQL_CALC_FOUND_ROWS with
UNION are:

The SQL_CALC_FOUND_ROWS keyword must
appear in the first SELECT of the
UNION.

The value of FOUND_ROWS() is exact
only if UNION ALL is used. If
UNION without ALL
is used, duplicate removal occurs and the value of
FOUND_ROWS() is only approximate.

If no LIMIT is present in the
UNION,
SQL_CALC_FOUND_ROWS is ignored and
returns the number of rows in the temporary table that
is created to process the UNION.

LAST_INSERT_ID(),
LAST_INSERT_ID(expr)

Returns the first automatically
generated value that was set for an
AUTO_INCREMENT column by the
most recentINSERT
or UPDATE statement to affect such a
column.

mysql> SELECT LAST_INSERT_ID();
-> 195

The ID that was generated is maintained in the server on a
per-connection basis. This means that
the value returned by the function to a given client is the
first AUTO_INCREMENT value generated for
most recent statement affecting an
AUTO_INCREMENT column by that
client. This value cannot be affected by other
clients, even if they generate
AUTO_INCREMENT values of their own. This
behavior ensures that each client can retrieve its own ID
without concern for the activity of other clients, and
without the need for locks or transactions.

The value of LAST_INSERT_ID() is not
changed if you set the AUTO_INCREMENT
column of a row to a non-“magic” value (that
is, a value that is not NULL and not
0).

Important: If you insert
multiple rows using a single INSERT
statement, LAST_INSERT_ID() returns the
value generated for the first inserted
row only. The reason for this is to
make it possible to reproduce easily the same
INSERT statement against some other
server.

Although the second INSERT statement
inserted three new rows into t, the ID
generated for the first of these rows was
2, and it is this value that is returned
by LAST_INSERT_ID() for the following
SELECT statement.

If you use INSERT IGNORE and the row is
ignored, the AUTO_INCREMENT counter is
not incremented and LAST_INSERT_ID()
returns 0, which reflects that no row was
inserted.

If expr is given as an argument
to LAST_INSERT_ID(), the value of the
argument is returned by the function and is remembered as
the next value to be returned by
LAST_INSERT_ID(). This can be used to
simulate sequences:

The UPDATE statement increments the
sequence counter and causes the next call to
LAST_INSERT_ID() to return the
updated value. The SELECT statement
retrieves that value. The
mysql_insert_id() C API function can
also be used to get the value. See
Section 25.2.3.36, “mysql_insert_id()”.

You can generate sequences without calling
LAST_INSERT_ID(), but the utility of
using the function this way is that the ID value is
maintained in the server as the last automatically generated
value. It is multi-user safe because multiple clients can
issue the UPDATE statement and get their
own sequence value with the SELECT
statement (or mysql_insert_id()), without
affecting or being affected by other clients that generate
their own sequence values.

Note that mysql_insert_id() is only
updated after INSERT and
UPDATE statements, so you cannot use the
C API function to retrieve the value for
LAST_INSERT_ID(expr)
after executing other SQL statements like
SELECT or SET.

ROW_COUNT()

ROW_COUNT() returns the number of rows
updated, inserted, or deleted by the preceding statement.
This is the same as the row count that the
mysql client displays and the value from
the mysql_affected_rows() C API function.