MySql常用的内置函数

痛定思痛。 2022-02-26 02:48 390阅读 0赞

mysql内置函数列表可以从[mysql官方文档][mysql]查询，这里仅分类简单介绍一些可能会用到的函数。

1 数学函数

abs(x)  
pi()  
mod(x,y)  
sqrt(x)  
ceil(x)或者ceiling(x)  
rand(),rand(N):返回0-1间的浮点数，使用不同的seed N可以获得不同的随机数  
round(x, D)：四舍五入保留D位小数，D默认为0， 可以为负数， 如round(19, -1)返回20  
truncate(x, D):截断至保留D位小数，D可以为负数， 如trancate(19,-1)返回10  
sign(x): 返回x的符号，正负零分别返回1， -1， 0  
pow(x,y)或者power(x,y)  
exp(x)：e^x  
log(x)：自然对数  
log10(x)：以10为底的对数  
radians(x):角度换弧度  
degrees(x):弧度换角度  
sin(x)和asin(x):  
cos(x)和acos(x):  
tan(x)和atan(x):  
cot(x):

2 字符串函数

char\_length(str):返回str所包含的字符数，一个多字节字符算一个字符  
length(str): 返回字符串的字节长度，如utf8中，一个汉字3字节，数字和字母算一个字节  
concat(s1, s1, ...): 返回连接参数产生的字符串  
concat\_ws(x, s1, s2, ...): 使用连接符x连接其他参数产生的字符串  
INSERT(str,pos,len,newstr):返回str,其起始于pos，长度为len的子串被newstr取代。  
1. 若pos不在str范围内，则返回原字符串str  
2. 若str中从pos开始的子串不足len,则将从pos开始的剩余字符用newstr取代  
3. 计算pos时从1开始，若pos=3,则从第3个字符开始替换  
lower（str)或者lcase(str):  
upper(str)或者ucase(str):  
left(s,n):返回字符串s最左边n个字符  
right(s,n): 返回字符串最右边n个字符  
lpad(s1, len, s2): 用s2在s1左边填充至长度为len, 若s1的长度大于len,则截断字符串s1至长度len返回  
rpad(s1, len, s2):  
ltrim(s):删除s左侧空格字符  
rtrim(s):  
TRIM(\[\{BOTH | LEADING | TRAILING\} \[remstr\] FROM\] str)或TRIM(\[remstr FROM\] str)：从str中删除remstr, remstr默认为空白字符  
REPEAT(str,count)：返回str重复count次得到的新字符串  
REPLACE(str,from\_str,to\_str)： 将str中的from\_str全部替换成to\_str  
SPACE(N):返回长度为N的空白字符串  
STRCMP(str1,str2):若str1和str2相同，返回0， 若str1小于str2, 返回-1， 否则返回1.  
SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len),MID(str,pos,len): 获取特定位置，特定长度的子字符串  
LOCATE(substr,str), LOCATE(substr,str,pos),INSTR(str,substr),POSITION(substr IN str): 返回字符串中特定子串的位置，注意这里INSTR与其他函数的参数位置是相反的  
REVERSE(str)  
ELT(N,str1,str2,str3,...)：返回参数strN, 若N大于str参数个数，则返回NULL  
FIELD(str,str1,str2,str3,...): 返回str在后面的str列表中第一次出现的位置，若找不到str或者str为NULL, 则返回0  
FIND\_IN\_SET(str,strlist)：strlist是由','分隔的字符串，若str不在strlist或者strlist为空字符串，则返回0；若任意一个参数为NULL则返回ＮＵＬＬ  
MAKE\_SET(bits,str1,str2,...): 由bits的作为位图来选取strN参数，选中的参数用','连接后返回

3 日期和时间函数

