MySQL 5.0 Reference Manual  /  Functions and Operators  /  Information Functions

12.13 Information Functions

Table 12.17 Information Functions

BENCHMARK() Repeatedly execute an expression
CHARSET() Return the character set of the argument
COERCIBILITY() Return the collation coercibility value of the string argument
COLLATION() Return the collation of the string argument
CONNECTION_ID() Return the connection ID (thread ID) for the connection
CURRENT_USER(), CURRENT_USER The authenticated user name and host name
DATABASE() Return the default (current) database name
FOUND_ROWS() For a SELECT with a LIMIT clause, the number of rows that would be returned were there no LIMIT clause
LAST_INSERT_ID() Value of the AUTOINCREMENT column for the last INSERT
ROW_COUNT() The number of rows updated
SCHEMA() Synonym for DATABASE()
SESSION_USER() Synonym for USER()
SYSTEM_USER() Synonym for USER()
USER() The user name and host name provided by the client
VERSION() Return a string that indicates the MySQL server version

  • 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.

    BENCHMARK() is intended for measuring the runtime performance of scalar expressions, which has some significant implications for the way that you use it and interpret the results:

    • Only scalar expressions can be used. Although the expression can be a subquery, it must return a single column and at most a single row. For example, BENCHMARK(10, (SELECT * FROM t)) will fail if the table t has more than one column or more than one row.

    • Executing a SELECT expr statement N times differs from executing SELECT BENCHMARK(N, expr) in terms of the amount of overhead involved. The two have very different execution profiles and you should not expect them to take the same amount of time. The former involves the parser, optimizer, table locking, and runtime evaluation N times each. The latter involves only runtime evaluation N times, and all the other components just once. Memory structures already allocated are reused, and runtime optimizations such as local caching of results already evaluated for aggregate functions can alter the results. Use of BENCHMARK() thus measures performance of the runtime component by giving more weight to that component and removing the noise introduced by the network, parser, optimizer, and so forth.

  • 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'

    Returns the collation coercibility value of the string argument.

    mysql> SELECT COERCIBILITY('abc' COLLATE latin1_swedish_ci);
            -> 0
            -> 3
    mysql> SELECT COERCIBILITY('abc');
            -> 4

    The return values have the meanings shown in the following table. Lower values have higher precedence.

    0Explicit collationValue with COLLATE clause
    1No collationConcatenation of strings with different collations
    2Implicit collationColumn value
    3System constantUSER() return value
    4CoercibleLiteral string
    5IgnorableNULL 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:

    0Explicit collationValue with COLLATE clause
    1No collationConcatenation of strings with different collations
    2Implicit collationColumn value, stored routine parameter or local variable
    3CoercibleLiteral 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'

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

    The value returned by CONNECTION_ID() is the same type of value as displayed in the Id column of SHOW PROCESSLIST output.

            -> 23786

    Returns the user name and host name combination for the MySQL account that the server used to authenticate the current client. This account determines your access privileges. 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'
            -> '@localhost'

    The example illustrates that although the client specified a user name 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 user name part of the CURRENT_USER() value). One way this might occur is that there is no account listed in the grant tables for davida.

    Within a stored program or view, CURRENT_USER() returns the account for the user who defined the object (as given by its DEFINER value) unless defined with the SQL SECURITY INVOKER characteristic. In the latter case, CURRENT_USER() returns the object's invoker. This applies to stored programs as of MySQL 5.0.10 and to views as of MySQL 5.0.24. (For older versions, CURRENT_USER() returns the account for the object's invoker.)

    Triggers and events have no option to define the SQL SECURITY characteristic, so for these objects, CURRENT_USER() returns the account for the user who defined the object. To return the invoker, use USER() or SESSION_USER().


    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'

    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.

    In the absence of the SQL_CALC_FOUND_ROWS option in the most recent successful SELECT statement, FOUND_ROWS() returns the number of rows in the result set returned by that statement. If the statement includes a LIMIT clause, FOUND_ROWS() returns the number of rows up to the limit. For example, FOUND_ROWS() returns 10 or 60, respectively, if the statement includes LIMIT 10 or LIMIT 50, 10.

    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> 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() enables 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.

    Beyond the cases described here, the behavior of FOUND_ROWS() is undefined (for example, its value following a SELECT statement that fails with an error).


    FOUND_ROWS() is not replicated reliably, and should not be used with databases that are to be replicated.


    LAST_INSERT_ID() (with no argument) returns a BIGINT (64-bit) value representing the first automatically generated value that was set for an AUTO_INCREMENT column by the most recently executed INSERT statement to affect such a column. For example, after inserting a row that generates an AUTO_INCREMENT value, you can get the value like this:

            -> 195

    if a table contains an AUTO_INCREMENT column and INSERT ... ON DUPLICATE KEY UPDATE updates (rather than inserts) a row, the value of LAST_INSERT_ID() is not meaningful. For a workaround, see Section, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”.

    The currently executing statement does not affect the value of LAST_INSERT_ID(). Suppose that you generate an AUTO_INCREMENT value with one statement, and then refer to LAST_INSERT_ID() in a multiple-row INSERT statement that inserts rows into a table with its own AUTO_INCREMENT column. The value of LAST_INSERT_ID() will remain stable in the second statement; its value for the second and later rows is not affected by the earlier row insertions. (However, if you mix references to LAST_INSERT_ID() and LAST_INSERT_ID(expr), the effect is undefined.)

    If the previous statement returned an error, the value of LAST_INSERT_ID() is undefined. For transactional tables, if the statement is rolled back due to an error, the value of LAST_INSERT_ID() is left undefined. For manual ROLLBACK, the value of LAST_INSERT_ID() is not restored to that before the transaction; it remains as it was at the point of the ROLLBACK.

    Within the body of a stored routine (procedure or function) or a trigger, the value of LAST_INSERT_ID() changes the same way as for statements executed outside the body of these kinds of objects. The effect of a stored routine or trigger upon the value of LAST_INSERT_ID() that is seen by following statements depends on the kind of routine:

    • If a stored procedure executes statements that change the value of LAST_INSERT_ID(), the changed value is seen by statements that follow the procedure call.

    • For stored functions and triggers that change the value, the value is restored when the function or trigger ends, so following statements do not see a changed value. (Before MySQL 5.0.12, the value is not restored and following statements do see a changed value.)

    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).


    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 (
        ->   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)
    | LAST_INSERT_ID() |
    |                1 |
    1 row in set (0.00 sec)
        -> (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)
    | 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 Section, “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() returns the number of rows changed, deleted, or inserted by the last statement if it was an UPDATE, DELETE, or INSERT. For other statements, the value may not be meaningful.

    For UPDATE statements, the affected-rows value by default is the number of rows actually changed. If you specify the CLIENT_FOUND_ROWS flag to mysql_real_connect() when connecting to mysqld, the affected-rows value is the number of rows found; that is, matched by the WHERE clause.

    For REPLACE statements, the affected-rows value is 2 if the new row replaced an old row, because in this case, one row was inserted after the duplicate was deleted.

    For INSERT ... ON DUPLICATE KEY UPDATE statements, the affected-rows value is 1 if the row is inserted as a new row and 2 if an existing row is updated.

    The ROW_COUNT() value is similar to the value from the mysql_affected_rows() C API function and the row count that the mysql client displays following statement execution.

    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.


    ROW_COUNT() is not replicated reliably.

  • SCHEMA()

    This function is a synonym for DATABASE(). It was added in MySQL 5.0.2.


    SESSION_USER() is a synonym for USER().


    SYSTEM_USER() is a synonym for USER().

  • USER()

    Returns the current MySQL user name and host name as a string in the utf8 character set.

    mysql> SELECT USER();
            -> 'davida@localhost'

    The value indicates the user name 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().


    Returns a string that indicates the MySQL server version. The string uses the utf8 character set. The value might have a suffix in addition to the version number. See the description of the version system variable in Section 5.1.4, “Server System Variables”.

    mysql> SELECT VERSION();
            -> '5.0.96-standard'

