Mysql 函数最全合集

mysql 中的数学函数

2.字符串函数

合并字符串函数 concat_ws(x,s1,s2,…)

mysql> select concat_ws('hello','abc','???');
+--------------------------------+
| concat_ws('hello','abc','???') |
+--------------------------------+
| abchello???                    |
+--------------------------------+
1 row in set (0.00 sec)

填充字符串函数 lpad(s1,len,s2) , rpad(s1,len,s2)

!! lpad(s1,len,s2)函数把字符串 s2 填充到 s1 的开始处，使字符串长度达到 len;

!! rpad(s1,len,s2)函数把字符串 s2 填充到 s1 的结尾处，使字符串长度达到 len;

mysql> select lpad('hello',10,'???');
+------------------------+
| lpad('hello',10,'???') |
+------------------------+
| ?????hello             |
+------------------------+
1 row in set (0.00 sec)

mysql> select rpad('hell0',10,'???');
+------------------------+
| rpad('hell0',10,'???') |
+------------------------+
| hell0?????             |
+------------------------+
1 row in set (0.00 sec)

ascii(str)
返回字符串str的第一个字符的ascii值(str是空串时返回0)

mysql> select ascii('2');  
　　-> 50  
mysql> select ascii(2);  
　　-> 50  
mysql> select ascii('dete');  
　　-> 100 

ord(str):
如果字符串str句首是单字节返回与ascii()函数返回的相同值
如果是一个多字节字符,以格式返回((first byte ascii code)*256+(second byte ascii

code))[*256+third byte asciicode...]

mysql> select ord('2');  
　　-> 50  

conv(n,from_base,to_base)
对from_base进制的数n,转成to_base进制的表示方式
注意：进制范围为2-36进制,当to_base是负数时,n作为有符号数否则作无符号数

mysql> select conv("a",16,2);  
　　-> '1010' 
mysql> select conv("6e",16,10);  
　　-> '110' 
mysql> select conv(-17,16,-10);  
　　-> '-23' 

bin(n)/oct(n)/hex(n)
作用：返回10进制数n对应的二进制/八进制/十六进制的表示方式

mysql> select bin(12);  
　　-> '1100'  
mysql> select oct(12);  
　　-> '14'  
mysql> select hex(255);  
　　-> 'ff' 

char(n,…)
作用：返回多个ascii码对应的字符组成的字符串

mysql> select char(77,121,83,81,'76');  
　　-> 'mysql' 
mysql> select char(77,77.3,'77.3');  
　　-> 'mmm' 

concat(str1,str2,…)
作用：将多个参数拼接成一个字符串，只要有一个为null,就返回null。

mysql> select concat('my', 's', 'ql');  
　　-> 'mysql' 
mysql> select concat('my', null, 'ql');  
　　-> null 
mysql> select concat(14.3);  
　　-> '14.3' 

length(str)/octet_length(str)
作用：返回参数对应的默认字符集的所有字节数。

char_length(str)/character_length(str)
作用：返回字符串str的字符长度

mysql> select length('text');  
　　-> 4  
mysql> select octet_length('text');  
　　-> 4  

locate(substr,str)/position(substr in str)/instr(str,substr)
作用：返回字符串substr在字符串str第一次出现的位置(str不包含substr时返回0)

mysql> select locate('bar', 'foobarbar');  
　　-> 4  
mysql> select locate('xbar', 'foobar');  
　　-> 0
mysql> select instr('foobarbar', 'bar');  
　　-> 4 

locate(substr,str,pos)
作用：返回字符串substr在字符串str的第pos个位置起第一次出现的位置(str不包含substr时返回0)

mysql> select locate('bar', 'foobarbar',5);  
　　-> 7  

lpad(str,len,padstr)/rpad(str,len,padstr)
用字符串padstr填补str左端/右端直到字串长度为len并返回

mysql> select lpad('hi',4,'??');  
　　-> '??hi'  
mysql> select rpad('hi',5,'?');  
　　-> 'hi???' 

left(str,len)/right(str,len)
作用：返回字符串str的左端/右端的len个字符

mysql> select left('foobarbar', 5);  
　　-> 'fooba'  
mysql> select right('foobarbar', 4);  
　　-> 'rbar' 

substring(str,pos,len)/substring(str from pos for len)/mid(str,pos,len)
作用：返回字符串str的位置pos起len个字符

mysql> select substring('quadratically',5,6);  
　　-> 'ratica' 

substring(str,pos)/substring(str from pos)
作用：返回字符串str的位置pos起的一个子串，pos为负数时，倒数后截取到最后

