mysql other functions

1. Bit functions

For bit operations, MySQL uses the BIGINT (64-bit) algorithm, so the maximum range of these operators is 64 bits.

|

Bitwise OR:

mysql> SELECT 29 | 15;

-> 31

The result is a 64-bit unsigned integer.

&

Bitwise AND:

mysql> SELECT 29 & 15;

-> 13

The result is a 64-bit unsigned integer.

^

Bitwise XOR:

mysql> SELECT 1 ^ 1;

-> 0 ; SELECT 11 ^ 3;

- > 8

The result is a 64-bit unsigned integer.

<<

Shift a longlong (BIGINT) number to the left by two places.

mysql> SELECT 1 << 2;

-> 4

The result is a 64-bit unsigned integer.

>>

Shift a longlong (BIGINT) number to the right by two places.

mysql> SELECT 4 >> 2;

-> 1

The result is a 64-bit unsigned integer.

~

Invert all bits.

mysql> SELECT 5 & ~1;

-> 4

The result is a 64-bit unsigned integer.

BIT_COUNT(N)

Returns the number of bits set in parameter N

mysql> SELECT BIT_COUNT(29);

-> 4

2. Encryption function

This section introduces encryption and encrypted values. If you want to store some results returned by cryptographic functions that may contain arbitrary byte values, use BLOB columns instead of CHAR or VARCHAR columns to avoid the potential problem of changing some data values due to the removal of trailing spaces.

AES_ENCRYPT(str,key_str) , AES_DECRYPT(crypt_str,key_str)

These functions allow encryption and data encryption using the official AES (Advanced Encryption Standard) algorithm, formerly known as "Rijndael". The length of the secret key is 128 bits, but you can extend it to 256 bits by changing the source. We chose 128 bit because it is much faster and this level of confidentiality is sufficient for most purposes.

Input parameters can be of any length. If any parameter is NULL, the result of the function is also NULL.

Because AES is a block-level algorithm, padding is used to encode unbalanced length strings, so the length of the resulting string is 16 * (trunc(string_length / 16) + 1).

If AES_DECRYPT() detects invalid data or incorrect padding, it will return NULL. However, if the entered information or password is invalid, AES_DECRYPT() may return a non-NULL value (which may be useless information).

You can use AES functions to store data in encrypted form by modifying your query:

INSERT INTO t VALUES (1,AES_ENCRYPT('text','password'));

AES_ENCRYPT() and AES_DECRYPT () can be regarded as the most secure encryption function for universal passwords in MySQL.

DECODE(crypt_str,pass_str)

Use pass_str as the password to decrypt the encrypted string crypt_str, crypt_str should be the string returned by ENCODE().

ENCODE(str, pass_str)

Use pass_str as password to decrypt str. Use DECODE() to decrypt the result.

The result is a binary string with the same length as str. If you want to keep it in a column, use the BLOB column type.

DES_DECRYPT(crypt_str[,key_str])

Use DES_ENCRYPT() to encrypt a string. If an error occurs, this function returns NULL.

Note that this function will only work when MySQL is configured with SSL support.

If the key_str parameter is not given, DES_DECRYPT() will first check the first byte of the encrypted string to determine the DES password key number used to encrypt the original string, and then read the key from the DES key file words to decrypt the information. In order for it to run, the user must have SUPER privileges. You can choose --des-key-file server to specify the key file.

If you pass a key_str parameter to this function, the string is used as the key to decrypt the message.

If the crypt_str parameter does not appear to be an encrypted string, MySQL will return the given crypt_str.

DES_ENCRYPT(str[,(key_num|key_str)])

Encrypt the string using the keywords given by the Triple-DES algorithm. If an error occurs, this function returns NULL.

Note that this function will only run after MySQL is configured with SSL support.

The selection of the encryption keyword used is based on the second parameter to DES_ENCRYPT(), if given:

Parameter Description

No parameters Use the first keyword from the DES keyword file.

key_num uses the key number (0-9) given by the DES keyword file.

key_str Encrypts str using the given key string.

Select --des-key-file server to specify the keyword file.

The returned string is a binary string, where the first character is CHAR(128 | key_num).

