Movatterモバイル変換

Table 14.20 Information Functions

• BENCHMARK(count,expr)

  TheBENCHMARK() function executes the expressionexpr repeatedlycount times. It may be used to time how quickly MySQL processes the expression. The result value is0, orNULL for inappropriate arguments such as aNULL or negative repeat count.

  The intended use is from within themysql client, which reports query execution times:

  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:

  • 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)) fails if the tablet has more than one column or more than one row.

  • Executing aSELECTexpr statementN times differs from executingSELECT 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 evaluationN times each. The latter involves only runtime evaluationN 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 ofBENCHMARK() 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, orNULL if the argument isNULL.

  mysql> SELECT CHARSET('abc');        -> 'utf8mb3'mysql> SELECT CHARSET(CONVERT('abc' USING latin1));        -> 'latin1'mysql> SELECT CHARSET(USER());        -> 'utf8mb3'

• COERCIBILITY(str)

  Returns the collation coercibility value of the string argument.

  mysql> SELECT COERCIBILITY('abc' COLLATE utf8mb4_swedish_ci);        -> 0mysql> SELECT COERCIBILITY(USER());        -> 3mysql> SELECT COERCIBILITY('abc');        -> 4mysql> 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 withCOLLATE 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”.

• COLLATION(str)

  Returns the collation of the string argument.

  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'

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

  The value returned byCONNECTION_ID() is the same type of value as displayed in theID column of the Information SchemaPROCESSLIST table, theId column ofSHOW PROCESSLIST output, and thePROCESSLIST_ID column of the Performance Schemathreads table.

  mysql> SELECT CONNECTION_ID();        -> 23786

  Warning

  Changing the session value of thepseudo_thread_id system variable changes the value returned by theCONNECTION_ID() function.

• CURRENT_ROLE()

  Returns autf8mb3 string containing the current active roles for the current session, separated by commas, orNONE if there are none. The value reflects the setting of thesql_quote_show_create system variable.

  Suppose that an account is granted roles as follows:

  GRANT 'r1', 'r2' TO 'u1'@'localhost';SET DEFAULT ROLE ALL TO 'u1'@'localhost';

  In sessions foru1, the initialCURRENT_ROLE() value names the default account roles. UsingSET ROLE changes that:

  mysql> SELECT CURRENT_ROLE();+-------------------+| CURRENT_ROLE()    |+-------------------+| `r1`@`%`,`r2`@`%` |+-------------------+mysql> SET ROLE 'r1'; SELECT CURRENT_ROLE();+----------------+| CURRENT_ROLE() |+----------------+| `r1`@`%`       |+----------------+

