MySQL 中函数列举

一、ifNull(expr1,expr2)

ifNull(expr1,expr2)类似于 Oracle 中的 NVL 函数。含义是：如果第一个参数不为空，则返回第一个参数，否则返回第二个参数。

二、if(expr1,expr2,expr3)

if(expr1,expr2,expr3)类似于 Oracle 中的 NVL2 函数。含义是：如果第一个表达式的值为 true(不为 0 或不为 null)，则返回第二个参数的值，否则返回第三个参数的值。

三、dateDiff函数(begin减去end)

dateDiff(begin,end);计算两个 date，dateTime 或 timeStamp 值之间的天数。该函数接受两个任何有效日期或日期时间值的参数。如果传递dateTime或timeStamp值，则dateDiff函数仅将日期部分用于计算，并忽略时间部分。

SELECT 
    NOW() a,
    -- NOW() 函数返回当前的日期和时间

	DATEDIFF(NOW(),'2008-08-08') b,
    -- begin>end，返回正数

	DATEDIFF('2008-08-08',NOW()) c,
    -- end<begin，返回负数

    DATEDIFF(NOW(),'2008-08-08') / 7  as weeks1,
	ROUND(DATEDIFF(NOW(),'2008-08-08') / 7, 2) as weeks2,
	DATEDIFF(NOW(),'2008-08-08') / 30 as months1,
    ROUND(DATEDIFF(NOW(),'2008-08-08') / 30,2) as months2
    -- 将dateDiff函数的返回值除以7或30，可以计算周或月作为间隔时间

四、 timeStampDiff函数

1️⃣区别于dateDiff(begin,end);，timeStampDiff(unit,begin,end);返回end-begin的结果，其中begin和end是 date 或 dateTime 表达式。

2️⃣该函数允许其参数具有混合类型，例如，begin是 date 值，end可以是 dateTime 值。 如果使用 date 值，则timeStampDiff函数将其视为时间部分为“00:00:00”的 dateTime 值。

3️⃣unit参数是确定(end-begin)的结果的单位，表示为整数。 以下是有效单位：

• MICROSECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• MONTH
• QUARTER
• YEAR

SELECT 
    TIMESTAMPDIFF(YEAR, '2010-01-01', '2000-12-31') YEAR,
    -- 不足10年，为9。而不是(2000-2010)

    TIMESTAMPDIFF(MONTH, '2000-01-31', '2000-08-08') MONTH,
    -- 不足7个月，为6。而不是(8-1)

    TIMESTAMPDIFF(DAY, '2010-01-01', '2000-01-01') DAY,

    TIMESTAMPDIFF(MINUTE, '2000-01-01 10:00:00', '2000-01-01 10:45:59') MINUTE,
    -- 差值应该是45分59秒。 但是，将unit参数传递为MINUTE，此时，函数返回45分钟

    TIMESTAMPDIFF(SECOND, '2000-01-01 00:00:00', '2000-01-01 00:45:59') SECOND
    -- 注：45分59秒= 45×60 + 59(秒)= 2759秒

五、截取字符串subString(str, pos)

subString(str, pos)等同于subStr(str, pos)

select 
	name a,
	SUBSTRING(name FROM 1 FOR 1) b,
	SUBSTRING(name ,1,1) c,
	SUBSTRING(name ,3,1) d,
	substring(name ,3,2) e
from tab;

select substr('abcde',2,3),substr('abcde',2),substr('abcde',-4,5) from dual;

六、trim( )、rTrim( )、lTrim( )

SELECT
  TRIM('   solos   ') a,
  TRIM(LEADING 's' FROM 'solos') b,
  TRIM(LEADING 'S' FROM 'solos') c,
  TRIM(TRAILING 's' FROM 'solos') d,
  TRIM(BOTH 's' FROM 'solos') e,
  LTRIM('   solos   ') f,
  RTRIM('   solos   ') g,
  TRIM('a' from 'abacde') h;

七、concat()

1️⃣concat(str1, str2,...)将多个字符串连接成一个字符串。返回结果为连接参数产生的字符串，如果有任何一个参数为 null，则返回值为 null。

2️⃣concat(str1, seperator,str2,seperator,...)返回结果为连接参数产生的字符串并且有分隔符，如果有任何一个参数为 null，则返回值为 null。

select * from ext_area;   -- 3193
select * from ext_area where area like concat('%');      --  3193
select * from ext_area where area like concat('%%');     --  3193
select * from ext_area where area like concat('%''%');   --  null
select * from ext_area where area like concat('%' '%');  --  3193

