MySQL :: MySQL 8.4 Reference Manual :: 14.15 Information Functions (original) (raw)

mysql> SELECT BENCHMARK(1000000,AES_ENCRYPT('hello','goodbye'));  
+---------------------------------------------------+  
| BENCHMARK(1000000,AES_ENCRYPT('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 executeBENCHMARK() 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: 

mysql> SELECT CHARSET('abc');  
        -> 'utf8mb3'  
mysql> SELECT CHARSET(CONVERT('abc' USING latin1));  
        -> 'latin1'  
mysql> SELECT CHARSET(USER());  
        -> 'utf8mb3'  
mysql> SELECT COERCIBILITY('abc' COLLATE utf8mb4_swedish_ci);  
        -> 0  
mysql> SELECT COERCIBILITY(USER());  
        -> 3  
mysql> SELECT COERCIBILITY('abc');  
        -> 4  
mysql> SELECT COERCIBILITY(1000);  
        -> 5

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

Coercibility Meaning Example
0 Explicit collation Value with COLLATE clause
1 No collation Concatenation of strings with different collations
2 Implicit collation Column value, stored routine parameter or local variable
3 System constant USER() return value
4 Coercible Literal string
5 Numeric Numeric or temporal value
6 Ignorable NULL or an expression derived fromNULL
For more information, seeSection 12.8.4, “Collation Coercibility in Expressions”. 
mysql> SELECT COLLATION('abc');  
        -> 'utf8mb4_0900_ai_ci'  
mysql> SELECT COLLATION(_utf8mb4'abc');  
        -> 'utf8mb4_0900_ai_ci'  
mysql> SELECT COLLATION(_latin1'abc');  
        -> 'latin1_swedish_ci'  
mysql> SELECT CONNECTION_ID();  
        -> 23786  
GRANT 'r1', 'r2' TO 'u1'@'localhost';  
SET DEFAULT ROLE ALL TO 'u1'@'localhost';

In sessions for u1, the initialCURRENT_ROLE() value names the default account roles. Using SET ROLE changes that: 

mysql> SELECT CURRENT_ROLE();  
+-------------------+  
| CURRENT_ROLE()    |  
+-------------------+  
| `r1`@`%`,`r2`@`%` |  
+-------------------+  
mysql> SET ROLE 'r1'; SELECT CURRENT_ROLE();  
+----------------+  
| CURRENT_ROLE() |  
+----------------+  
| `r1`@`%`       |  
+----------------+  
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 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 theCURRENT_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 itsDEFINER value) unless defined with theSQL SECURITY INVOKER characteristic. In the latter case, CURRENT_USER() returns 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() orSESSION_USER().
The following statements support use of theCURRENT_USER() function to take the place of the name of (and, possibly, a host for) an affected user or a definer; in such cases,CURRENT_USER() is expanded where and as needed: 

CREATE TABLE t (c VARCHAR(288) DEFAULT (CURRENT_USER()));  
mysql> SELECT DATABASE();  
        -> 'test'

If there is no default database,DATABASE() returnsNULL.

SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10;  
SELECT FOUND_ROWS();

Use these queries instead: 

SELECT * FROM tbl_name WHERE id > 100 LIMIT 10;  
SELECT COUNT(*) FROM tbl_name WHERE id > 100;

COUNT(*) is subject to certain optimizations.SQL_CALC_FOUND_ROWS causes some optimizations to be disabled.
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 an SQL_CALC_FOUND_ROWS option in theSELECT 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 firstSELECT 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 successfulSELECT 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 throughFOUND_ROWS() is transient and not intended to be available past the statement following theSELECT 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 andFOUND_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. UsingFOUND_ROWS() enables you to determine how many other pages are needed for the rest of the result.
The use of SQL_CALC_FOUND_ROWS andFOUND_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 theUNION result as a whole.
The intent of SQL_CALC_FOUND_ROWS forUNION is that it should return the row count that would be returned without a globalLIMIT. The conditions for use ofSQL_CALC_FOUND_ROWS withUNION are: 

mysql> SELECT LAST_INSERT_ID();  
        -> 195

The currently executing statement does not affect the value ofLAST_INSERT_ID(). Suppose that you generate an AUTO_INCREMENT value with one statement, and then refer toLAST_INSERT_ID() in a multiple-row INSERT statement that inserts rows into a table with its ownAUTO_INCREMENT column. The value ofLAST_INSERT_ID() remains stable in the second statement; its value for the second and later rows is not affected by the earlier row insertions. (You should be aware that, if you mix references toLAST_INSERT_ID() andLAST_INSERT_ID(expr), the effect is undefined.)
If the previous statement returned an error, the value ofLAST_INSERT_ID() is undefined. For transactional tables, if the statement is rolled back due to an error, the value ofLAST_INSERT_ID() is left undefined. For manualROLLBACK, the value of LAST_INSERT_ID() is not restored to that before the transaction; it remains as it was at the point of theROLLBACK.
Within the body of a stored routine (procedure or function) or a trigger, the value ofLAST_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 ofLAST_INSERT_ID() that is seen by following statements depends on the kind of routine: 

mysql> USE test;  
mysql> CREATE TABLE t (  
       id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,  
       name VARCHAR(10) NOT NULL  
       );  
mysql> INSERT INTO t VALUES (NULL, 'Bob');  
mysql> SELECT * FROM t;  
+----+------+  
| id | name |  
+----+------+  
|  1 | Bob  |  
+----+------+  
mysql> SELECT LAST_INSERT_ID();  
+------------------+  
| LAST_INSERT_ID() |  
+------------------+  
|                1 |  
+------------------+  
mysql> INSERT INTO t VALUES  
       (NULL, 'Mary'), (NULL, 'Jane'), (NULL, 'Lisa');  
mysql> SELECT * FROM t;  
+----+------+  
| id | name |  
+----+------+  
|  1 | Bob  |  
|  2 | Mary |  
|  3 | Jane |  
|  4 | Lisa |  
+----+------+  
mysql> SELECT LAST_INSERT_ID();  
+------------------+  
| LAST_INSERT_ID() |  
+------------------+  
|                2 |  
+------------------+

Although the second INSERT statement inserted three new rows into t, the ID generated for the first of these rows was2, and it is this value that is returned byLAST_INSERT_ID() for the following SELECT statement.
If you use INSERT IGNORE and the row is ignored, theLAST_INSERT_ID() remains unchanged from the current value (or 0 is returned if the connection has not yet performed a successfulINSERT) and, for non-transactional tables, the AUTO_INCREMENT counter is not incremented. For InnoDB tables, theAUTO_INCREMENT counter is incremented ifinnodb_autoinc_lock_mode is set to 1 or 2, as demonstrated in the following example: 

mysql> USE test;  
mysql> SELECT @@innodb_autoinc_lock_mode;  
+----------------------------+  
| @@innodb_autoinc_lock_mode |  
+----------------------------+  
|                          1 |  
+----------------------------+  
mysql> CREATE TABLE `t` (  
       `id` INT(11) NOT NULL AUTO_INCREMENT,  
       `val` INT(11) DEFAULT NULL,  
       PRIMARY KEY (`id`),  
       UNIQUE KEY `i1` (`val`)  
       ) ENGINE=InnoDB;  
# Insert two rows  
mysql> INSERT INTO t (val) VALUES (1),(2);  
# With auto_increment_offset=1, the inserted rows  
# result in an AUTO_INCREMENT value of 3  
mysql> SHOW CREATE TABLE t\G  
*************************** 1. row ***************************  
       Table: t  
Create Table: CREATE TABLE `t` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `val` int(11) DEFAULT NULL,  
  PRIMARY KEY (`id`),  
  UNIQUE KEY `i1` (`val`)  
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci  
# LAST_INSERT_ID() returns the first automatically generated  
# value that is successfully inserted for the AUTO_INCREMENT column  
mysql> SELECT LAST_INSERT_ID();  
+------------------+  
| LAST_INSERT_ID() |  
+------------------+  
|                1 |  
+------------------+  
# The attempted insertion of duplicate rows fail but errors are ignored  
mysql> INSERT IGNORE INTO t (val) VALUES (1),(2);  
Query OK, 0 rows affected (0.00 sec)  
Records: 2  Duplicates: 2  Warnings: 0  
# With innodb_autoinc_lock_mode=1, the AUTO_INCREMENT counter  
# is incremented for the ignored rows  
mysql> SHOW CREATE TABLE t\G  
*************************** 1. row ***************************  
       Table: t  
Create Table: CREATE TABLE `t` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `val` int(11) DEFAULT NULL,  
  PRIMARY KEY (`id`),  
  UNIQUE KEY `i1` (`val`)  
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci  
# The LAST_INSERT_ID is unchanged because the previous insert was unsuccessful  
mysql> SELECT LAST_INSERT_ID();  
+------------------+  
| LAST_INSERT_ID() |  
+------------------+  
|                1 |  
+------------------+

For more information, seeSection 17.6.1.6, “AUTO_INCREMENT Handling in InnoDB”.
If expr is given as an argument toLAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned byLAST_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);
  1. 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](update.html "15.2.17 UPDATE Statement") statement increments the sequence counter and causes the next call to [LAST\_INSERT\_ID()](information-functions.html#function%5Flast-insert-id) to return the updated value. The[SELECT](select.html "15.2.13 SELECT Statement") statement retrieves that value. The[mysql\_insert\_id()](/doc/c-api/8.4/en/mysql-insert-id.html) C API function can also be used to get the value. See[mysql\_insert\_id()](/doc/c-api/8.4/en/mysql-insert-id.html).

You can generate sequences without callingLAST_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 theSELECT statement (ormysql_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 andUPDATE statements, so you cannot use the C API function to retrieve the value forLAST_INSERT_ID(expr) after executing other SQL statements likeSELECT orSET.

mysql> SELECT ROLES_GRAPHML();  
+---------------------------------------------------+  
| ROLES_GRAPHML()                                   |  
+---------------------------------------------------+  
| <?xml version="1.0" encoding="UTF-8"?><graphml /> |  
+---------------------------------------------------+  
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)

Important
ROW_COUNT() is not replicated reliably using statement-based replication. This function is automatically replicated using row-based replication.

CREATE TABLE t (c VARCHAR(288) DEFAULT (SESSION_USER()));  
CREATE TABLE t (c VARCHAR(288) DEFAULT (SYSTEM_USER()));  
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 ofCURRENT_USER().
This function can be used for the default value of aVARCHAR orTEXT column, as shown in the following CREATE TABLE statement: 

CREATE TABLE t (c VARCHAR(288) DEFAULT (USER()));  
mysql> SELECT VERSION();  
        -> '8.4.5-standard'