MySql 函数

  1. 一般函数
  2. 数学函数
  3. 三角函数
  4. 字符串函数
  5. 日期时间函数
  6. 类型转换函数
  7. 辅助功能函数

一般函数

语法 意义
IFNULL(expr1,expr2) 返回第一个非空值
IFNULL(expr1,expr2) 返回第一个非空值
NULLIF(expr1,expr2) 判断两值是否相等,返回 NULL 或 expr1
IF(expr1,expr2,expr3) 判断 expr1 的真假,返回 expr2 或 expr3
CASE value

    WHEN [compare-value] THEN result 

    [WHEN [compare-value] THEN result ...] 

    [ELSE result]

END

当 value = compare 时返回相应的 result
CASE 

    WHEN [condition] THEN result

    [WHEN [condition] THEN result ...]

    [ELSE result] 

END

当 condition 为真时返回相应的 result
LEAST(n1,n2,...)

GREATEST(n1,n2,...)

返回最小值

返回最大值

数学函数

语法 意义
ABS(x) 绝对值
SIGN(x) 取符号,返回 -1,0,1
MOD(n,m)

n % m

取模,整除后的余数
FLOOR(x) 返回小于等于 x 的整数
CELLING(x) 返回大于等于 x 的整数
ROUND(x)

ROUND(x,d)

四舍五入
x DIV y 整除
EXP(x) 返回 e 的 x 次方
LN(x) 返回 x 的自然对数
LOG(x)

LOG(B,x)

LOG2(x)

LOG10(x)

返回自然对数

返回以 B 为底的自然对数

= LOG(2,x)

= LOG(10,x)

POW(x,y)

POWER(x,y)

计算 x 的 y 次方
PI() 圆周率 PI 值
RAND()

RAND(N)

随机数 0-1 或 0-N
TRUNCATE(x,d) 将数截止 d 位小数

三角函数

语法 意义
SIN(x)

COS(x)

TAN(x)

COT(x)

ASIN(x)

ACOS(x)

ATAN(x)

正弦

余弦

正切

余切

反正弦

反余弦

反正切

ATAN(x,y)

ATAN2(x,y)

= ATAN(x/y) 反正切
DEGREES(x)

RADIANS(x)

弧度转角度

角度转弧度

字符串函数

语法 意义
ASCII(str) 返回一个 ASCII 码值
str为空时为 0,为 NULL 时为 NULL
ORD(str) 返回 内码值,支持双字节
CONV(N,A,B) 将 A 进制的串 N,转成 B 进制的串返回
BIN(N) 返回二进制串
OCT(N) 返回八进制串
HEX(N) 返回十六进制串
HEX(str) 将字符串转成十六进制串
CHAR(N,...) 将数字串转成字符串,空值忽略
CONCAT(str1,str2,...) 连接字符串,任何一个为空则空
CONCAT_WS(分隔符,str1,str2,...) 连接字符串,忽略空串
LENGTH(str)

OCTET_LENGTH(str)

返回字节数
CHAR_LENGTH(str)

CHARACTER_LENGTH(str)

返回字数
BIT_LENGTH(str) 返回比特长度,8位于LENGTH()
INSTR(str,substr)

LOCATE(substr,str)

POSITION(substr IN str)

查找子字符串位置,首位置为 1
LOCATE(substr,str,pos) 从 pos 处查找子字符串位置
LPAD(str,len,padstr)
RPAD(str,len,padstr)
用 padstr 将 str 补充到 len长度, 分别为左补和右补

如果本身超长则截短

LEFT(str,len)

RIGHT(str,len)

从左或右取 str 的 len 个字符
SUBSTRING(str,pos,len)

SUBSTRING(str FROM pos FOR len)

MID(str,pos,len)

取字符串 pos 位置的 len 个字符
SUBSTRING_INDEX(str,delim,count) 返回 str 中第 count 个 delim 的左半部分,支持双字节
LTRIM(str)

RTRIM(str)

