(mysql.info) information-functions
Info Catalog
(mysql.info) encryption-functions
(mysql.info) other-functions
(mysql.info) miscellaneous-functions
12.9.3 Information Functions
----------------------------
* `BENCHMARK(COUNT,EXPR)'
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:
mysql> SELECT BENCHMARK(1000000,ENCODE('hello','goodbye'));
+----------------------------------------------+
| BENCHMARK(1000000,ENCODE('hello','goodbye')) |
+----------------------------------------------+
| 0 |
+----------------------------------------------+
1 row in set (4.74 sec)
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.
* `CHARSET(STR)'
Returns the character set of the string argument.
mysql> SELECT CHARSET('abc');
-> 'latin1'
mysql> SELECT CHARSET(CONVERT('abc' USING utf8));
-> 'utf8'
mysql> SELECT CHARSET(USER());
-> 'utf8'
* `COERCIBILITY(STR)'
Returns the collation coercibility value of the string argument.
mysql> SELECT COERCIBILITY('abc' COLLATE latin1_swedish_ci);
-> 0
mysql> SELECT COERCIBILITY(USER());
-> 3
mysql> SELECT COERCIBILITY('abc');
-> 4
The return values have the meanings shown in the following table.
Lower values have higher precedence.
*Coercibility**Meaning* *Example*
`0' Explicit Value with `COLLATE' clause
collation
`1' No Concatenation of strings with different
collation collations
`2' Implicit Column value
collation
`3' System `USER()' return value
constant
`4' Coercible Literal string
`5' Ignorable `NULL' or an expression derived from `NULL'
Before MySQL 5.0.3, the return values are shown as follows, and
functions such as `USER()' have a coercibility of 2:
*Coercibility**Meaning* *Example*
`0' Explicit Value with `COLLATE' clause
collation
`1' No Concatenation of strings with different
collation collations
`2' Implicit Column value, stored routine parameter or
collation local variable
`3' Coercible Literal string
* `COLLATION(STR)'
Returns the collation of the string argument.
mysql> SELECT COLLATION('abc');
-> 'latin1_swedish_ci'
mysql> SELECT COLLATION(_utf8'abc');
-> 'utf8_general_ci'
* `CONNECTION_ID()'
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. As of MySQL
5.0.10, 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 value of `CURRENT_USER()' can differ from the value of
`USER()'.
mysql> SELECT USER();
-> 'davida@localhost'
mysql> SELECT * FROM mysql.user;
ERROR 1044: Access denied for user ''@'localhost' to
database 'mysql'
mysql> SELECT CURRENT_USER();
-> '@localhost'
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'
* `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:
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM TBL_NAME
-> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();
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:
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM ... ;
mysql> SET @rows = FOUND_ROWS();
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 recent_ `INSERT' 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.
For example:
mysql> USE test;
Database changed
mysql> CREATE TABLE t (
-> id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> name VARCHAR(10) NOT NULL
-> );
Query OK, 0 rows affected (0.09 sec)
mysql> INSERT INTO t VALUES (NULL, 'Bob');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM t;
+----+------+
| id | name |
+----+------+
| 1 | Bob |
+----+------+
1 row in set (0.01 sec)
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO t VALUES
-> (NULL, 'Mary'), (NULL, 'Jane'), (NULL, 'Lisa');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t;
+----+------+
| id | name |
+----+------+
| 1 | Bob |
| 2 | Mary |
| 3 | Jane |
| 4 | Lisa |
+----+------+
4 rows in set (0.01 sec)
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 2 |
+------------------+
1 row in set (0.00 sec)
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:
1. Create a table to hold the sequence counter and initialize it:
mysql> CREATE TABLE sequence (id INT NOT NULL);
mysql> INSERT INTO sequence VALUES (0);
2. Use the table to generate sequence numbers like this:
mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
mysql> SELECT LAST_INSERT_ID();
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 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.
mysql> INSERT INTO t VALUES(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)
mysql> DELETE FROM t WHERE i IN(1,2);
Query OK, 2 rows affected (0.00 sec)
mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
`ROW_COUNT()' was added in MySQL 5.0.1.
* `SCHEMA()'
This function is a synonym for `DATABASE()'. It was added in MySQL
5.0.2.
* `SESSION_USER()'
`SESSION_USER()' is a synonym for `USER()'.
* `SYSTEM_USER()'
`SYSTEM_USER()' is a synonym for `USER()'.
* `USER()'
Returns the current MySQL username and hostname as a string in the
`utf8' character set.
mysql> SELECT USER();
-> 'davida@localhost'
The value indicates the username you specified when connecting to
the server, and the client host from which you connected. The
value can be different from that of `CURRENT_USER()'.
You can extract only the username part like this:
mysql> SELECT SUBSTRING_INDEX(USER(),'@',1);
-> 'davida'
* `VERSION()'
Returns a string that indicates the MySQL server version. The
string uses the `utf8' character set.
mysql> SELECT VERSION();
-> '5.0.19-standard'
Note that if your version string ends with `-log' this means that
logging is enabled.
Info Catalog
(mysql.info) encryption-functions
(mysql.info) other-functions
(mysql.info) miscellaneous-functions
automatically generated byinfo2html