数据库基于 MySQL Server 5.6

由于数据库自带函数的效率问题，对于海量数据的处理时，将加大数据库压力，使之成为系统的瓶颈。但对于大部分的初创等小型公司来说，数据并不多时，数据库函数并不能成为系统瓶颈，这时使用数据库自带的函数将给我们带来很大方便。所以这两天就梳理了一些MySQL中字符串、日期和时间等比较常用的函数，和各位分享。

控制流程函数

CASE

• CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result …] [ELSE result] END
• CASE WHEN [condition] THEN result [WHEN [condition] THEN result …] [ELSE result] END

这里有两个方案，第一个方案对 value 判定，每个 WHEN compare_value THEN result 或 ELSE 是一个分支，若分支中compare_value 与 value 相等，则返回对应的 THEN 后的 result，若所有 WHEN 后的 compare_value 都不与 value 想的呢过，则返回 ELSE 后的 result，若没有 ELSE，则返回 NULL。
示例：

SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;

结果

one

第二个方案对每个 WHEN 后的表达式进行判定，若判定结果为 true，则返回对应的 THEN 后的结果，若所有 WHEN 后的判定结果均为 false ，则返回 ELSE 后的 result，若没有 ELSE，则返回 NULL。
示例：

SELECT CASE WHEN 1 > 0 THEN 'true' ELSE 'false' END;

结果

true

IF

• IF(expr1, expr2, expr3)

IF 函数类似与 Java 或其他语言中的三元运算符，即对 expr1 进行判定，若结果为 true（expr1 <> 0 and expr1 <> NULL），则返回 expr2，否则，返回expr3。
示例1：

SELECT IF(1 > 2, 2, 3);

结果：

3

示例2：

SELECT IF(1 < 2, 'yes', 'no');

结果：

yes

示例3：

SELECT IF(STRCMP('test', 'test1'), 'no', 'yes');

结果：

no

IFNULL

• IFNULL(expr1, expr2)

IFNULL 语句与上面的 IF() 函数有点类似对 expr1 进行判定，若 expr1 不为空，则返回 expr1，否则返回 expr2。
示例1：

SELECT IFNULL(1, 0);

结果：

1

示例2：

SELECT IFNULL(NULL, 10);

结果：

10

示例3：

SELECT IFNULL(1/0, 'yes');

结果：

yes

NULLIF

• NULLIF(expr1, expr2)

若 expr1 与 expr2 相等，则返回 NULL，否则返回 expr1。和 CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END 效果相同。
示例1：

SELECT NULLIF(1, 1);

结果：

NULL

示例2：

SELECT NULLIF(1, 2);

结果：

1

字符串函数

字符串比较函数

LIKE

• expr LIKE pat [ESCAPE ‘escape_char’]

模式匹配，使用SQL简单正规表达式比较。返回1 (TRUE) 或 0 (FALSE)。 若 expr 或 pat 中任何一个为 NULL,则结果为 NULL。
模式不需要为文字字符串，可以被指定为一个字符串表达式或表列。
在模式中可以同 LIKE 一起使用以下两种通配符：

字符	说明
%	匹配任何数目的字符，甚至包括零个字符
_	只能匹配一个字符

示例1：

SELECT 'David!' LIKE 'David_';

结果：

1
示例2：

SELECT 'David!' LIKE '%D%v%';

结果：

1

若要将通配符视为普通字符，以便在字符串中匹配通配符，可将转义符(默认为\)置于此通配符前面即可。
示例1：

SELECT 'David!' LIKE 'David\_';

结果：

0

示例2：

SELECT 'David_' LIKE 'David\_';

结果：

1

要指定一个不同的转义字符,可使用 ESCAPE 语句。
示例：

SELECT 'David_' LIKE 'David|_' ESCAPE '|';

结果：

1

转义序列可以为空，也可以是一个字符的长度。
字符串比较不区分大小写，除非其中的一个比较对象是大小写敏感的（使用区分大小写敏感的字符集或者是一个二进制字符串）。
示例1：

SELECT 'abc' LIKE 'ABC';

结果：

1

示例2：

SELECT 'abc' LIKE _latin1 'ABC' COLLATE latin1_general_cs;

结果：

0

示例3：

SELECT 'abc' LIKE _latin1 'ABC' COLLATE latin1_bin;

结果：

0

示例4：

SELECT 'abc' LIKE BINARY 'ABC';

结果：

0