返回去掉左空/右空的字符串
TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str) 去掉头或/和尾部的指定字符或空格,支持双字节
SOUNDEX(str) 返回近音字
SPACE(N) 返回有 N 个空格的字符串
REPLACE(str,str1,str2) 在 str 中查找 str1,并换成 str2
REPEAT(str,count) 将 count 个 str 连接返回
REVERSE(str) 将 str 字符倒置,支持双字节
INSERT(str,pos,len,str1) 替换部分字符串
ELT(N,str1,str2,...) 返回 strN,超出范围返回 NULL
FIELD(str,s1,s2,s3,...) 如果 str = sN,返回 N,否则返回 0
FIND_IN_SET(str,strlist) strlist = "a,b,c,d,...",返回值同 FIELD
MAKE_SET(bits,s1,s2,...) 根据 bits 的二进制位,将 sN 以","为间隔连接后返回
EXPORT_SET(bits,s1,s0,[separator,[N]]) bits 的 N[64]个二进制位,0用s0替换,1用s1替换,以sepatator 为间隔连接后返回
LCASE(str)
LOWER(str)

UCASE(str)
UPPER(str)

将字符串转成小写或大写,支持双字节
KLOAD_FILE(file_name) 返回文件中的字符串
QUOTE(str) 将字符串按 MySql 规则转义

日期时间函数

语法 意义
DAYOFWEEK(date) 星期日为 1
WEEKDAY(date) 星期一为 0
DAYOFMONTH(date) 日 1-31
DAYOFYEAR(date) 一年中的第几日
MONTH(date) 月份 1-12
DAYNAME(date) 英文星期
MONTHNAME(date) 英文月份
QUARTER(date) 季度 1-4
WEEK(date)

WEEK(date,first)

一年中的周数,默认星期日一周的第一天,当 first 为奇数时,以星期一为一周的第一天
YEAR(date) 年份
YEARWEEK(date)

YEARWEEK(date,first)

返回 年份与周数,如 190022,1900年22周,first 参数同 WEEK
HOUR(time)

MINUTE(time)

SECOND(time)

返回时,分,秒
PERIOD_ADD(P,N) P 为年月组成的数字,4或6位长,N 为月数,两者相加
PERIOD_DIFF(P1,P2) 计算年月相差的月数
DATE_ADD(date,INTERVAL expr type)
ADDDATE(date,INTERVAL expr type)

DATE_SUB(date,INTERVAL expr type)
SUBDATE(date,INTERVAL expr type)

计算日期时间的加、减法,返回日期时间值

加一天:ADDDATE(date,INTERVAL 1 day)

参见:时间计算参数格式

EXTRACT(type FROM date) 按 type 提取时间的部分内容
TO_DAYS(date) 计算从 0 年开始是第几天
FROM_DAYS(N) 从 0 年开始第几天的日期
DATE_FORMAT(date,format) 按 format 格式提取时间内容

参见 时间日期提取格式

TIME_FORMAT(time,format) 同 DATE_FORMAT
CURDATE()
CURRENT_DATE

CUETIME()
CURRENT_TIME

当前日期,时间
NOW()

SYSDATE()

CURRENT_TIMESTAMP

当前日期时间
UNIX_TIMESTAMP()

UNIX_TIMESTAMP(date)

返回时间戳,1970-1-1 0:0:0 开始
FROM_UNIXTIME(unix_timestamp[,format]) 将时间戳转换成日期时间字符串
SEC_TO_TIME(seconds) 将秒数转换成时间
TIME_TO_SEC(time) 将时间转换成秒数

类型转换函数

语法 意义
CAST(exp AS type) 将表达式值转成 type 类型

类型值列表

  1. BINARY
  2. CHAR (4.0.6 中新加入)
  3. DATE
  4. DATETIME
  5. SIGNED {INTEGER}
  6. TIME
  7. UNSIGNED {INTEGER}

辅助功能函数

运算符 意义
DATABASE() 返回当前数据库名
USER()

SYSTEM_USER()

SESSION_USER()

返回当前 MySql 用户名
CURRENT_USER() 返回当前会话被验证匹配的用户名
PASSWORD(str)

OLD_PASSWORD(str)

加密文本,不可逆方法
ENCRYPT(str[,salt]) 使用 Unix crypt() 系统加密 str 前八个字符,salt 需有两个字符
ENCODE(str,pass_str) 使用 pass_str 密钥加密 str
DECODE(str,pass_str) 使用 pass_str 密钥解密 str
MD5(string)  计算一个字符串的 MD5 128 位校验和,返回 32 位十六进制串
SHA1(string) 

SHA(string) 

