mysql函数大全

1. 数学函数

1.1 绝对值abs(x)

select abs(-1);

1.2 圆周率pi()

select pi();

1.3 平方根sqrt(x)

select sqrt(4);

1.4 求余mod(x, y)

select mod(45.5, 6);

1.5 取整cell(x), celing(x), floor(x)

ceil(x)和ceiling(x)意义相同，返回不小于x的最小整数，返回值转化为一个bigint

select ceil(3.5), ceil(-3.5);

floor(x)返回不大于x的最大整数，返回值转化为一个bigint

select floor(3.5), floor(-3.5);

1.6 随机数rand(), rand(x)

rand(x)返回一个随机浮点数v，范围在0到1之间。若已指定一个整数参数x，则它被用做种子值，用来产生重复序列

select rand(), rand(), rand(10);

1.7 函数round(x), round(x, y)和truncate(x, y)

round(x)返回最接近参数x的整数，对x值进行四舍五入，y，表示小数位后y位，y为负表示保留小数点前y位

select round(33.333), round(66.666, 2);

truncate(x, y)返回被舍去至小数点后y位的数字x，若y的值为0，则结构不带有小数点或不带有小数部分。若y为负值，截去（归零）x小数点左边y位

select truncate(66.666, 2);

1.8 符号函数sign(x)

sign(x)返回参数的符号，x值为负、零或正时范湖结果依次为-1、0、1

select sign(-10), sign(0), sign(10);

1.9 幂运算pow(x, y), power(x, y), exp(x)

算pow(x), power(x, y)返回x的y次方

select pow(2, 3), power(2, 3);

exp(x)返回e的x次方值

select exp(2), exp(3);

1.10 对数运算log(x), log10(x)

log(x)返回x的自然对数，x性对于基数e的对数

select log(3);

log10(e)返回x基于基数为10的对数

select log10(10);

1.11 角度弧度转化radians(x), degrees(x)

radians(x)将参数x有角度转化为弧度

select radians(180);

degrees(x)将参数x由角度转换为弧度

select degrees(pi());

1.12 正弦函数sin(x)和反正弦函数asin(x)

select sin(1), asin(0.8414709848078965)

1.13 余弦函数cos(x)和反余弦函数acos(x)

select cos(1), acos(1);

1.14 正切函数，反正切函数和余切函数

select tan(3), atan(1), cot(1);

2. 字符串函数

2.1 字符串长度char_length(str), length(str)

char_length(x)返回值为x字符串str所包含的字符个数。一个字节字符算作一个单字符。
length(str)返回字节长度, 使用utf8编码字符集，一个汉字是3个字节，一个数字或字母算一个字节。

select char_length('String'), length('String');

2.2 字符串合并concat(s1, s2, …), concat_ws(x, s1, s2, …)

concat(s1, s2, …)拼接字符串, 一个或多个参数。如果任何一个参数为NULL，则返回NULL。如果所欲参数均为非二进制字符串，则结果为非二进制字符串。如果变量中含有任一二进制字符串，则结果为一个二进制字符串。
concat_ws(x, s1, s2, …)，x表示拼接符

select concat('Hello', 'world'), concat_ws(' ', 'Hello', 'world');

2.3 替换字符串insert(s1, x, len, s2)

insert(s1, x, len, s2)原始字符串s1,其子字符串起始于x位置和字符串s2取代的len字符。x超过字符串长度，则返回值为原始字符串。假如len的长度大于其他字符串的长度，则从x位置开始替换。若任何一个参数为NULL，则返回值为NULL

select insert('abcdefg', 2, 2, 'zzz');

2.4 字符串大小写转换lower(str), lcase(str), upper(str), ucase(str)

select lower('Hello World'), lcase('Hello World'), upper('Hello World'), ucase('Hello World');

2.5 获取指定长度字符串left(s, n), right(s, n)

left(s, n)返回字符串s开始最左边n个字符
left(s, n)返回字符串s开始最右边n个字符

select left('Hello world', 2), right('Hello world', 2);

2.6 填充字符串lpad(s1, len, s2)和rpad(s1, len, s2)