select concat('hello',',word!')  str; --  结果为：'hello,word!'

八、concat_ws()

区别于concat()，concat_ws(separator, str1, str2, ...)虽然也是将多个字符串连接成一个字符串，但是可以一次性指定分隔符【concat_ws 就是 concat with separator】

说明：第一个参数指定分隔符。需要注意的是分隔符不能为 null，如果为 null，则返回结果为 null。

九、group_concat()

以 deptId 分组，把 name 的[去重]值打印在一行，逗号分隔(默认)

select deptId,group_concat(distinct name) from tab group by deptId;

执行 sql 之后：

注：group_concat 只有与 group by 同时使用才能产生效果。

十、length： 获取字节个数(utf-8 一个汉字为3个字节，gbk为2个字节)

SELECT
	LENGTH( 'crud' ) a,
	LENGTH( '哈crud' ) b,
	length( 'a bc' ) c 
FROM
	DUAL;

十一、大小写转换

SELECT
	lower( 'NAME' ) a,
	upper( 'NaMe' ) b,
	CONCAT( UCASE( LEFT ( 'name', 1 ) ), SUBSTRING( 'name', 2 ) ) c 
FROM
	DUAL;

十二、instr：返回子串第一次出现的索引，如果没有则返回0

SELECT
	INSTR( '哈喽啊', '哈' ) a, 
	INSTR( '哈喽啊', '哦' ) b;
 -- a为1；b为0 （mysql是从1开始算位数）

十三、用指定字符实现左/右填充指定长度

SELECT
	LPAD( 'crud', 6, '*' ) a,
	LPAD( 'crud', 2, '*' ) b,
	RPAD( 'crud', 7, '*' ) c,
	RPAD( 'crud', 2, '*' ) d

十四、替换函数replace()

select 
  REPLACE('6166666','1','ac') a,
  REPLACE('666A666','A','ac') b
from dual;

---

/四舍五入/
select round(23.44),round(23.44,1),round(53.44,-2.2) from dual;

/向上取整，向下取整/
select ceil(23.44),floor(23.44) from dual;

/绝对值/
select abs(-33.42),abs(33.42),abs(0) from dual;

/求余函数/
select mod(5,2) from dual;

/m的n次方/
select power(2,3),power(null,2) from dual;

/取平方根/
select sqrt(16) from dual;

/数学函数/
select tan(6) from dual;

/拼接字符串/
select ‘ab’||‘cd’,concat(‘ab’,‘cd’) from dual;

/获取系统时间/
select sysdate from dual;

/当前日期加整数月/
select add_months(sysdate,3),add_months(sysdate,-3) from dual;

/返回下一个指定天的日期/
select next_day(sysdate,‘星期一’) from dual;

/返回date所在月的最后一天/
select last_day(sysdate) from dual;

/计算两个日期之间间隔的月数/
select months_between(‘18-12月-15’,‘18-1月-15’) from dual;

/获取年月日时分秒/
select extract(year from sysdate) YEAR,extract(month from sysdate) month,extract(day from sysdate) DAY from dual;

FIND_IN_SET(str,strlist)

1. 假如字符串str在由N子链组成的字符串列表strlist中，则返回值的范围在1到N之间。
2. 一个字符串列表就是一个由一些被‘,’符号分开的自链组成的字符串。
3. 如果第一个参数是一个常数字符串，而第二个是typeSET列，则FIND_IN_SET()函数被优化，使用比特计算。
4. 如果str不在strlist或strlist为空字符串，则返回值为0。
5. 如任意一个参数为NULL，则返回值为NULL。这个函数在第一个参数包含一个逗号(‘,’)时将无法正常运行。

strlist：一个由英文逗号“,”链接的字符串，例如：“a,b,c,d”，该字符串形式上类似于SET类型的值被逗号给链接起来。

示例：SELECT FIND_IN_SET('b','a,b,c,d') //返回值为2，即第2个值。

MySQL中的 IN和FIND_IN_SET的查询问题

原来以为mysql可以进行这样的查询
1️⃣select id, list, name from table where 'Uzi' IN (list)
注：1. table含有三个字段id:int, list:varchar(255), name:varchar(255)。实际上这样是不行的。

测试代码：