• CURRENT_USER,CURRENT_USER()

  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 theutf8mb3 character set.

  The value ofCURRENT_USER() can differ from the value ofUSER().

  mysql> SELECT USER();        -> 'davida@localhost'mysql> SELECT * FROM mysql.user;ERROR 1044: Access denied for user ''@'localhost' todatabase 'mysql'mysql> SELECT CURRENT_USER();        -> '@localhost'

  The example illustrates that although the client specified a user name ofdavida (as indicated by the value of theUSER() 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 fordavida.

  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 theSQL SECURITY characteristic, so for these objects,CURRENT_USER() returns the account for the user who defined the object. To return the invoker, useUSER() 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:

  • DROP USER

  • RENAME USER

  • GRANT

  • REVOKE

  • CREATE FUNCTION

  • CREATE PROCEDURE

  • CREATE TRIGGER

  • CREATE EVENT

  • CREATE VIEW

  • ALTER EVENT

  • ALTER VIEW

  • SET PASSWORD

  For information about the implications that this expansion ofCURRENT_USER() has for replication, seeSection 19.5.1.8, “Replication of CURRENT_USER()”.

  Beginning with MySQL 8.0.34, this function can be used for the default value of aVARCHAR orTEXT column, as shown in the followingCREATE TABLE statement:

  CREATE TABLE t (c VARCHAR(288) DEFAULT (CURRENT_USER()));

• DATABASE()

  Returns the default (current) database name as a string in theutf8mb3 character set. If there is no default database,DATABASE() returnsNULL. 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() returnsNULL.

• FOUND_ROWS()

  Note

  TheSQL_CALC_FOUND_ROWS query modifier and accompanyingFOUND_ROWS() function are deprecated as of MySQL 8.0.17; expect them to be removed in a future version of MySQL. As a replacement, considering executing your query withLIMIT, and then a second query withCOUNT(*) and withoutLIMIT to determine whether there are additional rows. For example, instead of these queries:

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

  Use these queries instead:

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

  COUNT(*) is subject to certain optimizations.SQL_CALC_FOUND_ROWS causes some optimizations to be disabled.

  ASELECT statement may include aLIMIT 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 theLIMIT, but without running the statement again. To obtain this row count, include anSQL_CALC_FOUND_ROWS option in theSELECT statement, and then invokeFOUND_ROWS() afterward:

  mysql> SELECT SQL_CALC_FOUND_ROWS * FROMtbl_name    -> WHERE id > 100 LIMIT 10;mysql> SELECT FOUND_ROWS();

  The secondSELECT returns a number indicating how many rows the firstSELECT would have returned had it been written without theLIMIT clause.

  In the absence of theSQL_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 aLIMIT clause,FOUND_ROWS() returns the number of rows up to the limit. For example,FOUND_ROWS() returns 10 or 60, respectively, if the statement includesLIMIT 10 orLIMIT 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 usingSELECT 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 withoutLIMIT, 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 ofSQL_CALC_FOUND_ROWS andFOUND_ROWS() is more complex forUNION statements than for simpleSELECT statements, becauseLIMIT may occur at multiple places in aUNION. It may be applied to individualSELECT statements in theUNION, or global to theUNION result as a whole.

  The intent ofSQL_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:

  • TheSQL_CALC_FOUND_ROWS keyword must appear in the firstSELECT of theUNION.

  • The value ofFOUND_ROWS() is exact only ifUNION ALL is used. IfUNION withoutALL is used, duplicate removal occurs and the value ofFOUND_ROWS() is only approximate.

  • If noLIMIT is present in theUNION,SQL_CALC_FOUND_ROWS is ignored and returns the number of rows in the temporary table that is created to process theUNION.

  Beyond the cases described here, the behavior ofFOUND_ROWS() is undefined (for example, its value following aSELECT statement that fails with an error).

  Important

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

• ICU_VERSION()

  The version of the International Components for Unicode (ICU) library used to support regular expression operations (seeSection 14.8.2, “Regular Expressions”). This function is primarily intended for use in test cases.

• LAST_INSERT_ID(),LAST_INSERT_ID(expr)

  With no argument,LAST_INSERT_ID() returns aBIGINT UNSIGNED (64-bit) value representing the first automatically generated value successfully inserted for anAUTO_INCREMENT column as a result of the most recently executedINSERT statement. The value ofLAST_INSERT_ID() remains unchanged if no rows are successfully inserted.

  With an argument,LAST_INSERT_ID() returns an unsigned integer, orNULL if the argument isNULL.

  For example, after inserting a row that generates anAUTO_INCREMENT value, you can get the value like this:

  mysql> SELECT LAST_INSERT_ID();        -> 195

  The currently executing statement does not affect the value ofLAST_INSERT_ID(). Suppose that you generate anAUTO_INCREMENT value with one statement, and then refer toLAST_INSERT_ID() in a multiple-rowINSERT 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 ofLAST_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:

  • If a stored procedure executes statements that change the value ofLAST_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 statements coming after it do not see a changed value.

  The ID that was generated is maintained in the server on aper-connection basis. This means that the value returned by the function to a given client is the firstAUTO_INCREMENT value generated for most recent statement affecting anAUTO_INCREMENT columnby that client. This value cannot be affected by other clients, even if they generateAUTO_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 ofLAST_INSERT_ID() is not changed if you set theAUTO_INCREMENT column of a row to a non-“magic” value (that is, a value that is notNULL and not0).

  Important

  If you insert multiple rows using a singleINSERT statement,LAST_INSERT_ID() returns the value generated for thefirst inserted rowonly. The reason for this is to make it possible to reproduce easily the sameINSERT statement against some other server.

  For example:

  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 secondINSERT statement inserted three new rows intot, the ID generated for the first of these rows was2, and it is this value that is returned byLAST_INSERT_ID() for the followingSELECT statement.

  If you useINSERT 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, theAUTO_INCREMENT counter is not incremented. ForInnoDB tables, theAUTO_INCREMENT counter is incremented ifinnodb_autoinc_lock_mode is set to1 or2, 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 rowsmysql> INSERT INTO t (val) VALUES (1),(2);# With auto_increment_offset=1, the inserted rows# result in an AUTO_INCREMENT value of 3mysql> SHOW CREATE TABLE t\G*************************** 1. row ***************************       Table: tCreate 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 columnmysql> SELECT LAST_INSERT_ID();+------------------+| LAST_INSERT_ID() |+------------------+|                1 |+------------------+# The attempted insertion of duplicate rows fail but errors are ignoredmysql> 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 rowsmysql> SHOW CREATE TABLE t\G*************************** 1. row ***************************       Table: tCreate 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 unsuccessfulmysql> SELECT LAST_INSERT_ID();+------------------+| LAST_INSERT_ID() |+------------------+|                1 |+------------------+

  For more information, seeSection 17.6.1.6, “AUTO_INCREMENT Handling in InnoDB”.

  Ifexpr 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);

  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();

     TheUPDATE statement increments the sequence counter and causes the next call toLAST_INSERT_ID() to return the updated value. TheSELECT statement retrieves that value. Themysql_insert_id() C API function can also be used to get the value. Seemysql_insert_id().

  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 theUPDATE 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 thatmysql_insert_id() is only updated afterINSERT 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.

