GNU Info

Info Node: (mysql.info)Miscellaneous functions

(mysql.info)Miscellaneous functions


Prev: Bit functions Up: Other Functions
Enter node , (file) or (file)node

Miscellaneous Functions
.......................

`DATABASE()'
     Returns the current database name:

          mysql> select DATABASE();
                  -> 'test'

     If there is no current database, `DATABASE()' returns the empty
     string.

`USER()'
`SYSTEM_USER()'
`SESSION_USER()'
     Returns the current MySQL user name:

          mysql> select USER();
                  -> 'davida@localhost'

     In MySQL Version 3.22.11 or later, this includes the client
     hostname as well  as the user name.  You can extract just the user
     name part like this (which works whether or not the value includes
     a hostname part):

          mysql> select substring_index(USER(),"@",1);
                  -> 'davida'

`PASSWORD(str)'
     Calculates a password string from the plaintext password `str'.
     This is the function that is used for encrypting MySQL passwords
     for storage in the `Password' column of the `user' grant table:

          mysql> select PASSWORD('badpwd');
                  -> '7f84554057dd964b'

     `PASSWORD()' encryption is non-reversible.

     `PASSWORD()' does not perform password encryption in the same way
     that Unix passwords are encrypted.  You should not assume that if
     your Unix password and your MySQL password are the same,
     `PASSWORD()' will result in the same encrypted value as is stored
     in the Unix password file.  See `ENCRYPT()'.

`ENCRYPT(str[,salt])'
     Encrypt `str' using the Unix `crypt()' system call. The `salt'
     argument should be a string with two characters.  (As of MySQL
     Version 3.22.16, `salt' may be longer than two characters.):

          mysql> select ENCRYPT("hello");
                  -> 'VxuFAJXVARROc'

     If `crypt()' is not available on your system, `ENCRYPT()' always
     returns `NULL'.

     `ENCRYPT()' ignores all but the first 8 characters of `str', at
     least on some systems.  This will be determined by the behavior of
     the underlying `crypt()' system call.

`ENCODE(str,pass_str)'
     Encrypt `str' using `pass_str' as the password.  To decrypt the
     result, use `DECODE()'.

     The results is a binary string of the same length as `string'.  If
     you want to save it in a column, use a `BLOB' column type.

`DECODE(crypt_str,pass_str)'
     Descrypts the encrypted string `crypt_str' using `pass_str' as the
     password.  `crypt_str' should be a string returned from `ENCODE()'.

`MD5(string)'
     Calculates a MD5 checksum for the string. Value is returned as a
     32 long hex number that may, for example, be used as a hash key:

          mysql> select MD5("testing");
                  -> 'ae2b1fca515949e5d54fb22b8ed95575'

     This is an "RSA Data Security, Inc. MD5 Message-Digest Algorithm".

`LAST_INSERT_ID([expr])'
     Returns the last automatically generated value that was inserted
     into an `AUTO_INCREMENT' column.  *Note `mysql_insert_id()':
     mysql_insert_id.

          mysql> select LAST_INSERT_ID();
                  -> 195

     The last ID that was generated is maintained in the server on a
     per-connection basis.  It will not be changed by another client.
     It will not even be changed if you update another `AUTO_INCREMENT'
     column 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 so that you it makes it possible to easily
     reproduce the same `INSERT' statement against some other server.

     If `expr' is given as an argument to `LAST_INSERT_ID()', then the
     value of the argument is returned by the function, is set as the
     next value to be returned by `LAST_INSERT_ID()' and used as the
     next auto_increment value.  This can be used to simulate sequences:

     First create the table:

          mysql> create table sequence (id int not null);
          mysql> insert into sequence values (0);

     Then the table can be used to generate sequence numbers like this:

          mysql> update sequence set id=LAST_INSERT_ID(id+1);

     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.  You can retrieve the new ID as you would read any normal
     `AUTO_INCREMENT' value in MySQL.  For example, `LAST_INSERT_ID()'
     (without an argument) will return the new ID.  The C API function
     `mysql_insert_id()' can also be used to get the value.

     Note that as `mysql_insert_id()' is only updated after `INSERT'
     and `UPDATE' statements, you can't use this function to retrieve
     the value for `LAST_INSERT_ID(expr)' after executing other SQL
     statements like `SELECT' or `SET'.

