当前位置: 澳门新濠3559 > 数据库 > 正文

MySQL Information Functions

时间:2019-10-06 19:52来源:数据库
Table 12.18 Information Functions MySQL Information Functions,mysqlfunctions Table 12.18 Information Functions Name Description BENCHMARK() Repeatedly execute an expression CHARSET() Return the character set of the argument COERCIBILI

Table 12.18 Information Functions

MySQL Information Functions,mysqlfunctions

Table 12.18 Information Functions

Name Description
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(Nexpr) 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'
    
  •  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 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 Ignorable NULL or an expression derived from NULL
  •  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.

    The value returned by CONNECTION_ID() is the same type of value as displayed in the ID column of the INFORMATION_SCHEMA.PROCESSLIST table, the Id column of SHOW PROCESSLIST output, and the PROCESSLIST_ID column of the Performance Schema threads table.

    mysql> SELECT CONNECTION_ID();
            -> 23786
    
  •  CURRENT_USERCURRENT_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 the utf8 character set.

    The value of CURRENT_USER() can differ from the value of USER().

    mysql> SELECT USER();
            -> '[email protected]'
    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 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.

    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()MySQL Information Functions。.

    The following statements support use of the CURRENT_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 of CURRENT_USER() has for replication in different releases of MySQL 5.5, see Section 17.4.1.8, “Replication of CURRENT_USER()”.

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

    If there is no default database, DATABASE() returns NULL.

  •  FOUND_ROWS()

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

    Important

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

  •  LAST_INSERT_ID()LAST_INSERT_ID(expr)

    With no argument, LAST_INSERT_ID() returns a 64-bit value representing the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement. The value has a type of BIGINT UNSIGNED as of MySQL 5.5.29, BIGINT (signed) before that. The value of LAST_INSERT_ID() remains unchanged if no rows are successfully inserted.

    With an argument, LAST_INSERT_ID() returns an unsigned integer as of MySQL 5.5.29, a signed integer before that.

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

    mysql> SELECT LAST_INSERT_ID();
            -> 195
    

    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.

    Prior to MySQL 5.5.35, this function was not replicated correctly if replication filtering rules were in use. (Bug #17234370, Bug #69861)

    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 will not 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 anAUTO_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 INSERTstatement 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 LAST_INSERT_ID() remains unchanged from the current value (or 0 is returned if the connection has not yet performed a successful INSERT) and, for non-transactional tables, theAUTO_INCREMENT counter is not incremented. For InnoDB tables, the AUTO_INCREMENT counter is incremented if innodb_autoinc_lock_mode is set to 1 or 2, as demonstrated in the following example:

    mysql> USE test;
    Database changed
    
    mysql> SELECT @@innodb_autoinc_lock_mode;
     ---------------------------- 
    | @@innodb_autoinc_lock_mode |
     ---------------------------- 
    |                          1 |
     ---------------------------- 
    1 row in set (0.00 sec)
    
    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 DEFAULT CHARSET=latin1;
    Query OK, 0 rows affected (0.02 sec)
    
    -- Insert two rows
    
    mysql> INSERT INTO t (val) VALUES (1),(2);
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    -- With auto_increment_offset=1, the inserted rows
    -- result in an AUTO_INCREMENT value of 3
    
    mysql> SHOW CREATE TABLE tG
    *************************** 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=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
    1 row in set (0.00 sec)
    
    -- 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 |
     ------------------ 
    1 row in set (0.00 sec)
    
    -- 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 tG
    *************************** 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=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
    1 row in set (0.00 sec)
    
    -- The LAST_INSERT_ID is unchanged becuase the previous insert was unsuccessful
    
    mysql> SELECT LAST_INSERT_ID();
     ------------------ 
    | LAST_INSERT_ID() |
     ------------------ 
    |                1 |
     ------------------ 
    1 row in set (0.00 sec)        
    

    See Section 14.11.6, “AUTO_INCREMENT Handling in InnoDB” for more information.

    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 23.8.7.37, “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()

    Before MySQL 5.5.5, ROW_COUNT() returns the number of rows changed, deleted, or inserted by the last statement if it was an UPDATEDELETE, or INSERT. For other statements, the value may not be meaningful.

    As of MySQL 5.5.5, ROW_COUNT() returns a value as follows:

    • DDL statements: 0. This applies to statements such as CREATE TABLE or DROP TABLE.

    • DML statements other than SELECT: The number of affected rows. This applies to statements such as UPDATEINSERT, or DELETE (as before), but now also to statements such as ALTER TABLE and LOAD DATA INFILE.

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

    • SIGNAL statements: 0.

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

    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)
    

    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 for DATABASE().

  •  SESSION_USER()

    SESSION_USER() is a synonym for USER().

  •  SYSTEM_USER()

    SYSTEM_USER()澳门新濠3559, 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();
            -> '[email protected]'
    

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

  •  VERSION()

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

    This function is unsafe for statement-based replication. Beginning with MySQL 5.5.1, a warning is logged if you use this function when binlog_format is set to STATEMENT. (Bug #47995)

    mysql> SELECT VERSION();
            -> '5.5.53-standard'
    
  •  

Information Functions,mysqlfunctions Table12.18Information Functions Name Description BENCHMARK() Repeatedly execute an expression CHARSET() Return the character set of the...

 

Name Description
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

Session System Variables

Several system variables exist only as session variables. These cannot be set at server startup but can be assigned values at runtime using the SET statement (except for those that are read only). Most of them are not displayed by SHOW VARIABLES, but you can obtain their values using SELECT. This section describes the session system variables. For information about setting or displaying their values, see Section 5.1.5, “Using System Variables”. For example:

mysql> SELECT @@autocommit;
 -------------- 
| @@autocommit |
 -------------- 
|            1 |
 -------------- 

The lettercase of these variables does not matter.

The following table lists the system variables that have only session scope:

Table 5.3. mysqld Session System Variable Summary

Name Cmd-Line Option file System Var Dynamic
autocommit     Yes Yes
big-tables Yes Yes    
- Variable: big_tables     Yes Yes
error_count     Yes No
foreign_key_checks     Yes Yes
identity     Yes Yes
insert_id     Yes Yes
last_insert_id     Yes Yes
ndb_table_no_logging     Yes Yes
ndb_table_temporary     Yes Yes
profiling     Yes Yes
rand_seed1     Yes Yes
rand_seed2     Yes Yes
sql_auto_is_null     Yes Yes
sql_big_selects     Yes Yes
sql_big_tables     Yes Yes
sql_buffer_result     Yes Yes
sql_log_bin     Yes Yes
sql_log_off     Yes Yes
sql_log_update     Yes Yes
sql_notes     Yes Yes
sql_quote_show_create     Yes Yes
sql_safe_updates     Yes Yes
sql_warnings     Yes Yes
timestamp     Yes Yes
transaction_allow_batching     Yes Yes
unique_checks     Yes Yes
warning_count     Yes No
  • autocommit

    The autocommit mode. If set to 1, all changes to a table take effect immediately. If set to 0, you must use COMMIT to accept a transaction or ROLLBACK to cancel it. By default, client connections begin with autocommit set to 1. If you change autocommit mode from 0 to 1, MySQL performs an automatic COMMIT of any open transaction. Another way to begin a transaction is to use a START TRANSACTION or BEGIN statement. See Section 12.4.1, “START TRANSACTION, COMMIT, and ROLLBACK Syntax”.

  • big_tables

    If set to 1, all temporary tables are stored on disk rather than in memory. This is a little slower, but the error The table tbl_name is full does not occur for SELECT operations that require a large temporary table. The default value for a new connection is 0 (use in-memory temporary tables). Normally, you should never need to set this variable, because in-memory tables are automatically converted to disk-based tables as required.

    ### Note

    This variable was formerly named sql_big_tables.

  • error_count

    The number of errors that resulted from the last statement that generated messages. This variable is read only. See Section 12.5.5.18, “SHOW ERRORS Syntax”.

  • foreign_key_checks

    If set to 1 (the default), foreign key constraints for InnoDB tables are checked. If set to 0, they are ignored. Disabling foreign key checking can be useful for reloading InnoDB tables in an order different from that required by their parent/child relationships. See Section 13.6.4.4, “FOREIGN KEY Constraints”.

    Setting foreign_key_checks to 0 also affects data definition statements: DROP SCHEMA drops a schema even if it contains tables that have foreign keys that are referred to by tables outside the schema, and DROP TABLE drops tables that have foreign keys that are referred to by other tables.

    ### Note

    Setting foreign_key_checks to 1 does not trigger a scan of the existing table data. Therefore, rows added to the table while foreign_key_checks = 0 will not be verified for consistency.

  • identity

    This variable is a synonym for the last_insert_id variable. It exists for compatibility with other database systems. You can read its value with SELECT @@identity, and set it using SET identity.

  • insert_id

    The value to be used by the following INSERT or ALTER TABLE statement when inserting an AUTO_INCREMENT value. This is mainly used with the binary log.

  • last_insert_id

    The value to be returned from LAST_INSERT_ID(). This is stored in the binary log when you use LAST_INSERT_ID() in a statement that updates a table. Setting this variable does not update the value returned by the mysql_insert_id() C API function.

  • profiling

    If set to 0 (the default), statement profiling is disabled. If set to 1, statement profiling is enabled and the SHOW PROFILES and SHOW PROFILE statements provide access to profiling information. See Section 12.5.5.33, “SHOW PROFILES Syntax”. This variable was added in MySQL 5.1.24.

  • profiling_history_size

    The number of statements for which to maintain profiling information if profiling is enabled. The default value is 15. The maximum value is 100. Setting the value to 0 effectively disables profiling. See Section 12.5.5.33, “SHOW PROFILES Syntax”. This variable was added in MySQL 5.1.24.

  • rand_seed1

    The rand_seed1 and rand_seed2 variables exist as session variables only, and can be set but not read. Beginning with MySQL 5.1.18, the variables — but not their values — are shown in the output of SHOW VARIABLES.

    The purpose of these variables is to support replication of the RAND() function. For statements that invoke RAND(), the master passes two values to the slave, where they are used to seed the random number generator. The slave uses these values to set the session variables rand_seed1 and rand_seed2 so that RAND() on the slave generates the same value as on the master.

  • rand_seed2

    See the description for rand_seed1.

  • sql_auto_is_null

    If set to 1 (the default), you can find the last inserted row for a table that contains an AUTO_INCREMENT column by using the following construct:

    WHERE auto_increment_column IS NULL
    

    This behavior is used by some ODBC programs, such as Access.

  • sql_big_selects

    If set to 0, MySQL aborts SELECT statements that are likely to take a very long time to execute (that is, statements for which the optimizer estimates that the number of examined rows exceeds the value of max_join_size). This is useful when an inadvisable WHERE statement has been issued. The default value for a new connection is 1, which allows all SELECT statements.

    If you set the max_join_size system variable to a value other than DEFAULT, sql_big_selects is set to 0.

  • sql_buffer_result

    If set to 1, sql_buffer_result forces results from SELECT statements to be put into temporary tables. This helps MySQL free the table locks early and can be beneficial in cases where it takes a long time to send results to the client. The default value is 0.

  • sql_log_bin

    If set to 0, no logging is done to the binary log for the client. The client must have the SUPER privilege to set this option. The default value is 1.

  • sql_log_off

    If set to 1, no logging is done to the general query log for this client. The client must have the SUPER privilege to set this option. The default value is 0.

  • sql_log_update

    This variable is deprecated, and is mapped to sql_log_bin.

  • sql_notes

    If set to 1 (the default), warnings of Note level are recorded. If set to 0, Note warnings are suppressed. mysqldump includes output to set this variable to 0 so that reloading the dump file does not produce warnings for events that do not affect the integrity of the reload operation.

  • sql_quote_show_create

    If set to 1 (the default), the server quotes identifiers for SHOW CREATE TABLE and SHOW CREATE DATABASE statements. If set to 0, quoting is disabled. This option is enabled by default so that replication works for identifiers that require quoting. See Section 12.5.5.12, “SHOW CREATE TABLE Syntax”, and Section 12.5.5.8, “SHOW CREATE DATABASE Syntax”.

  • sql_safe_updates

    If set to 1, MySQL aborts UPDATE or DELETE statements that do not use a key in the WHERE clause or a LIMIT clause. This makes it possible to catch UPDATE or DELETE statements where keys are not used properly and that would probably change or delete a large number of rows. The default value is 0.

  • sql_warnings

    This variable controls whether single-row INSERT statements produce an information string if warnings occur. The default is 0. Set the value to 1 to produce an information string.

  • timestamp = {timestamp_value | DEFAULT}

    Set the time for this client. This is used to get the original timestamp if you use the binary log to restore rows. timestamp_value should be a Unix epoch timestamp, not a MySQL timestamp.

    SET timestamp affects the value returned by NOW() but not by SYSDATE(). This means that timestamp settings in the binary log have no effect on invocations of SYSDATE(). The server can be started with the --sysdate-is-now option to cause SYSDATE() to be an alias for NOW(), in which case SET timestamp affects both functions.

  • unique_checks

    If set to 1 (the default), uniqueness checks for secondary indexes in InnoDB tables are performed. If set to 0, storage engines are allowed to assume that duplicate keys are not present in input data. If you know for certain that your data does not contain uniqueness violations, you can set this to 0 to speed up large table imports to InnoDB.

    Note that setting this variable to 0 does not require storage engines to ignore duplicate keys. An engine is still allowed to check for them and issue duplicate-key errors if it detects them.

  • warning_count

    The number of errors, warnings, and notes that resulted from the last statement that generated messages. This variable is read only. See Section 12.5.5.42, “SHOW WARNINGS Syntax”.

 

  •  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(Nexpr) 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'
    
  •  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 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 Ignorable NULL or an expression derived from NULL
  •  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.

    The value returned by CONNECTION_ID() is the same type of value as displayed in the ID column of the INFORMATION_SCHEMA.PROCESSLIST table, the Id column of SHOW PROCESSLIST output, and the PROCESSLIST_ID column of the Performance Schema threads table.

    mysql> SELECT CONNECTION_ID();
            -> 23786
    
  •  CURRENT_USERCURRENT_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 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 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.

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

    The following statements support use of the CURRENT_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 of CURRENT_USER() has for replication in different releases of MySQL 5.5, see Section 17.4.1.8, “Replication of CURRENT_USER()”.

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

    If there is no default database, DATABASE() returns NULL.

  •  FOUND_ROWS()

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

    Important

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

  •  LAST_INSERT_ID()LAST_INSERT_ID(expr)

    With no argument, LAST_INSERT_ID() returns a 64-bit value representing the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement. The value has a type of BIGINT UNSIGNED as of MySQL 5.5.29, BIGINT (signed) before that. The value of LAST_INSERT_ID() remains unchanged if no rows are successfully inserted.

    With an argument, LAST_INSERT_ID() returns an unsigned integer as of MySQL 5.5.29, a signed integer before that.

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

    mysql> SELECT LAST_INSERT_ID();
            -> 195
    

    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.

    Prior to MySQL 5.5.35, this function was not replicated correctly if replication filtering rules were in use. (Bug #17234370, Bug #69861)

    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 will not 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 anAUTO_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 INSERTstatement 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 LAST_INSERT_ID() remains unchanged from the current value (or 0 is returned if the connection has not yet performed a successful INSERT) and, for non-transactional tables, theAUTO_INCREMENT counter is not incremented. For InnoDB tables, the AUTO_INCREMENT counter is incremented if innodb_autoinc_lock_mode is set to 1 or 2, as demonstrated in the following example:

    mysql> USE test;
    Database changed
    
    mysql> SELECT @@innodb_autoinc_lock_mode;
     ---------------------------- 
    | @@innodb_autoinc_lock_mode |
     ---------------------------- 
    |                          1 |
     ---------------------------- 
    1 row in set (0.00 sec)
    
    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 DEFAULT CHARSET=latin1;
    Query OK, 0 rows affected (0.02 sec)
    
    -- Insert two rows
    
    mysql> INSERT INTO t (val) VALUES (1),(2);
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    -- With auto_increment_offset=1, the inserted rows
    -- result in an AUTO_INCREMENT value of 3
    
    mysql> SHOW CREATE TABLE tG
    *************************** 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=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
    1 row in set (0.00 sec)
    
    -- 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 |
     ------------------ 
    1 row in set (0.00 sec)
    
    -- 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 tG
    *************************** 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=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
    1 row in set (0.00 sec)
    
    -- The LAST_INSERT_ID is unchanged becuase the previous insert was unsuccessful
    
    mysql> SELECT LAST_INSERT_ID();
     ------------------ 
    | LAST_INSERT_ID() |
     ------------------ 
    |                1 |
     ------------------ 
    1 row in set (0.00 sec)        
    

    See Section 14.11.6, “AUTO_INCREMENT Handling in InnoDB” for more information.

    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 23.8.7.37, “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()

    Before MySQL 5.5.5, ROW_COUNT() returns the number of rows changed, deleted, or inserted by the last statement if it was an UPDATEDELETE, or INSERT. For other statements, the value may not be meaningful.

    As of MySQL 5.5.5, ROW_COUNT() returns a value as follows:

    • DDL statements: 0. This applies to statements such as CREATE TABLE or DROP TABLE.

    • DML statements other than SELECT: The number of affected rows. This applies to statements such as UPDATEINSERT, or DELETE (as before), but now also to statements such as ALTER TABLE and LOAD DATA INFILE.

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

    • SIGNAL statements: 0.

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

    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)
    

    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 for DATABASE().

  •  SESSION_USER()

    SESSION_USER() is a synonym for USER().

  •  SYSTEM_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().

  •  VERSION()

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

    This function is unsafe for statement-based replication. Beginning with MySQL 5.5.1, a warning is logged if you use this function when binlog_format is set to STATEMENT. (Bug #47995)

    mysql> SELECT VERSION();
            -> '5.5.53-standard'
    
  •  

编辑:数据库 本文来源:MySQL Information Functions

关键词: 澳门新濠3559