MySQL常用内置函数

文章目录

• 数学函数
• ` abs(X)` 返回X的绝对值
• `mod(N,M)` 或 `%` 返回 N 被 M 除的余数
• `floor(X)` 返回不大于X的最大整数值。
• `ceiling(X)` 返回不小于X的最小整数值。
• `round(X)` 返回参数X的四舍五入的一个整数。
• 字符串函数
• `ascii(str)` 返回字符串str的最左面字符的ASCII代码值。如果str是空字符串，返回0。如果str是NULL，返回NULL。
• `concat(str1,str2,...)` 返回来自于参数连结的字符串。如果任何参数是NULL，返回NULL。可以有超过2个的参数。一个数字参数被变换为等价的字符串形式。
• `length(str)` 返回字符串str的长度
• `locate(substr,str)` 返回子串substr在字符串str第一个出现的位置，如果substr不是在str里面，返回0.
• `instr(str,substr)` 返回子串substr在字符串str中的第一个出现的位置。
• `left(str,len)` 返回字符串str的最左面len个字符。
• `right(str,len)` 返回字符串str的最右面len个字符。
• `substr(str,pos)` 从字符串str的起始位置pos返回一个子串,和substr()一样
• `trim(str)` 返回字符串str，所有前缀或后缀被删除了
• `ltrim(str)` 返回删除了其前置空格字符的字符串str
• `rtrim(str)`返回删除了其拖后空格字符的字符串str。
• `replace(str,from_str,to_str)` 返回字符串str，其字符串from_str的所有出现由字符串to_str代替。
• `repeat(str,count)` 返回由重复count次的字符串str组成的一个字符串。如果count <= 0，返回一个空字符串。如果str或count是NULL，返回NULL。
• `reverse(str)` 返回颠倒字符顺序的字符串str
• `insert(str,pos,len,newstr)` 返回字符串str，在位置pos起始的子串且len个字符长的子串由字符串newstr代替。
• `substring_index(str,delim,count)` 从str中找到delim字符串按下标获取字符串
• 日期和时间函数
• 条件判断函数
• 系统信息函数
• `version()` 函数返回数据库的版本号
• `connection_id()` 函数返回服务器的连接数，也就是到现在为止MySQL服务的连接次数
• `database()` 和 `schema()` 返回当前数据库名
• 获取用户名的函数
• 获取字符串的字符集和排序方式的函数
• 获取最后一个自动生成的ID值的函数
• 加密函数
• 加密函数 `md5(str)`
• 加密函数 `encode(str,pswd_str)`
• 解密函数 `decode(crypt_str,pswd_str)`
• 格式化函数
• 其他函数

MySQL数据库中提供了很丰富的函数。MySQL函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等。通过这些函数，可以简化用户的操作。例如，字符串连接函数可以很方便的将多个字符串连接在一起。

数学函数

数学函数是MySQL中常用的一类函数。主要用于处理数字，包括整型、浮点数等。数学函数包括绝对值函数、正弦函数、余弦函数、获取随机数的函数等

abs(X) 返回X的绝对值

-- abs(X):返回X的绝对值
select abs(-100)

mod(N,M) 或 % 返回 N 被 M 除的余数

-- mod(N,M)或%:返回N被M除的余数
select mod(10,3);
select 10 % 3;

floor(X) 返回不大于X的最大整数值。

-- floor(X):返回不大于X的最大整数值。 
select floor(1.23), floor(-1.23);

ceiling(X) 返回不小于X的最小整数值。

-- ceiling(X):返回不小于X的最小整数值。
select ceiling(1.23), ceiling(-1.23);

round(X) 返回参数X的四舍五入的一个整数。

-- round(X) :返回参数X的四舍五入的一个整数。 
select round(1.23), round(-1.53);

字符串函数

ascii(str) 返回字符串str的最左面字符的ASCII代码值。如果str是空字符串，返回0。如果str是NULL，返回NULL。

-- ascii(str):返回字符串str的最左面字符的ASCII代码值。如果str是空字符串，返回0。如果str是NULL，返回NULL。 
select ascii('2'),ascii(2),ascii('dx'),ascii(''),ascii(null),ascii('null');