`FORMAT(X,D)'
     Formats the number `X' to a format like `'#,###,###.##'', rounded
     to `D' decimals.  If `D' is `0', the result will have no decimal
     point or fractional part:

          mysql> select FORMAT(12332.123456, 4);
                  -> '12,332.1235'
          mysql> select FORMAT(12332.1,4);
                  -> '12,332.1000'
          mysql> select FORMAT(12332.2,0);
                  -> '12,332'

`VERSION()'
     Returns a string indicating the MySQL server version:

          mysql> select VERSION();
                  -> '3.23.13-log'

     Note that if your version ends with `-log' this means that logging
     is enabled.

`CONNECTION_ID()'
     Returns the connection id (`thread_id') for the connection.  Every
     connection has its own unique id:

          mysql> select CONNECTION_ID();
                  -> 1

`GET_LOCK(str,timeout)'
     Tries to obtain a lock with a name given by the string `str', with
     a timeout of `timeout' seconds.  Returns `1' if the lock was
     obtained successfully, `0' if the attempt timed out, or `NULL' if
     an error occurred (such as running out of memory or the thread was
     killed with `mysqladmin kill').  A lock is released when you
     execute `RELEASE_LOCK()', execute a new `GET_LOCK()', or the thread
     terminates.  This function can be used to implement application
     locks or to simulate record locks.  It blocks requests by other
     clients for locks with the same name; clients that agree on a
     given lock string name can use the string to perform cooperative
     advisory locking:

          mysql> select GET_LOCK("lock1",10);
                  -> 1
          mysql> select GET_LOCK("lock2",10);
                  -> 1
          mysql> select RELEASE_LOCK("lock2");
                  -> 1
          mysql> select RELEASE_LOCK("lock1");
                  -> NULL

     Note that the second `RELEASE_LOCK()' call returns `NULL' because
     the lock `"lock1"' was automatically released by the second
     `GET_LOCK()' call.

`RELEASE_LOCK(str)'
     Releases the lock named by the string `str' that was obtained with
     `GET_LOCK()'. Returns `1' if the lock was released, `0' if the
     lock wasn't locked by this thread (in which case the lock is not
     released), and `NULL' if the named lock didn't exist.  The lock
     will not exist if it was never obtained by a call to `GET_LOCK()'
     or if it already has been released.

     The `DO' statement is convinient to use with `RELEASE_LOCK()'.
     Note: DO.

`BENCHMARK(count,expr)'
     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 in 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 may be advisable to execute `BENCHMARK()'
     several times, and interpret the result with regard to how heavily
     loaded the server machine is.

`INET_NTOA(expr)'
     Returns the network address (4 or 8 byte) for the numeric
     expression:

          mysql> select INET_NTOA(3520061480);
                 ->  "209.207.224.40"

`INET_ATON(expr)'
     Returns an integer that represents the numeric value for a network
     address.  Addresses may be 4 or 8 byte addresses:

          mysql> select INET_ATON("209.207.224.40");
                 ->  3520061480

     The generated number is always in network byte order; For example
     the above number is calculated as `209*256^3 + 207*256^2 + 224*256
     +40'.

`MASTER_POS_WAIT(log_name, log_pos)'
     Blocks until the slave reaches the specified position in the
     master log during replication. If master information is not
     initialized, returns NULL. If the slave is not running, will block
     and wait until it is started and goes to or past the specified
     position. If the slave is already past the specified position,
     returns immediately. The return value is the number of log events
     it had to wait to get to the specified position, or NULL in case
     of error. Useful for control of master-slave synchronization, but
     was originally written to facilitate replication testing.


automatically generated by info2www version 1.2.2.9