CREATE TABLE test (
  id int(8) NOT NULL auto_increment,
  name varchar(255) NOT NULL,
  list varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
)
insert into test values (1, 'name', 'Uzi,xiaohu,letme');
insert into test values (2, 'name2', 'xiaohu,Uzi,letme');
insert into test values (3, 'name3', 'letme,Uzi,xiaohu');

test1:sql =select * from test where 'Uzi' IN ('list');得到结果空值。
test2:sql =select * from test where FIND_IN_SET('Uzi','list');得到三条数据：

1	name	Uzi,xiaohu,letme
2	name2	xiaohu,Uzi,letme
3	name3	letme,Uzi,xiaohu

修改表数据：
update test set list='Uzi' where id='1';
然后执行test1的sql，可以返回一条结果。

再来看看这个：
2️⃣select id, list, name from table where 'Uzi' IN ('libk', 'zyfon', 'Uzi');

1️⃣和2️⃣到底有什么区别呢？为什么第一条不能取得正确的结果，而第二条却能取得结果？

原因其实是1️⃣中(list)，list是变量， 而2️⃣中(‘libk’, ‘zyfon’, ‘Uzi’)是常量。所以如果要让1️⃣能正确工作，需要用find_in_set()：
select id, list, name from table where FIND_IN_SET( 'Uzi' , list)1️⃣的改进版。

总结：所以如果list是常量，则可以直接用IN，否则要用FIND_IN_SET()函数。

十五、CAST() 和 CONVERT()

MySQL 的CAST()和CONVERT()函数可用来获取一个类型的值，并产生另一个类型的值。两者具体的语法如下：

CAST(value as type);
CONVERT(value, type);

就是CAST(xxx AS 类型) CONVERT(xxx,类型)。可以转换的类型是有限制的。这个类型可以是以下值其中的一个：

• 二进制，同带binary前缀的效果 : BINARY
• 字符型，可带参数 : CHAR()
• 日期 : DATE
• 时间: TIME
• 日期时间型 : DATETIME
• 浮点数 : DECIMAL
• 整数 : SIGNED(字段取值范围是-128 - 127)
• 无符号整数 : UNSIGNED(字段取值范围是0-255)
  例：
  SELECT CONVERT('23',SIGNED) a,CAST('125e342.83' AS signed) b,CAST('3.35' AS signed) c;
  结果如下：
  

应用

在MySQL中，进行中文排序和查找的时候，对汉字的排序和查找结果往往都是错误的。 这种情况在MySQL的很多版本中都存在。之所以这样，是因为MySQL在查询字符串时是大小写不敏感的，在编绎MySQL时一般以ISO-8859字符集作为默认的字符集，因此在比较过程中中文编码字符大小写转换造成了这种现象。

解决方法：
1️⃣对于包含中文的字段加上”binary”属性，使之作为二进制比较，例如将name char(10)改成name char(10) binary。
2️⃣如果使用源码编译MySQL，可以编译MySQL时使用 –with–charset=gbk参数，这样MySQL就会直接支持中文查找和排序了（默认的是latin1）。也可以用extra-charsets=gb2312,gbk来加入多个字符集。
3️⃣如果不想对表结构进行修改或者重新编译MySQL，也可以在查询语句的 order by 部分使用 CONVERT 函数。例如：

select * from tab order by CONVERT(ChineseColumnName USING gbk);

UTF8 默认校对集是utf8_general_ci，它不是按照中文来的。需要强制让MySQL按中文来排序。

十六、DATE_FORMAT(date,format)

DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。date 参数是合法的日期。format 规定日期/时间的输出格式。

可以使用的格式有：
格式 描述
%a 缩写星期名
%b 缩写月名
%c 月，数值
%D 带有英文前缀的月中的天
%d 月的天，数值(00-31)
%e 月的天，数值(0-31)
%f 微秒
%H 小时 (00-23)
%h 小时 (01-12)
%I 小时 (01-12)
%i 分钟，数值(00-59)
%j 年的天 (001-366)
%k 小时 (0-23)
%l 小时 (1-12)
%M 月名
%m 月，数值(00-12)
%p AM 或 PM
%r 时间，12-小时（hh:mm:ss AM 或 PM）
%S 秒(00-59)
%s 秒(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(),'%b %d %Y %h %i %p');
select DATE_FORMAT(NOW(),'%m-%d-%Y');
select DATE_FORMAT(NOW(),'%d %b %y');
select DATE_FORMAT(NOW(),'%d %b %Y %T:%f');

结果如下：

Jan 11 2020 01 17 PM
01-11-2020
11 Jan 20
11 Jan 2020 13:17:40:000000