concat(str1,str2,...) 返回来自于参数连结的字符串。如果任何参数是NULL，返回NULL。可以有超过2个的参数。一个数字参数被变换为等价的字符串形式。

-- concat(str1,str2,...):返回来自于参数连结的字符串。如果任何参数是NULL，返回NULL。可以有超过2个的参数。一个数字参数被变换为等价的字符串形式。 
select concat('a','b',0),concat(1,2),concat(0.23), concat('a','b',null);

length(str) 返回字符串str的长度

-- length(str):返回字符串str的长度
select length(0), length('abc'),length(null),length(0.001),length('null');

locate(substr,str) 返回子串substr在字符串str第一个出现的位置，如果substr不是在str里面，返回0.

-- locate(substr,str):返回子串substr在字符串str第一个出现的位置(下标从1开始)，如果substr不是在str里面，返回0. 
select locate('acc', 'abc123acc');

instr(str,substr) 返回子串substr在字符串str中的第一个出现的位置。

-- instr(str,substr):返回子串substr在字符串str中的第一个出现的位置, 与select locate('acc', 'abc123acc');相反。
select instr('abc123acc', 'acc');

left(str,len) 返回字符串str的最左面len个字符。

-- left(str,len):返回字符串str的最左面len个字符。
select left('abcdefg', 3);

right(str,len) 返回字符串str的最右面len个字符。

-- right(str,len):返回字符串str的最右面len个字符
select right('abcdefg', 3);

substr(str,pos) 从字符串str的起始位置pos返回一个子串,和substr()一样

-- substr(str,pos):从字符串str的起始位置pos返回一个子串,和substr()一样。
select substr('abcdefg',3), substring('abcdefg', 3);

trim(str) 返回字符串str，所有前缀或后缀被删除了

-- trim(str):返回字符串str，所有前缀或后缀被删除了
select concat(trim('  ..123...  '), 'aaa') as a;

ltrim(str) 返回删除了其前置空格字符的字符串str

-- ltrim(str):返回删除了其前置空格字符的字符串str
select concat(ltrim('  123   '), 'aa');

rtrim(str)返回删除了其拖后空格字符的字符串str。

-- rtrim(str):返回删除了其拖后空格字符的字符串str。
select concat(rtrim('  123   '), 'aa');

replace(str,from_str,to_str) 返回字符串str，其字符串from_str的所有出现由字符串to_str代替。

-- replace(str,from_str,to_str):返回字符串str，其字符串from_str的所有出现由字符串to_str代替。
select replace('abc123', '12', '45');

repeat(str,count) 返回由重复count次的字符串str组成的一个字符串。如果count <= 0，返回一个空字符串。如果str或count是NULL，返回NULL。

-- repeat(str,count):返回由重复count次的字符串str组成的一个字符串。如果count <= 0，返回一个空字符串。如果str或count是NULL，返回NULL。 
select repeat('abc', 3);

reverse(str) 返回颠倒字符顺序的字符串str

-- reverse(str):返回颠倒字符顺序的字符串str
select reverse('abc');

insert(str,pos,len,newstr) 返回字符串str，在位置pos起始的子串且len个字符长的子串由字符串newstr代替。

-- insert(str,pos,len,newstr):返回字符串str，在位置pos起始的子串且len个字符长的子串由字符串newstr代替
select insert('abcdefg', 2, 3, '11');

substring_index(str,delim,count) 从str中找到delim字符串按下标获取字符串

select

-- substring_index(str,delim,count),从str中找到delim字符串按下标获取字符串
select 
  substring_index('abcdecfg', 'c', 1),
  substring_index('abcdecfg', 'c', - 1),
  substring_index('abcdecfg', 'c', 2),
  substring_index('abcdecfg', 'c', 3);

日期和时间函数

-- WEEKDAY(date):返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。
select weekday(now()) + 1;

-- DAYOFMONTH(date):返回date的月份中的日期，在1到31范围内
select dayofmonth(now());