计算一个字符串的 SHA1 160 位校验和,返回 40 位十六进制串
AES_ENCRYPT(string,key_string) AES_DECRYPT(string,key_string)  使用 AES(Advanced Encryption Standardadvanced 先进的密码技术标准) 算法加密/解密数据。加密时使用 128 位长的密钥
DES_ENCRYPT(string_to_encrypt [, (key_number | key_string) ] )  使用 Triple-DES 算法以给定的密钥对字符串加密
DES_DECRYPT(string_to_decrypt [, key_string])  解密 DES_ENCRYPT() 加密后的字符串
LAST_INSERT_ID([expr])  返回被插入到一个 AUTO_INCREMENT 列中的最后一个自动产生的值
FORMAT(X,D)  将数字 X 格式化为一个 '#,###.##' 的形式
VERSION()  MySQL 服务器的版本
CONNECTION_ID()  返回当前连接的连接 ID(thread_id)。每个连接均有一个自己唯一的 id
GET_LOCK(str,timeout)  尝试获得一个由字符串 str 给定名字的锁定与一个 timeout 秒的超时
RELEASE_LOCK(str) 释放由字符串 str 命名的通过 GET_LOCK() 获得的锁定
IS_FREE_LOCK(str)  检查以 str 命名的锁定是否可以自由使用
BENCHMARK(count,expr)  BENCHMARK() 函数用于将表达式 expr 重复运行 count 次,结果通常为 0,在客户端使用时返回客户端执行时间
INET_NTOA(expr) 给定一个数字的网络地址 (4 或 8 字节),以一个字符串的形式返回点组表示的地址
INET_ATON(expr)  以字符串的形式给定一个点组表示的网络地址,返回一个地址的数字值表示的整数
MASTER_POS_WAIT(log_name, log_pos)  阻塞,只到从服务器到达(也就是说,已读取并应用了所有更新,一直到)主服务器上的日志中指定的位置
FOUND_ROWS() 返回上一次执行数据库查询输出记录的条数

时间计算参数格式

type 值 expr 格式
SECOND SECONDS
MINUTE MINUTES
HOUR HOURS
DAY DAYS
MONTH MONTHS
YEAR YEARS
MINUTE_SECOND "MINUTES:SECONDS"
HOUR_MINUTE "HOURS:MINUTES"
DAY_HOUR "DAYS HOURS"
YEAR_MONTH "YEARS-MONTHS"
HOUR_SECOND "HOURS:MINUTES:SECONDS"
DAY_MINUTE "DAYS HOURS:MINUTES"
DAY_SECOND "DAYS HOURS:MINUTES:SECONDS"

日期时间提取格式

符号 意义
%M  月的名字 (January..December) 
%W  星期的名字 (Sunday..Saturday) 
%D  有英文后缀的某月的第几天 (0th, 1st, 2nd, 3rd, etc.) 
%Y  年份,数字的,4 位 
%y  年份,数字的,2 位 
%X  周值的年份,星期日是一个星期的第一天,数字的,4 位,与 '%V' 一同使用 
%x  周值的年份,星期一是一个星期的第一天,数字的,4 位,与 '%v' 一同使用 
%a  缩写的星期名 (Sun..Sat) 
%d  月份中的天数,数字的 (00..31) 
%e  月份中的天数,数字的 (0..31) 
%m  月,数字的 (00..12) 
%c  月,数字的 (0..12) 
%b  缩写的月份名 (Jan..Dec) 
%j  一年中的天数 (001..366) 
%H  小时 (00..23) 
%k  小时 (0..23) 
%h  小时 (01..12) 
%I  小时 (01..12) 
%l  小时 (1..12) 
%i  分钟,数字的 (00..59) 
%r  时间,12 小时 (hh:mm:ss [AP]M) 
%T  时间,24 小时 (hh:mm:ss) 
%S  秒 (00..59) 
%s  秒 (00..59) 
%p  AM 或 PM 
%w  一周中的天数 (0=Sunday..6=Saturday) 
%U  星期 (00..53),星期日是一个星期的第一天 
%u  星期 (00..53),星期一是一个星期的第一天 
%V  星期 (01..53),星期日是一个星期的第一天。与 '%X' 一起使用 
%v  星期 (01..53),星期一是一个星期的第一天。与 '%x' 一起使用