GNU Info
(mysql.info)Miscellaneous functions
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