-- DAYOFYEAR(date):返回date在一年中的日数, 在1到366范围内。
select dayofyear(now());

-- MONTH(date):返回date的月份，范围1到12
select month(now());

-- DAYNAME(date):返回date的星期名字
select dayname(now());

-- MONTHNAME(date) :返回date的月份名字。 
select monthname(now());

-- QUARTER(date):返回date一年中的季度，范围1到4。
select quarter(now());

-- WEEK(date,first):对于星期天是一周的第一天的地方，有一个单个参数，返回date的周数，范围在0到52。
-- 2个参数形式WEEK()允许你指定星期是否开始于星期天或星期一。如果第二个参数是0，星期从星期天开始，如果第二个参数是1，从星期一开始。 

select week(now());
select week(now(), 0);
select week(now(), 1);

-- YEAR(date):返回date的年份，范围在1000到9999。
select year(now());

-- HOUR(time):返回time的小时，范围是0到23。
select hour(now());

-- MINUTE(time):返回time的分钟，范围是0到59。 
select minute(now());

-- SECOND(time):回来time的秒数，范围是0到59。 
select second(now());

-- DATE_ADD(date,INTERVAL expr type) ,进行日期增加的操作，可以精确到秒
select date_add(now(), interval 1 day);
select date_add(now(), interval 3 hour);
select date_add(now(), interval 2 minute);
select date_add(now(), interval 3 second);
select date_add(now(), interval '1:1' minute_second); -- 同时加分、秒
select date_add(now(), interval '1:1' hour_minute); -- 同时加时、分
select date_add(now(), interval '1:1' day_hour); -- 同时加天、时
select date_add(now(), interval '1' month);
select date_add(now(), interval '1:1' year_month);-- 同时加年、月
select date_add(now(), interval '1 0:0:3' day_second);-- 同时加年、月

DATE_SUB(date,INTERVAL expr type) ，进行日期减少的操作，可以精确到秒, 和上面相加用法一样

-- 获取当前时间：以‘YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS格式返回当前的日期和时间 
select now();

-- current_date():以‘YYYY-MM-DD’或YYYYMMDD格式返回今天日期值，取决于函数在一个字符串还是数字上下文被使用。
select current_date();

-- CURRENT_TIME:以‘HH:MM:SS’或HHMMSS格式返回当前时间值
select current_time();

-- 时间格式化date_format()
select date_format(now(), '%Y-%m-%d %H:%i:%S');
select date_format('2017-09-20 08:30:45',   '%Y-%m-%d %H:%i:%S');

-- TO_DAYS函数 返回一个天数! 啊哈？什么天数? 从年份0开始的天数 
select count(1) from `trade$platform_user` as u where to_days(now()) = to_days(u.`addtime`)

条件判断函数

-- IF(expr1,expr2,expr3)如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，则 IF()的返回值为expr2; 
-- 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。
select if(1 > 2, 'true', 'false');
select if(1 < 2, 'true', 'false');

-- ifnull(expr1, expr2), 如果expr1为null, 则返回expr2
select ifnull(null, 0);
select ifnull('null', 1);

-- Strcmp(str1,str2):如果str1>str2返回1,str1=str2反回0，str1<str2返回-1)
select strcmp('text', 'text2'); 
select strcmp('text2', 'text'); 
select strcmp('text', 'text');

-- case when then else end
select 
  u.type,
  case
    when u.type = 0 
    then '用户' 
    when u.type = 1 
    then '商户' 
    else '未知' 
  end 
from
  `trade$platform_user` as u ;

系统信息函数

系统信息函数用来查询MySQL数据库的系统信息。例如，查询数据库的版本，查询数据库的当前用户等。本小节将详细讲解系统信息函数的作用和使用方法。

version() 函数返回数据库的版本号

-- version()函数返回数据库的版本号；
select version();

connection_id() 函数返回服务器的连接数，也就是到现在为止MySQL服务的连接次数

-- connection_id()函数返回服务器的连接数，也就是到现在为止MySQL服务的连接次数；
select connection_id();

database() 和 schema() 返回当前数据库名