• ROLES_GRAPHML()

  Returns autf8mb3 string containing a GraphML document representing memory role subgraphs. TheROLE_ADMIN privilege (or the deprecatedSUPER privilege) is required to see content in the<graphml> element. Otherwise, the result shows only an empty element:

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

• ROW_COUNT()

  ROW_COUNT() returns a value as follows:

  • DDL statements: 0. This applies to statements such asCREATE TABLE orDROP TABLE.

  • DML statements other thanSELECT: The number of affected rows. This applies to statements such asUPDATE,INSERT, orDELETE (as before), but now also to statements such asALTER TABLE andLOAD DATA.

  • SELECT: -1 if the statement returns a result set, or the number of rows“affected” if it does not. For example, forSELECT * FROM t1,ROW_COUNT() returns -1. ForSELECT * FROM t1 INTO OUTFILE 'file_name',ROW_COUNT() returns the number of rows written to the file.

  • SIGNAL statements: 0.

  ForUPDATE statements, the affected-rows value by default is the number of rows actually changed. If you specify theCLIENT_FOUND_ROWS flag tomysql_real_connect() when connecting tomysqld, the affected-rows value is the number of rows“found”; that is, matched by theWHERE clause.

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

  ForINSERT ... ON DUPLICATE KEY UPDATE statements, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify theCLIENT_FOUND_ROWS flag, the affected-rows value is 1 (not 0) if an existing row is set to its current values.

  TheROW_COUNT() value is similar to the value from themysql_affected_rows() C API function and the row count that themysql 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: 0mysql> 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.

• SCHEMA()

  This function is a synonym forDATABASE().

• SESSION_USER()

  SESSION_USER() is a synonym forUSER().

  Beginning with MySQL 8.0.34, likeUSER(), this function can be used for the default value of aVARCHAR orTEXT column, as shown in the followingCREATE TABLE statement:

  CREATE TABLE t (c VARCHAR(288) DEFAULT (SESSION_USER()));

• SYSTEM_USER()

  SYSTEM_USER() is a synonym forUSER().

  Note

  TheSYSTEM_USER() function is distinct from theSYSTEM_USER privilege. The former returns the current MySQL account name. The latter distinguishes the system user and regular user account categories (seeSection 8.2.11, “Account Categories”).

  Beginning with MySQL 8.0.34, likeUSER(), this function can be used for the default value of aVARCHAR orTEXT column, as shown in the followingCREATE TABLE statement:

  CREATE TABLE t (c VARCHAR(288) DEFAULT (SYSTEM_USER()));

• USER()

  Returns the current MySQL user name and host name as a string in theutf8mb3 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 ofCURRENT_USER().

  Beginning with MySQL 8.0.34, this function can be used for the default value of aVARCHAR orTEXT column, as shown in the followingCREATE TABLE statement:

  CREATE TABLE t (c VARCHAR(288) DEFAULT (USER()));

• VERSION()

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

  This function is unsafe for statement-based replication. A warning is logged if you use this function whenbinlog_format is set toSTATEMENT.

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