User Comments
  Posted by Tom Andresen on June 28, 2005
I was issuing my query "select sql_calc_found_rows statement1 union statement2 union statement3 limit whatever" and getting a count from found_rows() that was incorrect. It was running as if I had run the query as "union all" because it was returning duplicates. After rereading the union syntax page I reissued the query with parens around each individual statement. "(select sql_calc_found_rows statement1) union (statement2) union (statement3) limit whatever" This yielded the expected result.
  Posted by Chr. Ludwig on September 8, 2005
if you want to perform a complex benchmark, you need to put doble quotes around your expression:

SELECT BENCHMARK(1000000, "ENCODE('hello','goodbye')");

And remember to but a SELECT in front of BENCHMARK.
  Posted by H Y on January 2, 2006
The above reads "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 word "may" seems to be an understatement. Everytime I run a SELECT with LIMIT, FOUND_ROWS() returns whatever number I put after LIMIT. E.g. after


FOUND_ROWS() will return 50.

But if I do

SELECT * FROM table LIMIT 10, 10,

FOUND_ROWS() will return 20 though. :)

This is annoying, since I need this information from a 20,000-row table fulltext search, and a 0,008 second search then suddenly takes about 20 seconds... Haven't found a workaround yet...
  Posted by Danny Swett on March 11, 2006