-- database()和schema()返回当前数据库名
select database(), schema();

获取用户名的函数

-- 获取用户名的函数
select user(), system_user(), session_user(), current_user();

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

-- 获取字符串的字符集和排序方式的函数
-- CHARSET(str)函数返回字符串str的字符集，一般情况这个字符集就是系统的默认字符集；COLLATION(str)函数返回字符串str的字符排列方式
select charset('abc'), collation('abc');

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

-- 获取最后一个自动生成的ID值的函数
-- LAST_INSERT_ID()函数返回最后生成的AUTO_INCREMENT值
select last_insert_id();

加密函数

加密函数 md5(str)

-- 加密函数md5(str)
-- md5(str)函数可以对字符串str进行加密。MD5(str)函数主要对普通的数据进行加密。下面使用MD5(str)函数为字符串“abcd”加密。
select md5('111111');

加密函数 encode(str,pswd_str)

-- 加密函数encode(str,pswd_str)
-- encode(str,pswd_str)函数可以使用字符串pswd_str来加密字符串str。加密的结果是一个二进制数，必须使用BLOB类型的字段来保存它。
select encode('aaa', 'abc');

解密函数 decode(crypt_str,pswd_str)

-- 解密函数decode(crypt_str,pswd_str)
-- decode(crypt_str,pswd_str)函数可以使用字符串pswd_str来为crypt_str解密。crypt_str是通过ENCODE(str,pswd_str)加密后的二进制数据。字符串pswd_str应该与加密时的字符串pswd_str是相同的。
-- 下面使用DECODE(crypt_str,pswd_str)为ENCODE(str,pswd_str)加密的数据解密。
select decode('111111', 'adsadskdjkasjdksjdkas');

格式化函数

-- FORMAT(x,n)函数可以将数字x进行格式化，将x保留到小数点后n位。这个过程需要进行四舍五入。
select format(1.23458, 2), format(1.236545, 2);

---

其他函数

-- ASCII(s)返回字符串s的第一个字符的ASCII码；BIN(x)返回x的二进制编码；
-- HEX(x)返回x的十六进制编码；OCT(x)返回x的八进制编码；CONV(x,f1,f2)将x从f1进制数变成f2进制数。
select ascii('abc'), bin('abc'), hex('abc'), oct('abc');
select conv(2000, 10, 2);

-- IP地址与数字相互转换的函数
-- INET_ATON(IP)函数可以将IP地址转换为数字表示；INET_NTOA(n)函数可以将数字n转换成IP的形式。
-- 其中，INET_ATON(IP)函数中IP值需要加上引号。这两个函数互为反函数。
select inet_aton('192.168.0.118');
select inet_ntoa('3232235638');

-- 加锁函数和解锁函数
-- GET_LOCT(name,time)函数定义一个名称为nam、持续时间长度为time秒的锁。如果锁定成功，返回1；如果尝试超时，返回0；如果遇到错误，返回NULL。
-- RELEASE_LOCK(name)函数解除名称为name的锁。如果解锁成功，返回1；如果尝试超时，返回0；如果解锁失败，返回NULL；
-- IS_FREE_LOCK(name)函数判断是否使用名为name的锁。如果使用，返回0；否则，返回1。

-- BENCHMARK(count,expr)函数将表达式expr重复执行count次，然后返回执行时间。该函数可以用来判断MySQL处理表达式的速度。
select benchmark(1000000, 1*1/1);

-- 改变字符集的函数
-- CONVERT(s USING cs)函数将字符串s的字符集变成cs
select charset('abc'), charset(convert('abc' using 'gbk'));

-- CAST(x AS type)和CONVERT(x,type)这两个函数将x变成type类型。
-- 这两个函数只对BINARY、CHAR、DATE、DATETIME、TIME、SIGNED INTEGER、UNSIGNED INTEGER这些类型起作用。
-- 但两种方法只是改变了输出值的数据类型，并没有改变表中字段的类型。
select cast(111 as char);

原文地址：https://blog.csdn.net/sinat_38899493/article/details/78710482