Copyright (C) 2000-2012 |
GNU Info (mysql.info)Miscellaneous functionsMiscellaneous 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 |