This is the desired result though, as:


FOUND_ROWS() will return 50 cause it actually found 50 entries in the table before it stopped and returned the result

But if I do

SELECT * FROM table LIMIT 10, 10,

FOUND_ROWS() will return 20 cause it had to find the first 10 you skipped over, then the second 10 it returned to you, so it found 20 entries before stopping and giving you the result

  Posted by Wade Bowmer on May 14, 2006
Be aware that using SQL_CALC_FOUND_ROWS and FOUND_ROWS() disables ORDER BY ... LIMIT optimizations (see bugs and Until it's fixed, you should run your own benchmarks with and without it.

  Posted by Paul Lautman on May 16, 2006
The text says "include a SQL_CALC_FOUND_ROWS option in the SELECT statement". What it doesn't say is that SQL_CALC_FOUND_ROWS must be at the front of any fields in the SELECT statement!
  Posted by Andrew Purdon on June 19, 2006
An alternative to the previously discussed issue:

SELECT * FROM table LIMIT 10, 10,
FOUND_ROWS() will return 20

If rows are counted from the client, the "expected" number of rows can be calcualted. This, of course, requires that the result set is passed to the client.
  Posted by Roman Okoyomov on October 1, 2006
about "LIMIT 10,10" issue...

manual says:
"... 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."

though that means 'select * from tableX limit 50' returns 50 as a result of 'Found_rows()' just because tableX has 50 records, and 'select * from tableX limit 10,10' will return also 50 and not 10 or 20 as a result!

and don't forget use SQL_CALC_FOUND_ROWS modifier.
  Posted by Jeffrey Binder on December 22, 2010
If you are using the Python MySQLdb module, bear in mind that inserting multiple rows with executemany() does NOT count as a single operation for the purpose of last_insert_id(). The value returned (assuming success) will be the ID created for the LAST row in the list.
  Posted by Lior Ben-Kereth on March 30, 2011
Using the LAST_INSERT_ID() to simulate a sequence as suggested above, will not work well when table is replicated, and access is randomly made for one of the replicated servers.
To overcome this, one can use this method:
1) Create a table with two columns: First is auto_incremented primary key. The second, is unique int.

`id` int(11) NOT NULL auto_increment,
`value` int(11) NOT NULL,
UNIQUE KEY `value` (`value`)

2) Then, use the replace statement to increment the id column. (The unique index on value makes sure that the single row is being deleted and new one is inserted)
REPLACE INTO `sequence` set value=1

3) Use LAST_INSERT_ID() to get the newly inserted Id.

Now, using the auto_increment_offset and the auto_increment_increment server varaiables listed here,, you can make sure that auto increment values on the replicated machines will not overlap (i.e odd, and even), and the sequence will work smoothly.

Lior Ben-Kereth

  Posted by Hector Guilarte on April 10, 2012
When using SELECT LAST_INSERT_ID() keep in mind that it does not need the FROM <Table> part of the query.

I know this might sound obvious, but I had huge performance problems on a production database just because of putting the "FROM".

Here is the blog post that helped me fix the issue on my code:

  Posted by Peter Bagnall on June 1, 2012
Despite the warning above "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" it seems that in actual fact you can. This was certainly correct in 5.0, but 5.5 appears to have removed this limitation.
  Posted by Kai Pfeiffer on May 18, 2013
I had the problem, that I retrieved a negative result like "-1" when I triggered a ROW_COUNT() after executing a prepared statement.

My mistake was, that I deallocated the statement before I executed ROW_COUNT().

Example 1:
1. EXECUTE statement
2. DEALLOCATE statement
doesn't work.

Example 2:
1. EXECUTE statement,
3. DEALLOCATE statement
works like expected

It might be trivial, but I searched the whole web for this solution and I found none.
  Posted by Piotr SaÅ‚aciak on November 18, 2013
If You would like to get current auto_increment value for certain table You can use this query. Just one note: it's insecure to let remote user to have access to information_schema, so try to execute this with some separated local user.

WHERE `table_schema` = 'DATABASE_NAME' AND `table_name` = 'TABLE_NAME';
Sign Up Login You must be logged in to post a comment.