Plus 128 makes it easier to identify encrypted keywords. If you use a string key, key_num is 127.

The resulting string length is new_len = orig_len + (8-(orig_len % 8))+1.

Each line in the DES keyword file has the following format:

key_num des_key_str

Each key_num must be a number ranging from 0 to 0. The order of lines in the file is arbitrary. des_key_str is the string used to encrypt information. There should be at least one space between the number and the keyword. If you do not specify any keyword arguments to DES_ENCRYPT(), the first keyword is the default keyword used.

Using the FLUSH DES_KEY_FILE statement, you can let MySQL read new keyword values from the keyword file. This requires you to have the RELOAD privilege.

One benefit of having a default set of keywords is that it provides applications with a way to verify encrypted column values without providing the end user with the ability to decrypt those values.

mysql> SELECT customer_address FROM customer_table

> WHERE crypted_credit_card = DES_ENCRYPT('credit_card_number');

ENCRYPT(str[,salt])

Use the Unix crypt() system call to encrypt str. The salt parameter should be a string containing at least 2 characters. If the salt parameter is not given, any value is used.

mysql> SELECT ENCRYPT('hello');

-> 'VxuFAJXVARROc'

At least on some systems, ENCRYPT() ignores everything except the first eight characters of str. This behavior is determined by the execution of the underlined crypt() system call.

If crypt() is not available on your system (as it is on Windows), ENCRYPT() will always return NULL. For this reason, we recommend you to use MD5() or SHA1() instead, as these two functions are suitable for all platforms.

MD5(str)

Computes an MD5 128-bit checksum for a string. The value is returned as a binary string of 32 hexadecimal digits. If the parameter is NULL, NULL will be returned. For example, the return value can be used as a hash key.

mysql> SELECT MD5('testing'); letters, see the binary string conversion given in the BINARY operator item in "Cast Functions and Operators".

OLD_PASSWORD(str)

When the implementation of PASSWORD() is changed to improve security, OLD_PASSWORD() will be added to MySQL. OLD_PASSWORD() returns the value of the old PASSWORD() implementation (pre-4.1), while allowing you to set passwords for any pre-4.1 clients that need to connect to your 5.1 MySQL server without cutting them off.

PASSWORD(str)

Calculate and return the password string from the original password str. When the parameter is NULL, NULL is returned. This function is used to store encrypted MySQL passwords in the Password column of the user authorization table.

mysql> SELECT PASSWORD('badpwd');

PASSWORD() performs password encryption differently from how Unix passwords are encrypted. See ENCRYPT().

Note: The PASSWORD() function is used by the authentication system in MySQL Server; you should not use it in your personal applications. To achieve the same purpose, MD5() or SHA1() can be used instead. For more information on handling passwords and authentication in your application, see RFC 2195

SHA1(str) SHA(str)

Computes a SHA1 160-bit checksum for a string, as in RFC 3174 (Secure Hash Algorithm ) as stated in. The value is returned as a 40-digit hexadecimal number, or NULL when the argument is NULL. One possible use of this function is as a hash key. You can also use it as a password-safe function for storing passwords.

mysql> SELECT SHA1('abc'); SHA() and SHA1() have the same meaning.

3. Information function

BENCHMARK(count,expr)

BENCHMARK() function repeats count times to execute expression expr. It can be used to calculate how quickly MySQL processes expressions. The resulting value is usually 0. Another use comes from within the mysql client, which can report the number of query executions:

mysql> SELECT BENCHMARK(1000000,ENCODE('hello','goodbye'));

+----------------------------------------------+ | BENCHMARK(1000000,ENCODE('hello','goodbye')) | +----------------------------------------------+ | 0 | +----------------------------------------------+ 1 row in set (4.74 sec)

The time reported here is the shared time on the client, not the CPU time on the server. It is recommended to execute BENCHMARK() multiple times and interpret the results in relation to the load on the server machine.

·     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)

Returns the integer compressibility 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 value has the following meaning:

Compressibility Meaning Example

0 Explicit sorting Value with COLLATE clause

1 No sorting String concatenation with different sorting

2 Explicit sorting Column value