lpad(s1, len, s2)返回字符串s1,其左边有字符串s2填补到len字符长度。加s1的长度大于len，则返回值被缩短至len字符。
rpad(s1, len, s2)返回字符串s1,其右边有字符串s2填补到len字符长度。

select lpad('Hello world', 2, 'zzz'), rpad('Hello world', 2, 'zzz');

2.7 删除空格ltrim(str), rtrim(str), trim(str)

ltrim(str)删除左空格，rtrim(str)删除右空格，trim(str)删除左右空格

select ltrim('  Hello world  '), ltrim('  Hello world  '), trim('  Hello world  ');

2.8 删除指定字符串trim(s1 from s)

trim(s1 from s)删除字符串s中两端所有子字符串是

select trim('zz' from 'zzHello worldzz');

2.9 重复生成字符串repeat(s, n)

repeat(s, n)返回一个由重复字符串s组成的字符串，字符串s的数目等于n。若n<=0，则返回一个空字符串。若s或n为NULL，则返回NULL。

select repeat('city', 3);

2.10 空格函数space(n)和替换函数replace(s, s1, s2)

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

select space('2');

replace(s, s1, s2)使用字符串s2替换字符串s中所有的字符串s1

select replace('zzHello world', 'zz', '');

2.11 字符串大小比较strcmp(s1, s2)

strcmp(s1, s2)若所有字符串均相同，则范湖0; 若根据当前分类次序，第一个参数小于第二个，则返回-1，其他情况返回1

select strcmp('hello', 'hello');

2.12 获取子串substring(s, n, len), mid(s, n, len)

substring(s, n, len)带有len参数的格式，从字符串s返回一个长度同len字符相同的子字符串，起始于位置n。也可能对n使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的n字符，即倒数第n个字符，而不是字符串的开头位置。
mid(s, n, len)和substring(s, n, len)作用相同。

select substring('breakfast', 5), substring('breakfast', 5, 3);
select mid('breakfast', 5), mid('breakfast', 5, 3);

2.13 匹配字符串开始位置函数locate(s1, s2),position(s1 in s2),instr(s2, s1)

这三个字符串作用相同，返回子字符s1在字符串s2的开始位置。

select locate('ball', 'football'), position('ball' in 'football'), instr('football', 'ball');

2.14 字符串逆序reverse(s)

select reverse('abcd');

2.15 返回指定位置的字符串elt(N, s1, s2, …)若N=1, 则返回值为字符串1, 若N=2，则返回字符串2。若N小于1或者大于参数数目，则返回值为NULL。

select elt(2, 'zhangsan', 'lisi', 'wanger');

2.16 返回指定字符串位置函数field(s, s1, s2, …)

field(s, s1, s2, …)返回字符串s在列表s1, s2, …中第一次出现的位置，在找不到s的情况下，返回值为0。若s为NULL，则返回值为0，原因是NULL不能同任何值进行同等比较。

select field('mazi', 'zhangsan', 'lisi', 'wanger', 'mazi');

2.17 返回子串位置的函数find_in_set(s1, s2)

find_in_set(s1, s2)返回字符串s1在字符串列表s2中出现的位置，字符串列表是一个由多个逗号’,‘分开的字符串组成的列表。如果s1不在s2或s2为空字符串，则返回值为0。如果任意一个参数为NULL，则返回值为NULL。这个函数在第一个参数包含一个逗号’,'时将无法正常运行。

select find_in_set('Hi','hihi,Hi, Hi');

2.18 选取字符串make_set(x, s1, s2, …)

make_set(x, s1, s2, …)返回由x的二进制数指定的相应位的字符串组成的字符串，s1对应比特1，s2对应比特01，以此类推。s1，s2…中的 NULL值不会被添加到结果中。

select make_set(1 | 4, 'hello', 'nice', 'world');

3. 日期时间函数

3.1 获取当前日期当前时间, curdate(), current_date(), curtime(), current_time();

select curdate(), current_date(), curtime(), current_time();

3.2 获取当前日期和时间的函数current_timestamp(), localtime(), now(), sysdate();