作为对标准 SQL 的扩展，MySQL 允许 LIKE 函数作用在数字表达式上。
示例：

SELECT 10 LIKE '1%';

结果：

1

NOT LIKE

• expr NOT LIKE pat [ESCAPE ‘escape_char’]

和 NOT (expr LIKE pat [ESCAPE ‘escape_char’]) 效果相同。

正则表达式函数

REGEXP

• expr REGEXP pat
• expr RLIKE pat

用给定的正则表达式 pat 匹配字符串 expr，如果匹配成功，则返回 1。否则返回 0。正则表达式 pat 和字符串 expr 二者任意一个为空时，将返回 NULL。RLIKE 是 REGEXP 的同义词, 是为了兼容 mSQL。
正则表达式 pat 不需要为字符串。例如，可以被指定为一个字符串表达式或表列。
REGEXP 是不区分大小写的，除非跟二进制字符串一起使用。
示例1：

SELECT 'Monty!' REGEXP '.*';

结果：

1

示例2：

SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';

结果：

1

示例3：

SELECT 'a' REGEXP 'A', 'a' REGEXP BINARY 'A';

结果：

1 0

示例5：

SELECT 'a' REGEXP '^[a-d]';

结果：

1

NOT REGEXP

• expr NOT REGEXP pat
• expr NOT RLIKE pat

相当于 NOT (expr REGEXP pat)。

数值函数

数学函数

TRUNCATE

• TRUNCATE (X, D)

返回被舍去至小数点后 D 位的数字 X。若 D 的值为 0, 则结果不带有小数点或不带有小数部分。可以将 D 设为负数,若要截去（归零） X小数点左起第 D 位（含）开始后面所有低位的值。

日期和时间函数

ADDDATE

• ADDDATE(date, INTERVAL expr unit)
• ADDDATE(expr, days)

当被第二个参数的 INTERVAL 格式激活后， ADDDATE() 的用法和 DATE_ADD() 的相同。相关函数 SUBDATE() 的用法和 DATE_SUB() 相同。对于 INTERVAL 参数上的信息 ，请参见关于 DATE_ADD() 的论述。
示例1：

SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY);

结果：

2008-02-02

示例2：

SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);

结果：

2008-02-02

ADDTIME

• ADDTIME(expr1, expr2)

ADDTIME() 将 expr2 添加至 expr 然后返回结果。expr 是一个时间或时间日期表达式，而 expr2 是一个时间表达式。

CURDATE

• CURDATE()

将当前日期按照 ‘YYYY-MM-DD’ 或 YYYYMMDD 格式的值返回，具体格式根据函数用在字符串或是数字语境中而定。
示例1：

SELECT CURDATE();

结果：

2016-07-29

示例2：

SELECT CURDATE() + 0;

结果：

20060729

CURRENT_DATE

• CURRENT_DATE
• CURRENT_DATE()

CURRENT_DATE 和 CURRENT_DATE() 的用法与 CURDATE() 相同。

CURTIME

• CURTIME([fsp])

将当前时间以 ‘HH:MM:SS’ 或 HHMMSS 的格式返回，具体格式根据函数用在字符串或是数字语境中而定。该值表示当前时区的时间。从 MySQL 5.6.4 开始，fsp 参数用来指定返回值中小数位的长度，该参数的取值范围 0 - 6。在 MySQL5.6.4 之前，给CURTIME() 函数传递任何参数都会被忽略。
示例1：

SELECT CURTIME();

结果：

16:38:39

示例2：

SELECT CURTIME();

结果：

163927

CURRENT_TIME

• CURRENT_TIME
• CURRENT_TIME([fsp])

CURRENT_TIME 和 CURRENT_TIME() 的用法与 CURTIME() 函数相同。

NOW

• NOW([fsp])

返回当前日期和时间值，其格式为 ‘YYYY-MM-DD HH:MM:SS’ 或YYYYMMDDHHMMSS， 具体格式取决于该函数是否用在字符串中或数字语境中。该值表示当前时区的时间。从 MySQL 5.6.4 开始，fsp 参数用来指定返回值中小数位的长度，该参数的取值范围 0 - 6。在 MySQL5.6.4 之前，给 NOW() 函数传递任何参数都会被忽略。
示例1：

SELECT NOW();

结果：

2016-07-29 16:49:28

示例2：

SELECT NOW() + 0;

结果：

20160729165026

