( Information functions

Info Catalog ( Encryption functions ( Other Functions ( Miscellaneous functions
 12.8.3 Information Functions
      The `BENCHMARK()' function executes the expression EXPR repeatedly
      COUNT times.  It may be used to time how fast MySQL processes the
      expression.  The result value is always `0'.  The intended use is
      from within the `mysql' client, which reports query execution
           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.
      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'
      `CHARSET()' was added in MySQL 4.1.0.
      Returns the collation coercibility value of the string argument.
           mysql> SELECT COERCIBILITY('abc' COLLATE latin1_swedish_ci);
                   -> 0
           mysql> SELECT COERCIBILITY('abc');
                   -> 3
           mysql> SELECT COERCIBILITY(USER());
                   -> 2
      The return values have the following meanings:
      *Coercibility*       *Meaning*
      `0'                  Explicit collation
      `1'                  No collation
      `2'                  Implicit collation
      `3'                  Coercible
      Lower values have higher precedence.
      `COERCIBILITY()' was added in MySQL 4.1.1.
      Returns the collation for the character set of the string argument.
           mysql> SELECT COLLATION('abc');
                   -> 'latin1_swedish_ci'
           mysql> SELECT COLLATION(_utf8'abc');
                   -> 'utf8_general_ci'
      `COLLATION()' was added in MySQL 4.1.0.
      Returns the connection ID (thread ID) for the connection.  Every
      connection has its own unique ID.
           mysql> SELECT CONNECTION_ID();
                   -> 23786
      `CONNECTION_ID()' was added in MySQL 3.23.14.
      Returns the username and hostname combination that the current
      session was authenticated as. This value corresponds to the MySQL
      account that determines your access privileges. It can be
      different 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
      username 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 username part of the
      `CURRENT_USER()' value). One way this might occur is that there is
      no account listed in the grant tables for `davida'.
      `CURRENT_USER()' was added in MySQL 4.0.6.
      Returns the default (current) database name.
           mysql> SELECT DATABASE();
                   -> 'test'
      If there is no default database, `DATABASE()' returns `NULL' as of
      MySQL 4.1.1, and the empty string before that.
      A `SELECT' statement may include a `LIMIT' clause to restrict the
      number of rows the server returns to the client.  In some cases,
      it is desirable to know how many rows the statement would have
      returned without the `LIMIT', but without running the statement
      again.  To get this row count, include a `SQL_CALC_FOUND_ROWS'
      option in the `SELECT' statement, then invoke `FOUND_ROWS()'
               -> WHERE id > 100 LIMIT 10;
           mysql> SELECT FOUND_ROWS();
      The second `SELECT' will return a number indicating how many rows
      the first `SELECT' would have returned had it been written without
      the `LIMIT' clause.  (If the preceding `SELECT' statement does not
      include the `SQL_CALC_FOUND_ROWS' option, then `FOUND_ROWS()' may
      return a different result when `LIMIT' is used than when it is
      Note that 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()'
      allows 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' queries than for simple `SELECT' statements, because
      `LIMIT' may occur at multiple places in a `UNION'. It may be
      applied to individual `SELECT' statements in the `UNION', or
      global to the `UNION' result as a whole.
      The intent of `SQL_CALC_FOUND_ROWS' for `UNION' is that it should
      return the row count that would be returned without a global
      `LIMIT'.  The conditions for use of `SQL_CALC_FOUND_ROWS' with
      `UNION' are:
         * The `SQL_CALC_FOUND_ROWS' keyword must appear in the first
           `SELECT' of the `UNION'.
         * The value of `FOUND_ROWS()' is exact only if `UNION ALL' is
           used.  If `UNION' without `ALL' is used, duplicate removal
           occurs and the value of `FOUND_ROWS()' is only approximate.
         * If no `LIMIT' is present in the `UNION', `SQL_CALC_FOUND_ROWS'
           is ignored and returns the number of rows in the temporary
           table that is created to process the `UNION'.
      `SQL_CALC_FOUND_ROWS' and `FOUND_ROWS()' are available starting at
      MySQL 4.0.0.
      Returns the last automatically generated value that was inserted
      into an `AUTO_INCREMENT' column.
           mysql> SELECT LAST_INSERT_ID();
                   -> 195
      The last ID that was generated is maintained in the server on a
      per-connection basis. This means the value the function returns to
      a given client is the most recent `AUTO_INCREMENT' value generated
      by that client. The value cannot be affected by other clients,
      even if they generate `AUTO_INCREMENT' values of their own. This
      behavior ensures that you can retrieve your 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 update the
      `AUTO_INCREMENT' column of a row with a non-magic value (that is,
      a value that is not `NULL' and not `0').
      If you insert many rows at the same time with an insert statement,
      `LAST_INSERT_ID()' returns the value for the first inserted row.
      The reason for this is to make it possible to easily reproduce the
      same `INSERT' statement against some other server.
      If you use `INSERT IGNORE' and the record is ignored, the
      `AUTO_INCREMENT' counter still is incremented and
      `LAST_INSERT_ID()' returns the new value.
      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:
         * 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);
         * 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.   `mysql_insert_id()' 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
      Note that `mysql_insert_id()' is only updated after `INSERT' and
      `UPDATE' statements, so you cannot use the C API function to
      retrieve the value for `LAST_INSERT_ID(EXPR)' after executing other
      SQL statements like `SELECT' or `SET'.
      `ROW_COUNT()' returns the number of rows updated, inserted, or
      deleted by the preceding statement.  This is the same as the row
      count that the `mysql' client displays and the value from the
      `mysql_affected_rows()' C API function.
           mysql> INSERT INTO t VALUES(1),(2),(3);
           Query OK, 3 rows affected (0.00 sec)
           Records: 3  Duplicates: 0  Warnings: 0
           mysql> SELECT ROW_COUNT();
           | ROW_COUNT() |
           |           3 |
           1 row in set (0.00 sec)
           mysql> DELETE FROM t WHERE i IN(1,2);
           Query OK, 2 rows affected (0.00 sec)
           mysql> SELECT ROW_COUNT();
           | ROW_COUNT() |
           |           2 |
           1 row in set (0.00 sec)
      `ROW_COUNT()' was added in MySQL 5.0.1.
      `SESSION_USER()' is a synonym for `USER()'.
      `SYSTEM_USER()' is a synonym for `USER()'.
      Returns the current MySQL username and hostname.
           mysql> SELECT USER();
                   -> 'davida@localhost'
      The value indicates the username you specified when connecting to
      the server, and the client host from which you connected. The
      value can be different than that of `CURRENT_USER()'.
      Prior to MySQL 3.22.11, the function value does not include the
      client hostname.  You can extract just the username part,
      regardless of whether the value includes a hostname part, like
           mysql> SELECT SUBSTRING_INDEX(USER(),'@',1);
                   -> 'davida'
      As of MySQL 4.1, `USER()' returns a value in the `utf8' character
      set, so you should also make sure that the `'@'' string literal is
      interpreted in that character set:
           mysql> SELECT SUBSTRING_INDEX(USER(),_utf8'@',1);
                   -> 'davida'
      Returns a string that indicates the MySQL server version.
           mysql> SELECT VERSION();
                   -> '4.1.3-beta-log'
      Note that if your version string ends with `-log' this means that
      logging is enabled.
Info Catalog ( Encryption functions ( Other Functions ( Miscellaneous functions
automatically generated byinfo2html