CURDATE(), CURRENT\_DATE, CURRENT\_DATE():用于获取当前日期，格式为'YYYY-MM-DD'; 若+0则返回YYYYMMDD  
UTC\_DATE, UTC\_DATE():返回当前世界标准时间  
CURTIME(\[fsp\]), CURRENT\_TIME, CURRENT\_TIME(\[fsp\]): 用于获取当前时间， 格式为'HH:MM:SS' 若+0则返回 HHMMSS  
UTC\_TIME, UTC\_TIME(\[fsp\])  
CURRENT\_TIMESTAMP, CURRENT\_TIMESTAMP(\[fsp\]), LOCALTIME, LOCALTIME(\[fsp\]), SYSDATE(\[fsp\]), NOW(\[fsp\]): 用于获取当前的时间日期，格式为'YYYY-MM-DD HH:MM:SS'，若+0则返回YYYYMMDDHHMMSS  
UTC\_TIMESTAMP, UTC\_TIMESTAMP(\[fsp\])  
UNIX\_TIMESTAMP(), UNIX\_TIMESTAMP(date)：返回一个unix时间戳（'1970-01-01 00:00:00' UTC至今或者date的秒数），这实际上是从字符串到整数的一个转化过程  
FROM\_UNIXTIME(unix\_timestamp), FROM\_UNIXTIME(unix\_timestamp,format)：从时间戳返回'YYYY-MM-DD HH:MM:SS' 或者YYYYMMDDHHMMSS，加入format后根据所需的format显示。  
MONTH(date)  
MONTHNAME(date)  
DAYNAME(date)  
DAY(date)，DAYOFMONTH(date)：1-31或者0  
DAYOFWEEK(date)：1-7==>星期天-星期六  
DAYOFYEAR(date)： 1-365（366）  
WEEK(date\[,mode\])：判断是一年的第几周，如果1-1所在周在新的一年多于4天，则将其定为第一周；否则将其定为上一年的最后一周。mode是用来人为定义一周从星期几开始。  
WEEKOFYEAR(date)：类似week(date,3)，从周一开始计算一周。  
QUARTER(date)：返回1-4  
HOUR(time)：返回时间中的小时数，可以大于24  
MINUTE(time)：  
SECOND(time)：  
EXTRACT(unit FROM date)：提取日期时间中的要素

SELECT EXTRACT(YEAR FROM '2009-07-02'); ##2009
        SELECT EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03');##200907
        SELECT EXTRACT(DAY_MINUTE FROM '2009-07-02 01:02:03');##20102
        SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.000123');##123

TIME\_TO\_SEC(time)  
SEC\_TO\_TIME(seconds)

TO\_DAYS(date): 从第0年开始的天数  
TO\_SECNDS(expr)：从第0年开始的秒数

ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days),DATE\_ADD(date,INTERVAL expr unit)  
DATE\_SUB(date,INTERVAL expr unit), DATE\_SUB(date,INTERVAL expr unit)  
ADDTIME(expr1,expr2)  
SUBTIME(expr1,expr2)

SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');##'2008-01-02 01:01:01.000001'
        SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');##'03:00:01.999997'

注意：时间日期的加减也可以直接用+/-来进行

date + INTERVAL expr unit
        date - INTERVAL expr unit
        如：
        SELECT '2008-12-31 23:59:59' + INTERVAL 1 SECOND;##'2009-01-01 00:00:00'
        SELECT INTERVAL 1 DAY + '2008-12-31';##'2009-01-01'
        SELECT '2005-01-01' - INTERVAL 1 SECOND;##'2004-12-31 23:59:59'

DATE\_FORMAT(date,format):  
DATEDIFF(expr1,expr2):返回相差的天数  
TIMEDIFF(expr1,expr2)：返回相隔的时间

4 条件判断函数

IF(expr1,expr2,expr3)：如果expr1不为0或者NULL,则返回expr2的值，否则返回expr3的值  
IFNULL(expr1,expr2)：如果expr1不为NULL,返回expr1,否则返回expr2  
NULLIF(expr1,expr2): 如果expr1=expr2则返回NULL, 否则返回expr2  
CASE value WHEN \[compare\_value\] THEN result \[WHEN \[compare\_value\] THEN result ...\] \[ELSE result\] END  
当compare\_value=value时返回result  
CASE WHEN \[condition\] THEN result \[WHEN \[condition\] THEN result ...\] \[ELSE result\] END  
当condition为TRUE时返回result

SELECT CASE 1 WHEN 1 THEN 'one'
            WHEN 2 THEN 'two' ELSE 'more' END;##'one'
        SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;##'true'
        SELECT CASE BINARY 'B'
            WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;##NULL

5 系统信息函数

VERSION():返回mysql服务器的版本，是utf8编码的字符串  
CONNECTION\_ID()：显示连接号（连接的线程号）  
DATABASE()，SCHEMA()：显示当前使用的数据库  
SESSION\_USER(), SYSTEM\_USER(), USER(), CURRENT\_USER, CURRENT\_USER():返回当前的用户名@主机，utf8编码字符串  
CHARSET(str)  
COLLATION(str)  
LAST\_INSERT\_ID()：自动返回最后一个insert或者update查询， 为auto\_increment列设置的第一个发生的值

