MySQL特殊函数

来源：互联网发布：用c语言编写计算器程序编辑：程序博客网时间：2024/06/05 05:39

1.有效数字保留函数FORMAT（x,n）
将数字x格式化，并以四舍五入的方式保留小数点后n位，结果以字符串返回，如果n=0，则返回结果不包含小数。

mysql> select format(1232.123456, 4),    -> format(12332.1,4),    -> format(12323.2,0);+------------------------+-------------------+-------------------+| format(1232.123456, 4) | format(12332.1,4) | format(12323.2,0) |+------------------------+-------------------+-------------------+| 1,232.1235             | 12,332.1000       | 12,323            |+------------------------+-------------------+-------------------+1 row in set (0.01 sec)

2.不同进制的数字进行转换的函数
CONV(N,from_base,to_base)，返回值为数值N的字符串表示，由from_base进制转化为to_base进制。如果有任意一个参数为NULL，则返回值为NULL。

mysql> select conv('a',16,2),    -> conv(15,10,2),    -> conv(15,10,8),    -> conv(15,10,16);+----------------+---------------+---------------+----------------+| conv('a',16,2) | conv(15,10,2) | conv(15,10,8) | conv(15,10,16) |+----------------+---------------+---------------+----------------+| 1010           | 1111          | 17            | F              |+----------------+---------------+---------------+----------------+1 row in set (0.01 sec)

3.IP地址与数字相互转换函数INET_ATON和INET_NTOA.

mysql> select inet_aton('209.207.224.40');+-----------------------------+| inet_aton('209.207.224.40') |+-----------------------------+|                  3520061480 |+-----------------------------+1 row in set (0.00 sec)mysql> select inet_ntoa(3520061480);+-----------------------+| inet_ntoa(3520061480) |+-----------------------+| 209.207.224.40        |+-----------------------+1 row in set (0.00 sec)

4.加锁解锁函数GET_LOCK(str,timeout),RELEASE_LOCK(str),IS_FREE_LOCK(str),IS_USED_LOCK(str)
GET_LOCK(str,timeout):使用字符串str给定的名字得到一个锁，超时为timeout秒。若成功得到锁，则返回1；若操作超时，返回0；发生错误返回NULL。
RELEASE_LOCK(str)：解锁。
IS_FREE_LOCK(str)：判断锁是否可用。
IS_USED_LOCK(str)：判断锁是否被正在使用。

mysql> select get_lock('lock1',10) as getlock,    -> is_used_lock('lock1') as isusedlock,    -> is_free_lock('lock1') as isfreelock,    -> release_lock('lock1') as releaselock;+---------+------------+------------+-------------+| getlock | isusedlock | isfreelock | releaselock |+---------+------------+------------+-------------+|       1 |         16 |          0 |           1 |+---------+------------+------------+-------------+1 row in set (0.00 sec)

5.重复执行指定的操作BENCHMARK
BENCHMARK(count,expr)函数重复count次执行表达式expr，它报告的是客户端经过的时间，而不是在服务器端的CPU时间。

mysql> select benchmark(50000,password('newpwd'));+-------------------------------------+| benchmark(50000,password('newpwd')) |+-------------------------------------+|                                   0 |+-------------------------------------+1 row in set (0.05 sec)

6.改变字符集函数CONVERT()

mysql> select charset('string'), charset(convert('string' using utf8));+-------------------+---------------------------------------+| charset('string') | charset(convert('string' using utf8)) |+-------------------+---------------------------------------+| latin1            | utf8                                  |+-------------------+---------------------------------------+1 row in set (0.00 sec)

7.改变数据类型的函数CAST(x,AS type)和CONVERT(x,type)

mysql> select cast(100 as char(2)),convert('2010-10-01 12:12:12', time);+----------------------+--------------------------------------+| cast(100 as char(2)) | convert('2010-10-01 12:12:12', time) |+----------------------+--------------------------------------+| 10                   | 12:12:12                             |+----------------------+--------------------------------------+1 row in set, 1 warning (0.00 sec)

0 0