3 System Constant USER() return value

4 Compressible Literal string

5 Ignoreable NULL or an expression obtained by NULL The value below

has a higher priority.

COLLATION(str)

Returns the sorting method of string parameters.

mysql> SELECT COLLATION('abc');

-> 'latin1_swedish_ci'

mysql> SELECT COLLATION(_utf8'abc');

-> 'utf8_general_ci'

CONNE CTION_ID()

returns for The connection ID (thread ID) of the connection. Each connection has its own unique ID.

mysql> SELECT CONNECTION_ID();

-> 23786

CURRENT_USER, CURRENT_USER()

Returns the verified username and hostname combination of the current session. This value corresponds to the MySQL account that determines your access rights. Within a stored procedure that is assigned the SQL SECURITY DEFINER characteristic, CURRENT_USER() returns the creator of the procedure.

The value of CURRENT_USER() can be different from the value of USER().

mysql> SELECT USER (); mysql'

mysql> SELECT CURRENT_USER();

-> '@localhost'

This example explains that although the client specified a davida username (as indicated by the value of the USER() function), the server Use an anonymous user account to identify the client (see the empty username section of CURRENT_USER() ). One reason this happens is that One does not have enough accounts in the authorization list to davida.

CURRENT_USER() The string returned uses the utf8 character set.

DATABASE()

Returns the default (current) database name using the utf8 character set. In a stored procedure, the default database is the database associated with the procedure, but is not necessarily the same as the default database in the calling context.

mysql> SELECT DATABASE();

-> 'test'

If there is no default database, DATABASE() returns NULL.

FOUND_ROWS()

A SELECT statement may include a LIMIT clause to limit the number of rows returned by the server to the client. In some cases, it is necessary to know how many rows the statement returned without LIMIT without running the statement again. To know this number of rows, include selecting SQL_CALC_FOUND_ROWS in the SELECT statement and then calling FOUND_ROWS():

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name

-> WHERE id > 100 LIMIT 10;

mysql > SELECT FOUND_ROWS() ;

The second SELECT returns a number indicating how many rows the first SELECT returned without the LIMIT clause (if the above SELECT statement does not include the SQL_CALC_FOUND_ROWS option, LIMIT is used and FOUND_ROWS is not used) () may return different results).

The effective number of rows passed by FOUND_ROWS() is instantaneous and is not used to skip statements following the SELECT SQL_CALC_FOUND_ROWS statement. If you need to refer to this value later, then 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 There are fewer rows in the total result set. However, this is faster than running the query again without LIMIT because the result set does not need to be sent to the client.

SQL_CALC_FOUND_ROWS 和 FOUND_ROWS() 在当你希望限制一个问询返回的行数时很有用，同时还能不需要再次运行问询而确定全部结果集合中的行数。一个例子就是提供页式显示的Web脚本，该显示包含显示搜索结果其它部分的页的连接。使用FOUND_ROWS() 使你确定剩下的结果需要多少其它的页。

SQL_CALC_FOUND_ROWS 和 FOUND_ROWS() 的应用对于UNION 问询比对于简单SELECT 语句更为复杂，原因是在UNION 中，LIMIT 可能会出现在多个位置。它可能适用于UNION中的个人 SELECT语句，或是总体上 到UNION 结果的全程。

SQL_CALC_FOUND_ROWS对于 UNION的意向是它应该不需要全程LIMIT而返回应返回的行数。SQL_CALC_FOUND_ROWS 和UNION 一同使用的条件是：

SQL_CALC_FOUND_ROWS 关键词必须出现在UNION的第一个 SELECT中。

FOUND_ROWS()的值只有在使用 UNION ALL时才是精确的。若使用不带ALL的UNION，则会发生两次删除， 而 FOUND_ROWS() 的指只需近似的。

假若UNION 中没有出现 LIMIT ，则SQL_CALC_FOUND_ROWS 被忽略，返回临时表中的创建的用来处理UNION的行数。

LAST_INSERT_ID() LAST_INSERT_ID(expr)

自动返回最后一个INSERT或 UPDATE 问询为 AUTO_INCREMENT列设置的第一个 发生的值。