mysql> select substring('quadratically',5);  
　　-> 'ratically' 
mysql> select substring('foobarbar' from 4);  
　　-> 'barbar' 

substring_index(str,delim,count)
作用：返回从字符串str的第count个出现的分隔符delim之后的子串
(count为正数时返回左端,否则返回右端子串)

mysql> select substring_index('www.mysql.com', '.', 2);  
　　-> 'www.mysql' 
mysql> select substring_index('www.mysql.com', '.', -2);  
　　-> 'mysql.com' 

ltrim(str)/rtrim(str)
作用：返回删除了左空格/右空格的字符串str

mysql> select ltrim('  barbar');  
　　-> 'barbar' 
mysql> select rtrim('barbar   ');  
　　-> 'barbar' 

trim([[both | leading | trailing] [remstr] from] str)
作用：返回前缀或后缀remstr被删除了的字符串str(位置参数默认both,remstr默认值为空格)

mysql> select trim('  bar   ');  
　　-> 'bar' 
mysql> select trim(leading 'x' from 'xxxbarxxx');  
　　-> 'barxxx' 
mysql> select trim(both 'x' from 'xxxbarxxx');  
　　-> 'bar' 
mysql> select trim(trailing 'xyz' from 'barxxyz');  
　　-> 'barx' 

space(n)
作用：返回由n个空格字符组成的一个字符串

mysql> select space(6);  
　　-> '      ' 

replace(str,from_str,to_str)
作用：用字符串to_str替换字符串str中的子串from_str并返回

mysql> select replace('www.mysql.com', 'w', 'ww');  
　　-> 'wwwwww.mysql.com' 

repeat(str,count)
作用：返回由count个字符串str连成的一个字符串(任何参数为null时
返回null,count<=0时返回一个空字符串)

mysql> select repeat('mysql', 3);  
　　-> 'mysqlmysqlmysql'

reverse(str)
作用：颠倒字符串str的字符顺序并返回

mysql> select reverse('abc');  
　　-> 'cba' 

insert(str,pos,len,newstr)
作用：把字符串str由位置pos起len个字符长的子串替换为字符串newstr并返回

mysql> select insert('quadratic', 3, 4, 'what');  
　　-> 'quwhattic' 

lcase(str)/lower(str)/ucase(str)/upper(str)
作用：返回str的大小写

elt(n,str1,str2,str3,…)
作用：返回第n个字符串(n小于1或大于参数个数返回null)

mysql> select elt(1, 'ej', 'heja', 'hej', 'foo');  
　　-> 'ej' 
mysql> select elt(4, 'ej', 'heja', 'hej', 'foo');  
　　-> 'foo' 

field(str,str1,str2,str3,…)
作用：返回str等于其后的第n个字符串的序号(如果str没找到返回0)

mysql> select field('ej', 'hej', 'ej', 'heja', 'hej',
'foo');  
　　-> 2  
mysql> select field('fo', 'hej', 'ej', 'heja', 'hej',
'foo');  
　　-> 0  

find_in_set(str,strlist)

作用：返回str在字符串集strlist中的序号(任何参数是null则返回
null,如果str没找到返回0,参数1包含","时工作异常)

mysql> select find_in_set('b','a,b,c,d');  
　　-> 2  

make_set(bits,str1,str2,…)
把参数1的数字转为二进制,假如某个位置的二进制位等于1,对应
位置的字串选入字串集并返回(null串不添加到结果中)

mysql> select make_set(1,'a','b','c');  
                 01   97-61 01100001 01100010 0011
　　-> 'a' 
mysql> select make_set(1 | 4,'hello','nice','world');  
                      01   0100     
　　-> 'hello,world' 
mysql> select make_set(0,'a','b','c');  
　　-> '' 

export_set(bits,on,off,[separator,[number_of_bits]])
按bits排列字符串集,只有当位等于1时插入字串on,否则插入
off(separator默认值",",number_of_bits参数使用时长度不足补0
而过长截断)

mysql> select export_set(5,'y','n',',',4)  
　　-> y,n,y,n   

load_file(file_name)
读入文件并且作为一个字符串返回文件内容(文件无法找到,路径
不完整,没有权限,长度大于max_allowed_packet会返回null)

mysql> update table_name set blob_column=load_file
("/tmp/picture") where id=1

;

soundex(str)
返回str的一个同音字符串(听起来“大致相同”字符串有相同的
同音字符串,非数字字母字符被忽略,在a-z外的字母被当作元音)

mysql> select soundex('hello');  
　　-> 'h400' 
mysql> select soundex('quadratically');  
　　-> 'q36324' 

3.日期和时间函数

