数据库基于 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 resultELSE 是一个分支,若分支中compare_valuevalue 相等,则返回对应的 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 进行判定,若结果为 trueexpr1 <> 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)

expr1expr2 相等,则返回 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)。 若 exprpat 中任何一个为 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 二者任意一个为空时,将返回 NULLRLIKEREGEXP 的同义词, 是为了兼容 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_DATECURRENT_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_TIMECURRENT_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_TIMESTAMPCURRENT_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 的值)。expr1expr2 为日期或 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 是一个 DATETIMEDATE 值,用来指定起始日期。 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

About Me
WEB开发工程师
GitHub Repos