mysql> SELECT LAST_INSERT_ID();

-> 195

产生的ID 每次连接后保存在服务器中。这意味着函数向一个给定客户端返回的值是该客户端产生对影响AUTO_INCREMENT列的最新语句第一个 AUTO_INCREMENT值的。这个值不能被其它客户端影响，即使它们产生它们自己的 AUTO_INCREMENT值。这个行为保证了你能够找回自己的 ID 而不用担心其它客户端的活动，而且不需要加锁或处理。

假如你使用一个非“magic”值来更新某一行的AUTO_INCREMENT 列，则LAST_INSERT_ID() 的值不会变化(换言之, 一个不是 NULL也不是 0的值)。

重点: 假如你使用单INSERT语句插入多个行， LAST_INSERT_ID() 只返回插入的第一行产生的值。其原因是这使依靠其它服务器复制同样的 INSERT语句变得简单。

例如：

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)

虽然第二个问询将3 个新行插入 t, 对这些行的第一行产生的 ID 为 2, 这也是 LAST_INSERT_ID()返回的值。

假如你使用 INSERT IGNORE而记录被忽略，则AUTO_INCREMENT 计数器不会增量，而 LAST_INSERT_ID() 返回0,这反映出没有插入任何记录。

若给出作为到LAST_INSERT_ID()的参数expr ，则参数的值被函数返回，并作为被LAST_INSERT_ID()返回的下一个值而被记忆。这可用于模拟序列：

创建一个表，用来控制顺序计数器并使其初始化：

o mysql> CREATE TABLE sequence (id INT NOT NULL);

o mysql> INSERT INTO sequence VALUES (0);

使用该表产生这样的序列数 ：

o mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);

o mysql> SELECT LAST_INSERT_ID();

UPDATE 语句会增加顺序计数器并引发向LAST_INSERT_ID() 的下一次调用，用来返回升级后的值。 SELECT 语句会检索这个值。 mysql_insert_id() C API函数也可用于获取这个值。

你可以不用调用LAST_INSERT_ID()而产生序列，但这样使用这个函数的效用在于 ID值被保存在服务器中，作为自动产生的值。它适用于多个用户，原因是多个用户均可使用 UPDATE语句并用SELECT语句(或mysql_insert_id()),得到他们自己的序列值，而不会影响其它产生他们自己的序列值的客户端或被其它产生他们自己的序列值的客户端所影响。

注意， mysql_insert_id() 仅会在INSERT 和UPDATE语句后面被升级， 因此你不能在执行了其它诸如SELECT或 SET 这样的SQL语句后使用 C API 函数来找回 LAST_INSERT_ID(expr) 对应的值。

ROW_COUNT()

ROW_COUNT() returns the number of rows upgraded, inserted or deleted by the previous statement. This number of rows is the same as the number of rows displayed by the mysql client and the value returned by the mysql_affected_rows() C API function.

mysql> INSERT INTO t VALUES(1),(2),(3);

Query completed, there are 3 rows in the table (0.00 seconds)

Records: 3 Repeats: 0 Warnings: 0

mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| +-------------+

| ROW_COUNT() |
+---------- --+
| 2 |
+-------------+

-> 1

mysql> 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 unlocked by the second GET_LOCK() call.

gives a point address representation of a network address as a string, returning an integer representing the value of the address. The address can be a 4 or 8 bit address.

mysql> SELECT INET_ATON('209.207.224.40');

-> 3520061480

The numbers generated are always in network byte order. As in the above example, the number is calculated as 209×2563 + 207×2562 + 224×256 + 40.

INET_ATON() also understands short-form IP addresses:

mysql> SELECT INET_ATON('127.0.0.1'), INET_ATON('127.1');
-> 2130706433, 2130706433

Notes: In storage by INET_ATON( ), it is recommended that you use INT UNSIGNED columns. If you use a (signed) INT column, the corresponding IP address values whose first octet is greater than 127 will be ended at 2147483647 (i.e., the value returned by INET_ATON('127.255.255.255')).

INET_NTOA(expr)

Given a numeric network address (4 or 8 bits), returns the electrical address representation of the address as a string.