3.1 获取当前日期：curdate(),current_date()

 获取当前时间：curtime(),current_time()

 获取当前日期和时间：now(),current_timestamp(),localtime(),sysdate(),localtimestamp()

3.2 unix时间戳函数

 以unix时间戳形式返回当前时间：unix_timestamp()

 将时间 d 以unix时间戳形式返回：unix_timestamp(d)

 把unix时间戳的时间转换为普通格式的时间：from_unixtime(d

3.3 返回utc日期：utc_date()

 返回utc时间：utc_time()          

3.4 获取月份：month(d),monthname(d)

mysql> select now(),month(now()),monthname(now());
+---------------------+--------------+------------------+
| now()               | month(now()) | monthname(now()) |
+---------------------+--------------+------------------+
| 2019-12-12 11:26:20 |           12 | December         |
+---------------------+--------------+------------------+
1 row in set (0.00 sec)

3.5 获取星期：

dayname(d) :Monday,…

  dayofweek(d) :1(周日),...

  weekday(d) :0(周一),...

3.6 获取星期数：

week(d),weekofyear(d) :d是本年第几周

3.7 获取天数：

dayofyear(d) :本年第几天

dayofmonth(d) ：本月第几天

3.8 返回日期d中年份值：

year(d)

 返回日期d是本年第几季度：quarter(d)

 返回时间t中小时值：hour(t)

 返回时间t中分钟值：minute(t)

 返回时间t中秒钟值：second(t)

3.9 返回日期的指定值：extract(type from d)

type:： year :年份

            month:月份

            day:几号

            hour:小时

            minute:分钟

            second:秒

3.10 时间和秒钟转换

   time_to_sec(t)

   sec_to_time(s)

3.11 计算日期和时间

(1) to_days(d) :日期d与0000年1月1日的天数

  from_days(n):从0000年1月1日开始 n 天后日期

  datediff(d1,d2):日期d1和d2之间相隔的天数

(2) adddate(d,n) :起始日期 d 加上 n 天的日期

  subdate(d,n) :起始日期 d 减去 n 天的日期

  addtime(t,n) :起始时间 t 加上 n 秒的时间

  subtime(t,n) :起始时间 t 减去 n 秒的时间

(3) adddate(d,interval expr type),date_add(d,interval expr type)：起始日期d加上一个时间段后日期

   subdate(d,interval expr type) :起始日期d减去一个时间段后日期

3.12 将日期和时间格式化

date_format(d,f):按f格式要求显示日期d

time_format(t,f):按f格式要求显示时间t

get_format(type,s):根据字符串s获取type类型数据的显示格式

!! date_format(d,get_format(type,s))

4.条件判断函数

4.1 if(expr,v1,v2) :expr成立，返回v1,否则返回v2

select grade,if(grade>=60,'pass','fail') from student

4.2 ifnull(v1,v2) :若v1不为空，显示v1值，否则显示v2值

<pre name="code" class="sql"> 例：select grade,ifnull(grade，‘no grade') from student

4.3 case函数

4.3.1 case when expr1 then v1

              [when expr2 then v2 ...]

              [else vn]

      end 字段

4.3.2 case expr

         when e1 then v1

         [when e2 then v2 ...]

         [else vn]

     end 字段

5.系统信息函数

6.加密函数

6.1 password(str) :主要用来给用户的密码加密，此加密不可逆。

!! 不能将未加密的密码直接存储到Mysql的user表中

6.2 md5(str) :主要用于对普通的数据加密

6.3 encode(str,pswd_str) :使用字符串pswd_str来加密字符串str.加密的结果是一个二进制数，必须使用blob类型的字段来保存

6.4 decode(crypt_str,pswd_str) :使用字符串pswd_str来为crypt_str解密。

7.其他函数

7.1 format(x,n) :将数字x进行格式化，将x保留到小数点后n位（四舍五入）<=>round(x,y)

7.2 ascII(s) :返回字符串s的第一个字符的ASCII码

  bin(x) :返回x的二进制编码

  hex(x) :x的十六进制编码

  oct(x) :x的八进制编码

  conv(x,f1,f2) :将x从f1进制数变成f2进制数

7.3 inet_aton(ip) :将ip地址转换为数字表示

  inet_ntoa(n) :将数字n转换成ip形式

7.4 get_lock(name,time) :定义一个名称为nam、持续时间长度为time秒的锁

  release_lock(name) :解除名称为name的锁

  is_free_lock(name) :是否使用名称为name的锁

7.5 benchmark(count,expr) :将表达式expr重复执行count次，然后返回执行时间

7.6 convert(s using cs) :将字符串s的字符集变成cs

7.7 cast(x as type)和convert(x,type) :将x变成type类型