MySQL函数集
- 绝对值函数ABS(x),返回x的绝对值。
- 返回圆周率的函数PI()
- 平方根函数SQRT(x)
- 求余函数MOD(x,y)
- 获取整数的函数CEIL(x)、CEILING(x)和FLOOR(x)
select ceil(-3.35),ceiling(3.35),floor(3.35);
输出:
-3 4 3
获取随机数的函数RAND()和RAND(X)
rand()和rand(x)都会返回一个随机浮点值v,范围在0到1之间(即0<=v<=1.0).两者的区别是rand()不指定种子,所以返回的结果是不同,rand(x)会以x作为种子值,如果种子相同,返回的是相同的.mysql> select rand(),rand(),rand(10),rand(10),rand(11); +--------------------+--------------------+--------------------+--------------------+-------------------+ | rand() | rand() | rand(10) | rand(10) | rand(11) | +--------------------+--------------------+--------------------+--------------------+-------------------+ | 0.9583094985767356 | 0.5364463483322843 | 0.6570515219653505 | 0.6570515219653505 | 0.907234631392392 | +--------------------+--------------------+--------------------+--------------------+-------------------+ 1 row in set (0.00 sec) mysql> select rand(),rand(),rand(10),rand(10),rand(11); +--------------------+--------------------+--------------------+--------------------+-------------------+ | rand() | rand() | rand(10) | rand(10) | rand(11) | +--------------------+--------------------+--------------------+--------------------+-------------------+ | 0.8073032766648599 | 0.4271773690610913 | 0.6570515219653505 | 0.6570515219653505 | 0.907234631392392 | +--------------------+--------------------+--------------------+--------------------+-------------------+ 1 row in set (0.00 sec)
- 返回字符数char_length, length返回字节数,并非是按照utf8返回.
select char_length('字符个数'), length('字符集');
+-------------------------+-------------------+
| char_length('字符个数') | length('字符集i') |
+-------------------------+-------------------+
| 4 | 7 |
+-------------------------+-------------------+
- 合并字符串concat(s1,s2,…)/concat(x,s1,s2).
第二个采用字符分割起来
mysql> select concat('字符串1','子浮窗2'),concat_ws('********','字符串1','字符串2');
+-----------------------------+-------------------------------------------+
| concat('字符串1','子浮窗2') | concat_ws('********','字符串1','字符串2') |
+-----------------------------+-------------------------------------------+
| 字符串1子浮窗2 | 字符串1********字符串2 |
+-----------------------------+-------------------------------------------+
1 row in set (0.00 sec)
- 替换字符串函数insert(s1,x,len,s2),从s1的x位置起,替换长度为len的s2。这里和oracle不同的是,使用负值直接回返回原字符串。
mysql> select INSERT('Quest',2,4,'What') As coll,INSERT('Quest',-1,4,'What') as col2,INSERT('Quest',1,4,'what');
+-------+-------+----------------------------+
| coll | col2 | INSERT('Quest',1,4,'what') |
+-------+-------+----------------------------+
| QWhat | Quest | whatt |
+-------+-------+----------------------------+
- 字符转成小写LOWER,LCASE,转成大写使用Upper()或UCASE
select lower('BEAUTIFUL');
- 获取指定长度的字符串的函数LEFT(s,n)和RIGHT(s,n)
mysql> select left('football',4),right('football',4);
+--------------------+---------------------+
| left('football',4) | right('football',4) |
+--------------------+---------------------+
| foot | ball |
+--------------------+---------------------+
1 row in set (0.00 sec)
- 填充字符lpad和rpad,和oracle的一样
- 删除空格函数,LTRIM(),RTRIM()和trim,和oracle的一样
- 删除指定字符串trim(s1 from s),这个函数的作用是删除字符串s中两端所有的s1。s1可省略,但是要表示成”否则会报错,默认删除空格。
mysql> select trim('' from ' ddddccc '),trim('xy' from 'axybxyxy'),trim('xy' from 'xybxyxc'),trim('xy' from 'xyxyxybbbbxyyx');
+----------------------------+----------------------------+---------------------------+----------------------------------+
| trim('' from ' ddddccc ') | trim('xy' from 'axybxyxy') | trim('xy' from 'xybxyxc') | trim('xy' from 'xyxyxybbbbxyyx') |
+----------------------------+----------------------------+---------------------------+----------------------------------+
| ddddccc | axyb | bxyxc | bbbbxyyx |
+----------------------------+----------------------------+---------------------------+----------------------------------+
1 row in set (0.04 sec)
- 重复生成字符串repeat(s1,n).如果n<=0,返回一个空字符串。如果是s1为null,返回null。
mysql> select repeat('MySQL',2);
+-------------------+
| repeat('MySQL',2) |
+-------------------+
| MySQLMySQL |
+-------------------+
1 row in set (0.00 sec)
- 空格函数space(n),返回一个n个空格组成的字符串
mysql> select concat(';',space(5),';') ;
+--------------------------+
| concat(';',space(5),';') |
+--------------------------+
| ; ; |
+--------------------------+
1 row in set (0.05 sec)
- replace(s,s1,s2)将s中所有的s1替换为s2,和oracle中的replace是一样的.
- 字符串比较大小函数,strcmp(s1,s2),比较大小,返回0,-1,1.这个没有说是根据什么进行比较.
mysql> select strcmp('a','b');
+-----------------+
| strcmp('a','b') |
+-----------------+
| -1 |
+-----------------+
1 row in set (0.02 sec)
- 截取字符串函数,substring(s,n1,len)或mid(s,n1,len),与oracle的作用相同.
- 查找字符串的位置LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str, str1),都是返回str1在str的位置。这个和oracle的instr作用是一样的,并且如果使用instr,用法也一样。
- 逆转顺序函数reverse(s),返回以s依序排列的字符串.
mysql> select reverse('abcd');
+-----------------+
| reverse('abcd') |
+-----------------+
| dcba |
+-----------------+
1 row in set (0.00 sec)
- 返回指定位置的字符串ELT(n,str1,str2,str3….strn),返回第n个位置的str,如果N小于1或者大于所有字符串的个数,那么会返回Null。
mysql> select elt(2,'a','ccc','cdcd');
+-------------------------+
| elt(2,'a','ccc','cdcd') |
+-------------------------+
| ccc |
+-------------------------+
- 返回字符串位置的函数FIND_IN_SET(s1,s2)
mysql> select FIND_IN_SET('aa','cd,aa,cdcd');
+--------------------------------+
| FIND_IN_SET('aa','cd,aa,cdcd') |
+--------------------------------+
| 2 |
+--------------------------------+
选取字符串的函数MAKE_SET(x,s1,s2..)返回由x的二进制数指定的相应的字符串组成的字符串,也就是将x表示成二进制,然后将对应为1的字符串取出来,形成set字符串.
mysql> select make_set(1, 'x','y','z'); +--------------------------+ | make_set(1, 'x','y','z') | +--------------------------+ | x | +--------------------------+ 1 row in set (0.00 sec) mysql> select make_set(5, 'x','y','z'); +--------------------------+ | make_set(5, 'x','y','z') | +--------------------------+ | x,z | +--------------------------+ 1 row in set (0.00 sec)
- 获取当前日期函数CURDATE()或CURRENT_DATE(),返回格式为YYYYMMDD或YYYY-MM-DD格式的值.
mysql> select CURDATE(),CURRENT_DATE();
+------------+----------------+
| CURDATE() | CURRENT_DATE() |
+------------+----------------+
| 2016-04-26 | 2016-04-26 |
+------------+----------------+
1 row in set (0.00 sec)
- 获取当前日期和时间CURRENT_TIMESTAMP(),LOCALTIME(),NOW()和SYSDATE()4个函数的作用相同,都是返回当前日期和时间值,格式为YYYY-MM-DD HH:MM:SS或YYYYMMDDHHMMSS
mysql> select CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE();
+---------------------+---------------------+---------------------+---------------------+
| CURRENT_TIMESTAMP() | LOCALTIME() | NOW() | SYSDATE() |
+---------------------+---------------------+---------------------+---------------------+
| 2016-04-26 11:33:03 | 2016-04-26 11:33:03 | 2016-04-26 11:33:03 | 2016-04-26 11:33:03 |
+---------------------+---------------------+---------------------+---------------------+
- 时间戳和日期格式数据相互转换.unix_timestamp(date)返回时间戳,from_unixtime(timestamp)返回时间.
mysql> select unix_timestamp(now()),unix_timestamp(now()-1),now()-1;
+-----------------------+-------------------------+----------------+
| unix_timestamp(now()) | unix_timestamp(now()-1) | now()-1 |
+-----------------------+-------------------------+----------------+
| 1461644926 | 1461644925 | 20160426122845 |
+-----------------------+-------------------------+----------------+
mysql> select from_unixtime(unix_timestamp());
+---------------------------------+
| from_unixtime(unix_timestamp()) |
+---------------------------------+
| 2016-04-26 12:31:12 |
+---------------------------------+
1 row in set (0.04 sec)
- 获取日期类型数据的MONTH(DATE)和MonthName(data)返回日期中的月份。
mysql> select month(now());
+--------------+
| month(now()) |
+--------------+
| 4 |
+--------------+
1 row in set (0.00 sec)
- 获取星期的函数DAYNAME(d),DAYOFWEEK(d)和WEEKDAY(d),都会返回星期内的天。不同的是DAYNAME返回的是英文字符,DAYOFWEEK返回以1表示周日的索引编号,WEEKDAY(d)返回以0位周1的索引。
mysql> select dayname('2016-04-26'), dayofweek('2016-04-26'),weekday('2016-04-26');
+-----------------------+-------------------------+-----------------------+
| dayname('2016-04-26') | dayofweek('2016-04-26') | weekday('2016-04-26') |
+-----------------------+-------------------------+-----------------------+
| Tuesday | 3 | 1 |
+-----------------------+-------------------------+-----------------------+
1 row in set (0.00 sec)
- 获得本年的第几个星期.week(d)或weekofyear(d).week其实支持双参数的,即week(d,n),关于n的模式可以去查看。weekofyear(n)相当于weed(d,3).
- 获取当月的第几天或者当前的第几天.dayofyear(d),dayofmonth(d)
mysql> select dayofyear('2016-04-26'),dayofmonth('2014-04-26');
+-------------------------+--------------------------+
| dayofyear('2016-04-26') | dayofmonth('2014-04-26') |
+-------------------------+--------------------------+
| 117 | 26 |
+-------------------------+--------------------------+
1 row in set (0.04 sec)
- 获取年、嫉妒、小时、分钟和秒数。YAER(d),QUARTER(d),minute(time),second(time)
- extract(type from date),与oracle一样.
- time_to_sec(time)返回已转化为秒的time参数。将时间长度转化成秒.sec_to_time(sec)将秒数转化为小时分钟秒数的时间.
- 时间加减函数。date_add(date, Interval expr type),date_sub(date, Interval expr type).ADDTIME(time,time_expr),SUBTIME(time,time_expr).datediff(date1,date2)返回起始于date1到date2的天数,oracle是返回小数的,这里只返回整数。
mysql> select date_add('2016-04-26 13:01:33', Interval -1 day), date_sub('2016-04-26 13:34:01', Interval 1 day);
+--------------------------------------------------+-------------------------------------------------+
| date_add('2016-04-26 13:01:33', Interval -1 day) | date_sub('2016-04-26 13:34:01', Interval 1 day) |
+--------------------------------------------------+-------------------------------------------------+
| 2016-04-25 13:01:33 | 2016-04-25 13:34:01 |
+--------------------------------------------------+-------------------------------------------------+
1 row in set (0.05 sec)
mysql> select addtime('2016-04-26 13:37:00','01:00:00'),subtime('2016-04-26 13:38:33', '01:32:09');
+-------------------------------------------+--------------------------------------------+
| addtime('2016-04-26 13:37:00','01:00:00') | subtime('2016-04-26 13:38:33', '01:32:09') |
+-------------------------------------------+--------------------------------------------+
| 2016-04-26 14:37:00 | 2016-04-26 12:06:24 |
+-------------------------------------------+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select datediff('2016-04-26 13:22:00', '2016-04-23'),datediff('2014-04-26 13:30:21', '2014-04-28 13:30:09');
+-----------------------------------------------+--------------------------------------------------------+
| datediff('2016-04-26 13:22:00', '2016-04-23') | datediff('2014-04-26 13:30:21', '2014-04-28 13:30:09') |
+-----------------------------------------------+--------------------------------------------------------+
| 3 | -2 |
+-----------------------------------------------+--------------------------------------------------------+
1 row in set (0.00 sec)
- 条件判断函数if(expr,v1,v2),比较像java中的三目表达式。
mysql> select if(1=0,'abc','cdd');
+---------------------+
| if(1=0,'abc','cdd') |
+---------------------+
| cdd |
+---------------------+
1 row in set (0.00 sec)
- IFNULL(v1,v2)如果v1不为空就返回v1,否则返回v2。相当于oracle中的nvl.
- case when end,和oracle的一致。
系统函数部分:
- version();获取mysql的版本
mysql> select version();
+------------+
| version() |
+------------+
| 5.6.28-log |
+------------+
1 row in set (0.00 sec)
- connecton_id()函数返回服务器连接数.
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 1 |
+-----------------+
1 row in set (0.00 sec)
--processlist可以查看所有连接数(root,普通用户只能看到自己的),连接状态。processlist只能显示前100条,如果要显示全部的话,需要使用show full processlist
mysql> show processlist;
+----+------+-----------------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+-------+------------------+
| 1 | root | localhost:49665 | NULL | Query | 0 | init | show processlist |
+----+------+-----------------+------+---------+------+-------+------------------+
-- 其中各列的说明如下:
id: 登陆MySQL时分配的connect_id
User:当前登录用户
host:显示这个语句发出的ip或host
db:连接的数据库
Command:显示当前连接执行的命令。一般取值为休眠(SLEEP),查询(Query),连接(Connect)。
Time:显示这个状态持续的时间,单位是秒。
state:显示当前连接的sql语句的状态,很重要的列。
info: 显示执行的sql
- 获取当前数据库名.Database()或者schema();
mysql> select database(),schema();
+------------+------------+
| database() | schema() |
+------------+------------+
| daily_used | daily_used |
+------------+------------+
1 row in set (0.00 sec)
- 获取当前登录用户名的函数。user(),current_user(),current_user(),system_user()和session_user(),oracle的是context value中存储,具体的命令是USER,sys_context,userenv()返回。
加密函数:
- password(str),加密字符串。主要用处密码加密,并且加密是单向的,不能解析出来明文。
mysql> select password('abcd'),password('abcd');
+-------------------------------------------+-------------------------------------------+
| password('abcd') | password('abcd') |
+-------------------------------------------+-------------------------------------------+
| *A154C52565E9E7F94BFC08A1FE702624ED8EFFDA | *A154C52565E9E7F94BFC08A1FE702624ED8EFFDA |
+-------------------------------------------+-------------------------------------------+
1 row in set (0.00 sec)
- md5(str)
- encode(str,password_str)/decode(str,password_str),以password_str作为加密和解密的key,进行加密或解密.
mysql> select encode('abccd','ukl'),decode(encode('abccd','ukl'),'ukl');
+-----------------------+-------------------------------------+
| encode('abccd','ukl') | decode(encode('abccd','ukl'),'ukl') |
+-----------------------+-------------------------------------+
| 螶?· | abccd |
+-----------------------+-------------------------------------+
1 row in set (0.00 sec)
其他函数
- 格式化函数format(x,n),将数字x格式化,以四舍五入的方式保留到小数点n位。这里的和oracle不同,oracle对于n为负数的处理是向整数位截取。
mysql> select format(13232.3223, -3),format(13232.3223,3),format(13232.3223,0);
+------------------------+----------------------+----------------------+
| format(13232.3223, -3) | format(13232.3223,3) | format(13232.3223,0) |
+------------------------+----------------------+----------------------+
| 13,232 | 13,232.322 | 13,232 |
+------------------------+----------------------+----------------------+
1 row in set (0.00 sec)
- 不同进制的转换函数conv(n,from_base,to_base),这里比oracle方便,oracle没有这种转换。
- IP地址和数字互相转换。 Inet_aton(expr),inet_ntoa(expr)。具体名称推测为internet address to number/internet number to address
mysql> select inet_aton('192.168.1.24'),inet_ntoa('3232235800');
+---------------------------+-------------------------+
| inet_aton('192.168.1.24') | inet_ntoa('3232235800') |
+---------------------------+-------------------------+
| 3232235800 | 192.168.1.24 |
+---------------------------+-------------------------+
1 row in set (0.00 sec)
- 加锁函数和解锁函数.get_lock(str,timeout)会在timeout内尝试获得一个str名称的锁,成功得到锁返回1,超时返回0,错误返回null。release_lock(str)解锁,解开返回1,未解开返回0,锁名称不存在NULL。is_free_lock(str)检查str的锁是否可以使用,是否处于打开状态。若处于解开状态,返回1,锁正在使用返回0,出现错误返回NULL。is_used_lock(str)检查str的锁是否正在使用,如果是返回正在使用锁的connect_id,否则返回null。
mysql> select get_lock('lock1',10) as get_lock,
-> is_used_lock('lock1') as is_used_lock,
-> is_free_lock('lock1') as is_free_lock,
-> release_lock('lock1') as release_lock,
-> is_free_lock('lock1') as is_free_lock2;
+----------+--------------+--------------+--------------+---------------+
| get_lock | is_used_lock | is_free_lock | release_lock | is_free_lock2 |
+----------+--------------+--------------+--------------+---------------+
| 1 | 1 | 0 | 1 | 1 |
+----------+--------------+--------------+--------------+---------------+
1 row in set (0.00 sec)
- 重复执行函数benchmark(count,expr) 重复执行count次expr,用来统计函数的执行时间,另外一个特定可以知道执行这么多次操作的总体时间。
mysql> select benchmark(100000,password('cdcdcd'));
+------------------------------------+
| benchmark(100000,password('cdcdcd')) |
+------------------------------------+
| 0 | --这里代表执行很快,不是不花时间
+------------------------------------+
1 row in set (0.02 sec),这里是多次执行的总时间
- 改变数据类型函数cast(x as type)和convert(x,type),和oracle的用法很相近,但是根据oracle的经验应该用的不多。