mysql> SELECT INET_NTOA(3520061480);
-> '209.207.224.40'

IS_FREE_LOCK(str)

Check whether the lock named str can be used (in other words, it is not blocked). If the lock can be used, it returns 1 (no one is using this lock), if the lock is being used, it returns 0, and if an error occurs, it returns NULL (such as incorrect parameters).

IS_USED_LOCK(str)

Checks whether the lock named str is in use (in other words, blocked). If blocked, returns the connection identifier of the client using the lock. Otherwise return NULL.

MASTER_POS_WAIT(log_name,log_pos[,timeout])

This function is very useful for controlling master-slave synchronization. It will remain blocked until all supplementary information in the host record has been read and applied from the device to the specified location. The return value is the number of logged events it must wait to reach the specified location. If the slave SQL thread has not been started, the slave host information has not been initialized, the parameters are incorrect, or any error occurs, the function returns NULL. If the timeout is exceeded, -1 is returned. If the slave SQL thread is terminated while MASTER_POS_WAIT() is waiting, this function returns NULL. If the slave device passes through the specified location, the function will return the result immediately.

If a timeout value has been specified, MASTER_POS_WAIT() will stop waiting when the timeout seconds elapse. The timeout must be greater than 0; a zero or negative timeout indicates no supermarket time.

NAME_CONST(name,value)

Returns the given value. When used to produce a result set column, NAME_CONST() causes the column to use the given name.

mysql> SELECT NAME_CONST('myname', 14);

+--------+
| myname |
+--------+
| 14 |
+--- -----+

This function was added in MySQL 5.0.12. It is for internal use only. You may see this function in the mysqlbinlog bookcase.

RELEASE_LOCK(str)

Unlock the lock obtained by GET_LOCK() and named with the string str. If the lock is unlocked, 1 is returned. If the thread has not created the lock, 0 is returned (the lock has not been unlocked at this time). If the named lock does not exist, NULL is returned. If the lock has never been acquired by a call to GET_LOCK(), or the lock has been unlocked early, the lock does not exist.

DO statement and RELEASE_LOCK() are very convenient to use at the same time.

SLEEP(duration)

Sleep (pause) for the number of seconds given by the duration parameter, and then return 0. If SLEEP() is interrupted, it returns 1. duration may or may include a given fractional part in microseconds.

UUID()

Returns a Universally Unique Identifier (UUID), which is generated based on the instructions of "DCE 1.1: Remote Procedure Call" (Appendix A) CAE (Common Application Environment), which was published in October 1997 Published by The Open Group (File No. C706, http://www.opengroup.org/public/pubs/catalog/c706.htm).

UUID is designed to be a number that is unique in time and space. Two calls to UUID() should produce two different values, even if the calls are executed on two separate computers that are not connected to each other.

UUID is a 128-bit number represented by a string of 5 hexadecimal digits in the format aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:

The first 3 digits are generated from a timestamp.

The 4th number maintains temporary uniqueness in case the timestamp value loses its uniqueness (e.g. due to economic times).

The 5th number is an IEEE 802 node number, which provides spatial uniqueness. If the latter is not available, it is replaced with a random number. (For example, because the host machine does not have an Ethernet card, or we do not know how to find the machine address of the interface on your operating system). If this is the case, spatial uniqueness cannot be guaranteed. Still, the chance of a conflict occurring is very low.

Currently, the MAC address of an interface is taken into account by FreeBSD and Linux. In other operating systems, MySQL uses randomly generated 48-bit numbers.

mysql> SELECT UUID();

-> '6ccd780c-baba-1026-9564-0040f4311e29'

Note that UUID() does not support the copy function.

VALUES(col_name)

In an INSERT … ON DUPLICATE KEY UPDATE … statement, you can use the VALUES(col_name) function in the UPDATE clause to access the column values from the INSERT part of the statement. In other words, VALUES(col_name) in the UPDATE clause accesses the value of col_name that needs to be inserted, and no duplicate key conflict will occur. This function is particularly useful in multi-row insertions. The VALUES() function is only meaningful in INSERT ... UPDATE statements, and will only return NULL in other cases.

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)

-> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b) ;