MySQL Information Functions

淩亂°似流年 2021-12-17 14:23 171阅读 0赞

**Table 12.18 Information Functions**

<table>     
 <thead> 
  <tr> 
   <th>Name</th> 
   <th>Description</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><a href="http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_benchmark" rel="nofollow"><code>BENCHMARK()</code></a></td> 
   <td>Repeatedly execute an expression</td> 
  </tr> 
  <tr> 
   <td><a href="http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_charset" rel="nofollow"><code>CHARSET()</code></a></td> 
   <td>Return the character set of the argument</td> 
  </tr> 
  <tr> 
   <td><a href="http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_coercibility" rel="nofollow"><code>COERCIBILITY()</code></a></td> 
   <td>Return the collation coercibility value of the string argument</td> 
  </tr> 
  <tr> 
   <td><a href="http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_collation" rel="nofollow"><code>COLLATION()</code></a></td> 
   <td>Return the collation of the string argument</td> 
  </tr> 
  <tr> 
   <td><a href="http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_connection-id" rel="nofollow"><code>CONNECTION_ID()</code></a></td> 
   <td>Return the connection ID (thread ID) for the connection</td> 
  </tr> 
  <tr> 
   <td><a href="http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_current-user" rel="nofollow"><code>CURRENT_USER()</code>,&nbsp;<code>CURRENT_USER</code></a></td> 
   <td>The authenticated user name and host name</td> 
  </tr> 
  <tr> 
   <td><a href="http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_database" rel="nofollow"><code>DATABASE()</code></a></td> 
   <td>Return the default (current) database name</td> 
  </tr> 
  <tr> 
   <td><a href="http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_found-rows" rel="nofollow"><code>FOUND_ROWS()</code></a></td> 
   <td>For a SELECT with a LIMIT clause, the number of rows that would be returned were there no LIMIT clause</td> 
  </tr> 
  <tr> 
   <td><a href="http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_last-insert-id" rel="nofollow"><code>LAST_INSERT_ID()</code></a></td> 
   <td>Value of the AUTOINCREMENT column for the last INSERT</td> 
  </tr> 
  <tr> 
   <td><a href="http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_row-count" rel="nofollow"><code>ROW_COUNT()</code></a></td> 
   <td>The number of rows updated</td> 
  </tr> 
  <tr> 
   <td><a href="http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_schema" rel="nofollow"><code>SCHEMA()</code></a></td> 
   <td>Synonym for DATABASE()</td> 
  </tr> 
  <tr> 
   <td><a href="http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_session-user" rel="nofollow"><code>SESSION_USER()</code></a></td> 
   <td>Synonym for USER()</td> 
  </tr> 
  <tr> 
   <td><a href="http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_system-user" rel="nofollow"><code>SYSTEM_USER()</code></a></td> 
   <td>Synonym for USER()</td> 
  </tr> 
  <tr> 
   <td><a href="http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_user" rel="nofollow"><code>USER()</code></a></td> 
   <td>The user name and host name provided by the client</td> 
  </tr> 
  <tr> 
   <td><a href="http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_version" rel="nofollow"><code>VERSION()</code></a></td> 
   <td>Return a string that indicates the MySQL server version</td> 
  </tr> 
 </tbody> 
</table>

