(mysql.info.gz) Information functions
Info Catalog
(mysql.info.gz) Encryption functions
(mysql.info.gz) Other Functions
(mysql.info.gz) Miscellaneous functions
12.8.3 Information Functions
----------------------------
`BENCHMARK(COUNT,EXPR)'
The `BENCHMARK()' function executes the expression EXPR repeatedly
COUNT times. It may be used to time how fast 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'
`CHARSET()' was added in MySQL 4.1.0.
`COERCIBILITY(STR)'
Returns the collation coercibility value of the string argument.
mysql> SELECT COERCIBILITY('abc' COLLATE latin1_swedish_ci);
-> 0
mysql> SELECT COERCIBILITY('abc');
-> 3
mysql> SELECT COERCIBILITY(USER());
-> 2
The return values have the following meanings:
*Coercibility* *Meaning*
`0' Explicit collation
`1' No collation
`2' Implicit collation
`3' Coercible
Lower values have higher precedence.
`COERCIBILITY()' was added in MySQL 4.1.1.
`COLLATION(STR)'
Returns the collation for the character set of the string argument.
mysql> SELECT COLLATION('abc');
-> 'latin1_swedish_ci'
mysql> SELECT COLLATION(_utf8'abc');
-> 'utf8_general_ci'
`COLLATION()' was added in MySQL 4.1.0.
`CONNECTION_ID()'
Returns the connection ID (thread ID) for the connection. Every
connection has its own unique ID.
mysql> SELECT CONNECTION_ID();
-> 23786
`CONNECTION_ID()' was added in MySQL 3.23.14.
`CURRENT_USER()'
Returns the username and hostname combination that the current
session was authenticated as. This value corresponds to the MySQL
account that determines your access privileges. It can be
different 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'.
`CURRENT_USER()' was added in MySQL 4.0.6.
`DATABASE()'
Returns the default (current) database name.
mysql> SELECT DATABASE();
-> 'test'
If there is no default database, `DATABASE()' returns `NULL' as of
MySQL 4.1.1, and the empty string before that.
`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 get this row count, include a `SQL_CALC_FOUND_ROWS'
option in the `SELECT' statement, 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' will return 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.)
Note that 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' queries 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'.
`SQL_CALC_FOUND_ROWS' and `FOUND_ROWS()' are available starting at
MySQL 4.0.0.
`LAST_INSERT_ID()'
`LAST_INSERT_ID(EXPR)'
Returns the last automatically generated value that was inserted
into an `AUTO_INCREMENT' column.
mysql> SELECT LAST_INSERT_ID();
-> 195
The last ID that was generated is maintained in the server on a
per-connection basis. This means the value the function returns to
a given client is the most recent `AUTO_INCREMENT' value generated
by that client. The value cannot be affected by other clients,
even if they generate `AUTO_INCREMENT' values of their own. This
behavior ensures that you can retrieve your 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 update the
`AUTO_INCREMENT' column of a row with a non-magic value (that is,
a value that is not `NULL' and not `0').
If you insert many rows at the same time with an insert statement,
`LAST_INSERT_ID()' returns the value for the first inserted row.
The reason for this is to make it possible to easily reproduce the
same `INSERT' statement against some other server.
If you use `INSERT IGNORE' and the record is ignored, the
`AUTO_INCREMENT' counter still is incremented and
`LAST_INSERT_ID()' returns the new value.
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:
* 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);
* 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. `mysql_insert_id()' 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.
`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.
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 than that of `CURRENT_USER()'.
Prior to MySQL 3.22.11, the function value does not include the
client hostname. You can extract just the username part,
regardless of whether the value includes a hostname part, like
this:
mysql> SELECT SUBSTRING_INDEX(USER(),'@',1);
-> 'davida'
As of MySQL 4.1, `USER()' returns a value in the `utf8' character
set, so you should also make sure that the `'@'' string literal is
interpreted in that character set:
mysql> SELECT SUBSTRING_INDEX(USER(),_utf8'@',1);
-> 'davida'
`VERSION()'
Returns a string that indicates the MySQL server version.
mysql> SELECT VERSION();
-> '4.1.3-beta-log'
Note that if your version string ends with `-log' this means that
logging is enabled.
Info Catalog
(mysql.info.gz) Encryption functions
(mysql.info.gz) Other Functions
(mysql.info.gz) Miscellaneous functions
automatically generated byinfo2html