6 加密和压缩函数

PASSWORD(str):这个函数的输出与变量old\_password有关。old\_password 在mysql5.6中默认为0。 不同取值的效果如下表  
![865265-20160224150201724-1500361171.png][]  
old\_password=1时， password(str)的效果与old\_password(str)相同，由于其不够安全已经弃用（5.6.5以后）。  
old\_password=2时，在生成哈希密码时会随机加盐。

MD5(str):计算MD5 128位校验和，返回32位16进制数构成的字符串，当str为NULL时返回NULL。可以用作哈希密码  
SHA1(str), SHA(str)：计算160位校验和，返回40位16进制数构成的字符串，当str为NULL时返回NULL。  
SHA2(str, hash\_length)：计算SHA-2系列的哈希方法(SHA-224, SHA-256, SHA-384, and SHA-512). 第一个参数为待校验字符串，第二个参数为结果的位数（224， 256， 384， 512）  
ENCRYPT(str\[,salt\]): 用unix crypt()来加密str. salt至少要有两位字符，否则会返回NULL。若未指定salt参数，则会随机添加salt。

ECODE(crypt\_str,pass\_str):解密crypt\_str, pass\_str用作密码  
ENCODE(str,pass\_str)：用pass\_str作为密码加密str

DES\_ENCRYPT(str\[,\{key\_num|key\_str\}\])：用Triple-DES算法编码str， 这个函数只有在mysql配置成支持ssl时才可用。  
DES\_DECRYPT(crypt\_str\[,key\_str\])

AES\_ENCRYPT(str,key\_str\[,init\_vector\])  
AES\_DECRYPT(crypt\_str,key\_str\[,init\_vector\])

COMPRESS(string\_to\_compress)：返回二进制码  
UNCOMPRESS(string\_to\_uncompress)

7 聚合函数

若在没使用group by时使用聚合函数，相当于把所有的行都归于一组来进行处理。除非特殊说明，一般聚合函数会忽略掉NULL.  
AVG(\[DISTINCT\] expr): 返回expr的平均值，distinct选项用于忽略重复值  
COUNT(\[DISTINCT\] expr)：返回select中expr的非0值个数，返回值为bigint类型  
group\_concat:连接组内的非空值，若无非空值，则返回NULL

GROUP_CONCAT([DISTINCT] expr [,expr ...]
                 [ORDER BY {unsigned_integer | col_name | expr}
                        [ASC | DESC] [,col_name ...]]
                [SEPARATOR str_val])

MAX(\[DISTINCT\] expr)  
MIN(\[DISTINCT\] expr)

SUM(\[DISTINCT\] expr)  
VAR\_POP(expr)  
VARIANCE(expr)：同VAR\_POP(expr)，但是这是标准sql的一个扩展函数  
VAR\_SAMP(expr)  
STD(expr): 这是标准sql的一个扩展函数  
STDDEV(expr)：这个函数是为了跟oracle兼容而设置的  
STDDEV\_POP(expr)：这个是sql标准函数

STDDEV\_SAMP(expr)：样本标准差

8 格式或类型转化函数

FORMAT(X,D\[,locale\])：将数字X转化成'\#,\#\#\#,\#\#\#.\#\#'格式，D为保留的小数位数  
CONV(N,from\_base,to\_base)：改变数字N的进制，返回值为该进制下的数字构成的字符串  
INET\_ATON(expr)：ip字符串转数字  
INET\_NTOA(expr)：数字转ip字符串  
CAST(expr AS type)：转换数据类型  
CONVERT(expr,type), CONVERT(expr USING transcoding\_name)： type可以为BINARY\[(N)\]，CHAR\[(N)\]，DATE，DATETIME， DECIMAL\[(M\[,D\])\]，DECIMAL\[(M\[,D\])\]，TIME，UNSIGNED \[INTEGER\]等等。transcoding\_name如utf8等等

文章转自：[https://www.cnblogs.com/noway-neway/p/5211401.html][https_www.cnblogs.com_noway-neway_p_5211401.html]

[mysql]: https://dev.mysql.com/doc/refman/5.6/en/func-op-summary-ref.html
[865265-20160224150201724-1500361171.png]: /images/20220226/902f6b6e5458485194cf402860aedac0.png
[https_www.cnblogs.com_noway-neway_p_5211401.html]: https://www.cnblogs.com/noway-neway/p/5211401.html