select current_timestamp(), localtime(), now(), sysdate();

3.3 unix时间戳函数unix_timestamp(date)

unix_timestamp(date)若无参数调用,则返回一个Unix时间戳('1970-01-01 00:00:00’GMT之后的秒数）作为无符号整数。其中，GMT (Greenwich mean time)为格林尼治标准时间）。若用date来调用unix_timestamp()，它会将参数值以’1970-01-01 00:00:00’GMT后的秒数的形式返回。date可以是一个date字符串、datetime字符串、timestamp或一个当地时间的YYMMDD或YYYYMMDD格式的数字。

select now(), unix_timestamp(), unix_timestamp(now());

3.4 返回UTC日期函数和返回UTC时间函数utc_date(), utc_time()

utc_date()函数返回当前UTC(世界标准时间）日期值，其格式为‘YYYY-MM-DD’或YYYYMMDD，具体格式取决于函数是否用在字符串或数字语境中。
utc_time()返回当前UTC时间值，其格式为’HH:MM:SS’或HHMMSS具体格式取决于函数是否用在字符串或数字语境中。

select utc_date(), utc_time();

3.5 获取月份month(date), monthname(date)

select month('2011-11-11'), monthname('2011-11-11');

3.6 获取星期函数dayname(d), dayofweek(d), weekday(d)

dayname(d)函数返回d对应的工作日的英文名称，例如Sunday、Monday等。
dayofweek(d)函数返回d对应的一周中的索引(位置)。1表示周日，2表示周一，…,7表示周六)。
WEEKDAY(d)返回d对应的工作日索引。0表示周一，1表示周二，…，6表示周

select dayname('2011-11-11'), dayofweek('2011-11-11'), weekday('2011-11-11');

3.7 获取星期数的函数week(d), weekofyear(d)

week(d)计算日期d是一年中的第几周。week()的双参数形式允许指定该星期是否起始于周日或周一，以及返回值的范围是否为从0_53或从153。若Mode参数被省略，则使用default_week_format系统自变量的值。
weekofyear(d)计算某天位于一年中的第几周，范围是从1~53。相当于weekK(d,3)。

select week('2020-10-10'), week('2020-10-10', 0), weekofyear('2020-10-10');

3.8 获取天数函数dayofyear(d), dayofmonth(d)

dayofyear(d)函数返回d是一年中的第几天，范围是从1~366。
dayofmonth(d)函数返回d是一个月中的第几天，范围是从1~31。

select dayofyear('2020-11-11'), dayofmonth('2020-11-11');

3.9 获取年份year(d)、季度quarter(d)、小时hour(d)、分钟minute(d)、秒钟second(d)

select year('2020-11-11 11:11:11'), quarter('2020-11-11 11:11:11'), hour('2020-11-11 11:11:11'),  minute('2020-11-11 11:11:11'), second('2020-11-11 11:11:11');

3.10 获取日期的指定值函数extract(type from date)

extract(type from date)函数所使用的时间间隔类型说明符同date_add()或date_sub()的相同，但它从日期中提取一部分，而不是执行日期运算。

select extract(year from '2020-11-11 11:11:11'), extract(month from '2020-11-11 11:11:11');

3.11 时间和秒钟转换time_to_sec(time), sec_to_time(second)

time_to_se(time)返回已转化为秒的time参数。转换公式为:小时*3600+分钟*60+秒。

SEC_TO_TIME(seconds)返回被转化为小时、分钟和秒数的 seconds参数值，其格式为’HH:MM:SS’’'或HHMMSS，具体格式根据该函数是否用在字符串或数字语境中而定。

select time_to_sec('11:11:11'), sec_to_time(40271);

3.12 计算日期和时间函数

计算日期和时间的函数有:date_add()、adddate()、date_sub()、subdate()、addtime()、subtime和 date_diff()。

date_add(date, interval expr type)和 date_sub(date, interval expr type)，其中，date是一个datetime 或date值，用来指定起始时间。expr是一个表达式，用来指定从起始日期添加或减去的时间间隔值。expr是一个字符串;对于负值的时间间隔，它可以以一个负号‘-’开头。type为关键词，它指示了表达式被解释的方式。见下表

type值	预期的expr格式
MICROSECOND	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
OUARTER	OUARTERS
YEAR	YEARS
SECOND MICROSECOND	‘SECONDS.MICROSECONDS’
MINUTE MICROSECOND	‘MINUTES.MICROSECONDS’
MINUTE SECOND	‘MINUTES:SECONDS’
HOUR MICROSECOND	‘HOURS.MICROSECONDS’
HOUR SECOND	‘HOURS:MINUTES:SECONDS’
HOUR MINUTE	‘HOURS:MINUTES’
DAY MICROSECOND	‘DAYS.MICROSECONDS’
DAY SECOND	‘DAYS HOURS:MINUTES:SECONDS’
DAY MINUTE	‘DAYS HOURS:MINUTES’
DAY HOUR	‘DAYS HOURS’
YEAR MONTH	‘YEARS-MONTHS’

select date_add('2020-11-11 11:11:11', interval '1:1' minute_second);

3.13 时间和日期格式化date_format(date, fmt)

fmt格式如下图

说明符	说明
%a	工作日的缩写名称(Sun…Sat)
%b	月份的缩写名称(Jan…Dec)
%c	月份，数字形式(0…12)
%D	带有英语后缀的该月日期(Oth,1st, 2nd, 3rd, … .)
%d	该月日期,数字形式(00…31)
%e	该月日期,数字形式(0…31)
%f	微秒(000000…999999)
%H	以2位数表示24小时(00…23)
%h, %I	以2位数表示12小时(01…12)
%i	分钟,数字形式(00…59)
%j	一年中的天数(001…366)
%k	以24(0…23)小时表示时间
%1	以12(1…12)小时表示时间
%M	月份名称(January…December)
%m	月份，数字形式(00…12)
%p	上午(AM)或下午(PM)
%r	时间，12小时制（小时hh:分钟mm:秒数ss后加AM或PM）
%S,%s	以2位数形式表示秒(00…59)
%T	时间，24小时制(小时hh:分钟mm:秒数ss）
%U	周(00…53)，其中周日为每周的第一天
%u	周(00…53)，其中周一为每周的第一天
%V	周(01…53)，其中周日为每周的第一天;和%X同时使用
%v	周(01…53)，其中周一为每周的第一天;和%x同时使用
%W	工作日名称（周日…周六)
%w	一周中的每日（0=周日…6=周六）
%X	该周的年份，其中周日为每周的第一天;数字形式，4位数;和%V同时使用
%x	该周的年份，其中周一为每周的第一天;数字形式，4位数;和%v同时使用
%Y	4位数形式表示年份
%y	2位数形式表示年份
%%	‘%’文字字符

select date_format(now(), '%Y-%m-%d %H:%i:%s');

4. 条件判断函数

4.1 if(expr, v1, v2)

if(expr, v1, v2)，如果表达式expr是 true(expr 0 and expr NULL)，则 if()的返回值为vl;否则返回值为v2。if()的返回值为数字值或字符串值，具体情况视其所在语境而定。

select if(1 = 1, 'true', 'false');

4.2 ifnull(v1, v2)

ifnull(v1,v2)假如 v1不为 NULL，则 ifnull()的返回值为vl;否则其返回值为 v2。ifnull()的返回值是数字或是字符串，具体情况取决于其所在的语境。

select ifnull(null, 'default value');

4.3 case

case expr when v1 then r1 [when v2 then r2] [else rn] end

select case 2 when 1 then 'one' when 2 then 'two' else 'O' end;

5. 系统信息函数

5.1 获取版本号version(), 连接数connection_id(), 数据库名database() schema()

select version(), connection_id(), 名database(), schema();

5.2 获取用户名

user()、current_user()、system_user()和 session_user()这几个函数返回当前被 MySQL 服务器验证的用户名和主机名组合。这个值符合确定当前登录用户存取权限的MySQL账户。一般情况下,这几个函数的返回值是相同的。

select user(), current_user() , system_user(), session_user();

5.3 获取字符串的字符集和排序方式

select charset('abc'), charset(convert('abc' using latin1)), collation('abc');

5.4 获取最后一个自动生成的ID值的函数

last_insert_id(O)自动返回最后一个insert或update为AUTO_INCREMENT列设置的第一个发生的值。

select last_insert_id();

6. 加/解密函数

6.1 加密password(str)

select password('123456');

6.2 加密md5(str)

select md5('123456');

6.3 加密encode(str, paswd_str)

encode(str, pswd_str)使用pswd_str作为密码，加密str。使用decode()解密结果，是一个和 str长度相同的二进制字符串。

select encode('secret', 'city'), length(encode('secret', 'city'));

6.4 解密函数decode(crypt_str, pswd_str)

decode(crypt _str, pswd_str)使用pswd_str作为密码，解密加密字符串crypt_str，crypt_str是由encode()返回的字符串。

select decode(encode('secret', 'city'), 'city');

7. 其他函数

7.1 格式化函数format(x, n)

format(x, n)将数字x格式化，并以四舍五入的方式保留小数点后n位，结果以字符串的形式返回。若n为0，则返回结果函数不含小数部分。

select format(66.6666, 3);

7.2 不同进制数字转换

conv(N, from_base, to_base)函数进行不同进制数间的转换。返回值为数值N的字符串表示，由from base进制转化为to _base进制。如有任意一个参数为NULL，则返回值为NULL.自变量N被理解为一个整数，但是可以被指定为一个整数或字符串。最小基数为2，而最大基数则为36。

select conv('a', 16, 2);

7.3 IP地址与数字相互转换

inet_aton(expr)给出一个作为字符串的网络地址的点地址表示,返回一个代表该地址数值的整数。地址可以是4或8bit地址。

inet_ntoa(expr)给定一个数字网络地址(4或8bit)，返回作为字符串的该地址的点地址表示。

select inet_aton('192.168.1.200'), inet_ntoa(inet_aton('192.168.1.200'));

7.4 加锁和解锁

get_lock(str, timeout)设法使用字符串str给定的名字得到一个锁,持续时间timeout秒。
若成功得到锁，则返回1;若操作超时，则返回0;若发生错误，则返回NULL。假如有一个用GET_LOCK()得到的锁，当执行RELEASE_LOCK()或连接断开（正常或非正常》时，这个锁就会解除。

release_lock(str)解开被get_lock()获取的，用字符串str所命名的锁。若锁被解开，则返回1;若该线程尚未创建锁，则返回0(此时锁没有被解开)﹔若命名的锁不存在,则返回NULL。若该锁从未被get_lock()的调用获取，或锁已经被提前解开，则该锁不存在。

if_free_lock(str)检查名为str的锁是否可以使用(换言之，没有被封锁）。若锁可以使用，则返回1(没有人在用这个锁)﹔若这个锁正在被使用，则返回0;出现错误，则返回NULL（诸如不正确的参数)。

is_used lock(str)检查名为str 的锁是否正在被使用(换言之，被封锁)。若被封锁，则返回使用该锁的客户端的连接标识符（connection ID);否则，返回NULL。

select get_lock('lock1', 10), is_used_lock('lock1'), is_free_lock('lock1'), release_lock('lock1');

7.5 重复执行操作

benchmark(count,expr)函数重复count 次执行表达式expr。它可以用于计算MySQL处理表达式的速度。结果值通常为0(0只是表示处理过程很快，并不是没有花费时间）。另一个作用是它可以在MySOL客户端内部报告语句执行的时间。

select benchmark(2, password('123456'));

7.6 改变字符集函数

convert(… using …)带有using的convert()函数被用来在不同的字符集之间转化数据。

select charset('abc'), charset(convert('abc' using latin1));

7.8 改变数据类型

cast(x , AS type)和convert(x, type)函数将一个类型的值转换为另个类型的值,可转换的type有:binary、char(n)、date、time、datetime、decimal、signed、unsigned。

select cast(100 as char(2));