*   [`BENCHMARK(count,expr)`][BENCHMARK_count_expr]
    
    The [`BENCHMARK()`][BENCHMARK_count_expr] 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**][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()`][BENCHMARK_count_expr] several times, and to interpret the result with regard to how heavily loaded the server machine is.
    
    [`BENCHMARK()`][BENCHMARK_count_expr] 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))`][BENCHMARK_count_expr] will fail if the table `t` has more than one column or more than one row.
     *  Executing a `SELECT expr` statement *`N`* times differs from executing `SELECT BENCHMARK(N, expr)` in terms of the amount of overhead involved. The two have very different execution profiles and you should not expect them to take the same amount of time. The former involves the parser, optimizer, table locking, and runtime evaluation *`N`* times each. The latter involves only runtime evaluation *`N`* times, and all the other components just once. Memory structures already allocated are reused, and runtime optimizations such as local caching of results already evaluated for aggregate functions can alter the results. Use of [`BENCHMARK()`][BENCHMARK_count_expr] 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)`][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)`][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.
    
    <table>      
     <thead> 
      <tr> 
       <th>Coercibility</th> 
       <th>Meaning</th> 
       <th>Example</th> 
      </tr> 
     </thead> 
     <tbody> 
      <tr> 
       <td><code>0</code></td> 
       <td>Explicit collation</td> 
       <td>Value with&nbsp;<code>COLLATE</code>&nbsp;clause</td> 
      </tr> 
      <tr> 
       <td><code>1</code></td> 
       <td>No collation</td> 
       <td>Concatenation of strings with different collations</td> 
      </tr> 
      <tr> 
       <td><code>2</code></td> 
       <td>Implicit collation</td> 
       <td>Column value, stored routine parameter or local variable</td> 
      </tr> 
      <tr> 
       <td><code>3</code></td> 
       <td>System constant</td> 
       <td><a href="http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_user" rel="nofollow"><code>USER()</code></a>&nbsp;return value</td> 
      </tr> 
      <tr> 
       <td><code>4</code></td> 
       <td>Coercible</td> 
       <td>Literal string</td> 
      </tr> 
      <tr> 
       <td><code>5</code></td> 
       <td>Ignorable</td> 
       <td><code>NULL</code>&nbsp;or an expression derived from&nbsp;<code>NULL</code></td> 
      </tr> 
     </tbody> 
    </table>
 *   [`COLLATION(str)`][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()`][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()`][CONNECTION_ID] is the same type of value as displayed in the `ID` column of the [`INFORMATION_SCHEMA.PROCESSLIST`][INFORMATION_SCHEMA.PROCESSLIST] table, the `Id` column of [`SHOW PROCESSLIST`][SHOW PROCESSLIST] output, and the `PROCESSLIST_ID` column of the Performance Schema [`threads`][threads] table.
    
        mysql> SELECT CONNECTION_ID();
                -> 23786
 *   [`CURRENT_USER`][CURRENT_USER], [`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 the `utf8` character set.
    
    The value of [`CURRENT_USER()`][CURRENT_USER] can differ from the value of [`USER()`][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()`][USER] function), the server authenticated the client using an anonymous user account (as seen by the empty user name part of the [`CURRENT_USER()`][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()`][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()`][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()`][CURRENT_USER] returns the account for the user who defined the object. To return the invoker, use [`USER()`][USER] or [`SESSION_USER()`][SESSION_USER].
    
    The following statements support use of the [`CURRENT_USER()`][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()`][CURRENT_USER] is expanded where and as needed:
    
     *  [`DROP USER`][DROP USER]
     *  [`RENAME USER`][RENAME USER]
     *  [`GRANT`][GRANT]
     *  [`REVOKE`][REVOKE]
     *  [`CREATE FUNCTION`][CREATE FUNCTION]
     *  [`CREATE PROCEDURE`][CREATE PROCEDURE]
     *  [`CREATE TRIGGER`][CREATE TRIGGER]
     *  [`CREATE EVENT`][CREATE EVENT]
     *  [`CREATE VIEW`][CREATE VIEW]
     *  [`ALTER EVENT`][ALTER EVENT]
     *  [`ALTER VIEW`][ALTER VIEW]
     *  [`SET PASSWORD`][SET PASSWORD]
    
    For information about the implications that this expansion of [`CURRENT_USER()`][CURRENT_USER] has for replication in different releases of MySQL 5.5, see [Section 17.4.1.8, “Replication of CURRENT\_USER()”][Section 17.4.1.8_ _Replication of CURRENT_USER].
 *   [`DATABASE()`][DATABASE]
    
    Returns the default (current) database name as a string in the `utf8` character set. If there is no default database, [`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()`][DATABASE] returns `NULL`.
 *   [`FOUND_ROWS()`][FOUND_ROWS]
    
    A [`SELECT`][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`][SELECT] statement, and then invoke [`FOUND_ROWS()`][FOUND_ROWS] afterward:
    
        mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
            -> WHERE id > 100 LIMIT 10;
        mysql> SELECT FOUND_ROWS();
    
    The second [`SELECT`][SELECT] returns a number indicating how many rows the first [`SELECT`][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`][SELECT] statement, [`FOUND_ROWS()`][FOUND_ROWS] returns the number of rows in the result set returned by that statement. If the statement includes a `LIMIT` clause,[`FOUND_ROWS()`][FOUND_ROWS] returns the number of rows up to the limit. For example, [`FOUND_ROWS()`][FOUND_ROWS] returns 10 or 60, respectively, if the statement includes `LIMIT 10` or `LIMIT 50, 10`.
    
    The row count available through [`FOUND_ROWS()`][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()`][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()`][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()`][FOUND_ROWS] is more complex for [`UNION`][UNION] statements than for simple [`SELECT`][SELECT] statements, because `LIMIT` may occur at multiple places in a [`UNION`][UNION]. It may be applied to individual [`SELECT`][SELECT] statements in the [`UNION`][UNION], or global to the [`UNION`][UNION] result as a whole.
    
    The intent of `SQL_CALC_FOUND_ROWS` for [`UNION`][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`][UNION] are:
    
     *  The `SQL_CALC_FOUND_ROWS` keyword must appear in the first [`SELECT`][SELECT] of the [`UNION`][UNION].
     *  The value of [`FOUND_ROWS()`][FOUND_ROWS] is exact only if [`UNION ALL`][UNION] is used. If [`UNION`][UNION] without `ALL` is used, duplicate removal occurs and the value of [`FOUND_ROWS()`][FOUND_ROWS] is only approximate.
     *  If no `LIMIT` is present in the [`UNION`][UNION], `SQL_CALC_FOUND_ROWS` is ignored and returns the number of rows in the temporary table that is created to process the [`UNION`][UNION].
    
    Beyond the cases described here, the behavior of [`FOUND_ROWS()`][FOUND_ROWS] is undefined (for example, its value following a [`SELECT`][SELECT] statement that fails with an error).
    
    Important
    
    [`FOUND_ROWS()`][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], [`LAST_INSERT_ID(expr)`][LAST_INSERT_ID]
    
    With no argument, [`LAST_INSERT_ID()`][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`][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()`][LAST_INSERT_ID] remains unchanged if no rows are successfully inserted.
    
    With an argument, [`LAST_INSERT_ID()`][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()`][LAST_INSERT_ID]. Suppose that you generate an `AUTO_INCREMENT` value with one statement, and then refer to [`LAST_INSERT_ID()`][LAST_INSERT_ID] in a multiple-row [`INSERT`][INSERT] statement that inserts rows into a table with its own `AUTO_INCREMENT` column. The value of [`LAST_INSERT_ID()`][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()`][LAST_INSERT_ID] and [`LAST_INSERT_ID(expr)`][LAST_INSERT_ID], the effect is undefined.)
    
    If the previous statement returned an error, the value of [`LAST_INSERT_ID()`][LAST_INSERT_ID] is undefined. For transactional tables, if the statement is rolled back due to an error, the value of [`LAST_INSERT_ID()`][LAST_INSERT_ID] is left undefined. For manual [`ROLLBACK`][ROLLBACK], the value of [`LAST_INSERT_ID()`][LAST_INSERT_ID] is not restored to that before the transaction; it remains as it was at the point of the [`ROLLBACK`][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()`][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()`][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()`][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 an`AUTO_INCREMENT` column *by that client*. This value cannot be affected by other clients, even if they generate `AUTO_INCREMENT` values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.
    
    The value of [`LAST_INSERT_ID()`][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`][INSERT] statement, [`LAST_INSERT_ID()`][LAST_INSERT_ID] returns the value generated for the *first* inserted row *only*. The reason for this is to make it possible to reproduce easily the same [`INSERT`][INSERT]statement 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`][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()`][LAST_INSERT_ID] for the following [`SELECT`][SELECT] statement.
    
    If you use [`INSERT IGNORE`][INSERT] and the row is ignored, the [`LAST_INSERT_ID()`][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, the`AUTO_INCREMENT` counter is not incremented. For `InnoDB` tables, the `AUTO_INCREMENT` counter is incremented if [`innodb_autoinc_lock_mode`][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 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=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 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=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”][Section 14.11.6_ _AUTO_INCREMENT Handling in InnoDB] for more information.
    
    If *`expr`* is given as an argument to [`LAST_INSERT_ID()`][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()`][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`][UPDATE] statement increments the sequence counter and causes the next call to [`LAST_INSERT_ID()`][LAST_INSERT_ID] to return the updated value. The [`SELECT`][SELECT] statement retrieves that value. The [`mysql_insert_id()`][mysql_insert_id] C API function can also be used to get the value. See [Section 23.8.7.37, “mysql\_insert\_id()”][mysql_insert_id].
    
    You can generate sequences without calling [`LAST_INSERT_ID()`][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`][UPDATE] statement and get their own sequence value with the [`SELECT`][SELECT] statement (or [`mysql_insert_id()`][mysql_insert_id]), without affecting or being affected by other clients that generate their own sequence values.
    
    Note that [`mysql_insert_id()`][mysql_insert_id] is only updated after [`INSERT`][INSERT] and [`UPDATE`][UPDATE] statements, so you cannot use the C API function to retrieve the value for [`LAST_INSERT_ID(expr)`][LAST_INSERT_ID] after executing other SQL statements like [`SELECT`][SELECT] or [`SET`][SET].
 *   [`ROW_COUNT()`][ROW_COUNT]
    
    Before MySQL 5.5.5, [`ROW_COUNT()`][ROW_COUNT] returns the number of rows changed, deleted, or inserted by the last statement if it was an [`UPDATE`][UPDATE], [`DELETE`][DELETE], or [`INSERT`][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`][CREATE TABLE] or [`DROP TABLE`][DROP TABLE].
     *  DML statements other than [`SELECT`][SELECT]: The number of affected rows. This applies to statements such as [`UPDATE`][UPDATE], [`INSERT`][INSERT], or [`DELETE`][DELETE] (as before), but now also to statements such as [`ALTER TABLE`][ALTER TABLE] and [`LOAD DATA INFILE`][LOAD DATA INFILE].
     *  [`SELECT`][SELECT]: -1 if the statement returns a result set, or the number of rows “affected” if it does not. For example, for `SELECT * FROM t1`, [`ROW_COUNT()`][ROW_COUNT] returns -1. For `SELECT * FROM t1 INTO OUTFILE 'file_name'`, [`ROW_COUNT()`][ROW_COUNT]returns the number of rows written to the file.
     *  [`SIGNAL`][SIGNAL] statements: 0.
    
    For [`UPDATE`][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()`][mysql_real_connect] when connecting to [**mysqld**][mysqld], the affected-rows value is the number of rows “found”; that is, matched by the `WHERE` clause.
    
    For [`REPLACE`][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`][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 the`CLIENT_FOUND_ROWS` flag, the affected-rows value is 1 (not 0) if an existing row is set to its current values.
    
    The [`ROW_COUNT()`][ROW_COUNT] value is similar to the value from the [`mysql_affected_rows()`][mysql_affected_rows] C API function and the row count that the [**mysql**][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()`][ROW_COUNT] is not replicated reliably using statement-based replication. This function is automatically replicated using row-based replication.
 *   [`SCHEMA()`][SCHEMA]
    
    This function is a synonym for [`DATABASE()`][DATABASE].
 *   [`SESSION_USER()`][SESSION_USER]
    
    [`SESSION_USER()`][SESSION_USER] is a synonym for [`USER()`][USER].
 *   [`SYSTEM_USER()`][SYSTEM_USER]
    
    [`SYSTEM_USER()`][SYSTEM_USER] is a synonym for [`USER()`][USER].
 *   [`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()`][CURRENT_USER].
 *   [`VERSION()`][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`][version] system variable in [Section 5.1.4, “Server System Variables”][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`][binlog_format] is set to `STATEMENT`. (Bug \#47995)
    
        mysql> SELECT VERSION();
                -> '5.5.53-standard'
 *

转载于:https://www.cnblogs.com/kungfupanda/p/5867069.html

[BENCHMARK_count_expr]: http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_benchmark
[mysql]: http://dev.mysql.com/doc/refman/5.5/en//mysql.html
[CHARSET_str]: http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_charset
[COERCIBILITY_str]: http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_coercibility
[COLLATION_str]: http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_collation
[CONNECTION_ID]: http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_connection-id
[INFORMATION_SCHEMA.PROCESSLIST]: http://dev.mysql.com/doc/refman/5.5/en//processlist-table.html
[SHOW PROCESSLIST]: http://dev.mysql.com/doc/refman/5.5/en//show-processlist.html
[threads]: http://dev.mysql.com/doc/refman/5.5/en//threads-table.html
[CURRENT_USER]: http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_current-user
[USER]: http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_user
[SESSION_USER]: http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_session-user
[DROP USER]: http://dev.mysql.com/doc/refman/5.5/en//drop-user.html
[RENAME USER]: http://dev.mysql.com/doc/refman/5.5/en//rename-user.html
[GRANT]: http://dev.mysql.com/doc/refman/5.5/en//grant.html
[REVOKE]: http://dev.mysql.com/doc/refman/5.5/en//revoke.html
[CREATE FUNCTION]: http://dev.mysql.com/doc/refman/5.5/en//create-function.html
[CREATE PROCEDURE]: http://dev.mysql.com/doc/refman/5.5/en//create-procedure.html
[CREATE TRIGGER]: http://dev.mysql.com/doc/refman/5.5/en//create-trigger.html
[CREATE EVENT]: http://dev.mysql.com/doc/refman/5.5/en//create-event.html
[CREATE VIEW]: http://dev.mysql.com/doc/refman/5.5/en//create-view.html
[ALTER EVENT]: http://dev.mysql.com/doc/refman/5.5/en//alter-event.html
[ALTER VIEW]: http://dev.mysql.com/doc/refman/5.5/en//alter-view.html
[SET PASSWORD]: http://dev.mysql.com/doc/refman/5.5/en//set-password.html
[Section 17.4.1.8_ _Replication of CURRENT_USER]: http://dev.mysql.com/doc/refman/5.5/en//replication-features-current-user.html
[DATABASE]: http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_database
[FOUND_ROWS]: http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_found-rows
[SELECT]: http://dev.mysql.com/doc/refman/5.5/en//select.html
[UNION]: http://dev.mysql.com/doc/refman/5.5/en//union.html
[LAST_INSERT_ID]: http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_last-insert-id
[INSERT]: http://dev.mysql.com/doc/refman/5.5/en//insert.html
[ROLLBACK]: http://dev.mysql.com/doc/refman/5.5/en//commit.html
[innodb_autoinc_lock_mode]: http://dev.mysql.com/doc/refman/5.5/en//innodb-parameters.html#sysvar_innodb_autoinc_lock_mode
[Section 14.11.6_ _AUTO_INCREMENT Handling in InnoDB]: http://dev.mysql.com/doc/refman/5.5/en//innodb-auto-increment-handling.html
[UPDATE]: http://dev.mysql.com/doc/refman/5.5/en//update.html
[mysql_insert_id]: http://dev.mysql.com/doc/refman/5.5/en//mysql-insert-id.html
[SET]: http://dev.mysql.com/doc/refman/5.5/en//set-variable.html
[ROW_COUNT]: http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_row-count
[DELETE]: http://dev.mysql.com/doc/refman/5.5/en//delete.html
[CREATE TABLE]: http://dev.mysql.com/doc/refman/5.5/en//create-table.html
[DROP TABLE]: http://dev.mysql.com/doc/refman/5.5/en//drop-table.html
[ALTER TABLE]: http://dev.mysql.com/doc/refman/5.5/en//alter-table.html
[LOAD DATA INFILE]: http://dev.mysql.com/doc/refman/5.5/en//load-data.html
[SIGNAL]: http://dev.mysql.com/doc/refman/5.5/en//signal.html
[mysql_real_connect]: http://dev.mysql.com/doc/refman/5.5/en//mysql-real-connect.html
[mysqld]: http://dev.mysql.com/doc/refman/5.5/en//mysqld.html
[REPLACE]: http://dev.mysql.com/doc/refman/5.5/en//replace.html
[INSERT ... ON DUPLICATE KEY UPDATE]: http://dev.mysql.com/doc/refman/5.5/en//insert-on-duplicate.html
[mysql_affected_rows]: http://dev.mysql.com/doc/refman/5.5/en//mysql-affected-rows.html
[SCHEMA]: http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_schema
[SYSTEM_USER]: http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_system-user
[VERSION]: http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_version
[version]: http://dev.mysql.com/doc/refman/5.5/en//server-system-variables.html#sysvar_version
[Section 5.1.4_ _Server System Variables]: http://dev.mysql.com/doc/refman/5.5/en//server-system-variables.html
[binlog_format]: http://dev.mysql.com/doc/refman/5.5/en//replication-options-binary-log.html#sysvar_binlog_format