NOW() 返回一个固定时间，该时间指示了该开始执行的时间（在存储函数或者触发器中，NOW() 函数返回存储函数或者触发器开始执行的时间）。这同 SYSDATE() 的运行有所不同，SYSDATE() 函数返回它自身执行的时间。
示例1：

SELECT NOW(), SLEEP(2), NOW();

结果：

NOW()	SLEEP(2)	NOW()
2016-07-29 17:16:40	0	2016-07-29 17:16:40

示例2：

SELECT SYSDATE(), SLEEP(2), SYSDATE();

结果：

SYSDATE()	SLEEP(2)	SYSDATE()
2016-07-29 17:52:24	0	2016-07-29 17:52:26

CURRENT_TIMESTAMP

• CURRENT_TIMESTAMP
• CURRENT_TIMESTAMP([fsp])

CURRENT_TIMESTAMP 和 CURRENT_TIMESTAMP() 的用法与 NOW() 函数相同。

DATE

• DATE(expr)

提取日期或时间日期表达式 expr 中的日期部分，若没有提取到，则返回 NULL。
示例：

SELECT DATE('2016-12-31 01:02:03');

结果：

2016-12-31

DATEDIFF

• DATEDIFF(expr1, expr2)

返回起始时间 expr1 和结束时间 expr2 之间的天数（即表达式 expr1 - expr2 的值）。expr1 和 expr2 为日期或 date-and-time 表达式。计算中只用到这些值的日期部分。
示例1：

SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');

结果：

1

示例2：

SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');

结果：

-31

DATE_ADD, DATE_SUB

• DATE_ADD(date, INTERVAL expr unit)
• DATE_SUB(date, INTERVAL expr unit)

这两个函数执行日期运算。参数 date 是一个 DATETIME 或 DATE 值，用来指定起始日期。 expr 是一个表达式，用来指定从起始日期(即参数 date)添加或减去的日期间隔值。expr 是一个字符串；对于负值的日期间隔，它可以以一个 “-”开头。 type 为关键词，它指示了表达式被解释的方式。
关键词 INTERVA 及 unit 分类符均不区分大小写。
下表展示了unit 和 expr 参数的关系：

unit 值	期望的 expr 值
MICROSECOND	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEARS
SECOND_MICROSECOND	‘SECONDS.MICROSECONDS’
MINUTE_MICROSECOND	‘MINUTES:SECONDS.MICROSECONDS’
MINUTE_SECOND	‘MINUTES:SECONDS’
HOUR_MICROSECOND	‘HOURS:MINUTES:SECONDS.MICROSECONDS’
HOUR_SECOND	‘HOURS:MINUTES:SECONDS’
HOUR_MINUTE	‘HOURS:MINUTES’
DAY_MICROSECOND	‘DAYS HOURS:MINUTES:SECONDS.MICROSECONDS’
DAY_SECOND	‘DAYS HOURS:MINUTES:SECONDS’
DAY_MINUTE	‘DAYS HOURS:MINUTES’
DAY_HOUR	‘DAYS HOURS’
YEAR_MONTH	‘YEARS-MONTHS’

返回值取决于传递的参数。
示例1：

SELECT DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 DAY);

结果：

2011-01-01 23:59:59

示例2：

SELECT DATE_ADD('2100-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND);

结果：

2101-01-01 00:01:00

示例3：

SELECT DATE_SUB('2005-01-01 00:00:00', INTERVAL '1 1:1:1' DAY_SECOND);

结果：

2004-12-30 22:58:59

DATE_FORMAT

• DATE_FORMAT(date, format)

根据 format 字符串安排 date 值的格式。

说明符	说明
%a	工作日的缩写名称 (Sun..Sat)
%b	月份的缩写名称 (Jan..Dec)
%c	月份，数字形式(0..12)
%D	带有英语后缀的该月日期 (0th，1st，2nd，3rd，···)
%d	该月日期，数字形式 (00..31)
%e	该月日期，数字形式(0..31)
%f	微秒 (000000..999999)
%H	小时(00..23)
%h	小时(01..12)
%I	小时 (01..12)
%i	分钟，数字形式 (00..59)
%j	一年中的天数 (001..366)
%k	小时 (0..23)
%l	小时 (1..12)
%M	月份名称 (January..December)
%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位数)
%%	% 字符

示例1：

SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');

结果：

4th 00 Thu 04 10 Oct 277

示例2：

SELECT DATE_FORMAT('1999-01-01', '%X %V');

结果：

1998 52