MySQL常用日期处理函数和文本处理函数

常见日期处理函数

  • NOW([fsp])返回当前的日期和时间，格式为：'YYYY-MM-DD HH:MM:SS' 或 'YYYYMMDDHHMMSS'。具体使用哪种格式取决于NOW()函数使用的环境（字符串/数字）。

    mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2019-03-28 06:46:39 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW() + 0;
+----------------+
| NOW() + 0      |
+----------------+
| 20190328064659 |
+----------------+
1 row in set (0.00 sec)

    还可以在函数中提供一个fsp参数（0-6）来指定秒数的精度：

    mysql> SELECT NOW(6);
+----------------------------+
| NOW(6)                     |
+----------------------------+
| 2019-03-28 06:47:11.620058 |
+----------------------------+
1 row in set (0.00 sec)

    NOW()和SYSDATE()都可以返回当前时间，二者的区别在于，NOW()返回的时包含该函数的语句开始运行的时间，而SYSDATE()返回的是函数执行的时间。另一个区别是，在执行SET TIMESTAMP语句后，SYSDATE()的返回值不受影响，而NOW()则会返回设定的值。

  • CURDATE()返回当前的日期，格式为：'YYYY-MM-DD' 或 'YYYYMMDD'。具体使用哪种格式取决于该函数使用的环境（字符串/数字）。

    mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2019-03-28 |
+------------+
1 row in set (0.01 sec)

mysql> SELECT CURDATE() + 0;
+---------------+
| CURDATE() + 0 |
+---------------+
|      20190328 |
+---------------+
1 row in set (0.00 sec)

  • CURTIME([fsp])返回当前的时间，格式为：'HH:MM:SS' 或 'HHMMSS'。具体使用哪种格式取决于该函数使用的环境（字符串/数字）。还可以在函数中提供一个fsp参数（0-6）来指定秒数的精度：

    mysql> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 07:03:54  |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT CURTIME() + 0;
+---------------+
| CURTIME() + 0 |
+---------------+
|         70404 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT CURTIME(6) + 0;
+----------------+
| CURTIME(6) + 0 |
+----------------+
|   70416.578713 |
+----------------+
1 row in set (0.00 sec)

  • DATE(expr)提取日期表达式/日期时间表达式中的日期部分。

    mysql> SELECT DATE('2010-10-05 09:29:45');
+-----------------------------+
| DATE('2010-10-05 09:29:45') |
+-----------------------------+
| 2010-10-05                  |
+-----------------------------+
1 row in set (0.00 sec)

  • EXTRACT(unit FROM date)从日期表达式/日期时间表达式中提取给定的unit。

    mysql> SELECT EXTRACT(YEAR FROM '2008-09-12');
+---------------------------------+
| EXTRACT(YEAR FROM '2008-09-12') |
+---------------------------------+
|                            2008 |
+---------------------------------+
1 row in set (0.02 sec)

mysql> SELECT EXTRACT(QUARTER FROM '2008-09-12');
+------------------------------------+
| EXTRACT(QUARTER FROM '2008-09-12') |
+------------------------------------+
|                                  3 |
+------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT EXTRACT(YEAR_MONTH FROM '2008-09-12');
+---------------------------------------+
| EXTRACT(YEAR_MONTH FROM '2008-09-12') |
+---------------------------------------+
|                                200809 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT EXTRACT(HOUR_MICROSECOND FROM '2008-09-12 12:23:55.023111');
+-------------------------------------------------------------+
| EXTRACT(HOUR_MICROSECOND FROM '2008-09-12 12:23:55.023111') |
+-------------------------------------------------------------+
|                                                122355023111 |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

    常见的unit值和其对应的格式如下表：

    unit Value Expected expr Format
    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'

  • DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)这两个函数可以对日期进行数学计算。

    date参数指定了起始的日期/日期时间值

    expr参数指定了需要在起始日期的基础上增加/减少的时间区间。expr被视为一个字符串，可以以'-'开始去表示一个负的间隔。

    unit表示expr的格式，标明了expr该以何种方式进行翻译

    返回值可能为：

    1. DATE：当date参数为一个日期表达式，并且计算过程中只涉及了DATE, MONTH, DAY。
    2. DATETIME：当date参数为一个日期时间表达式/时间戳，或者unit值使用了HOURS, MINUTES, SECONDS。
    3. 除了1，2以外的其他情况均返回字符串。
    mysql> SELECT DATE_ADD('2009-03-11', INTERVAL 1 DAY);
+----------------------------------------+
| DATE_ADD('2009-03-11', INTERVAL 1 DAY) |
+----------------------------------------+
| 2009-03-12                             |
+----------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT DATE_SUB('2009-03-12', INTERVAL 1 YEAR);
+-----------------------------------------+
| DATE_SUB('2009-03-12', INTERVAL 1 YEAR) |
+-----------------------------------------+
| 2008-03-12                              |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD('2009-04-10 19:34:12', INTERVAL '25:48' MINUTE_SECOND);
+-----------------------------------------------------------------+
| DATE_ADD('2009-04-10 19:34:12', INTERVAL '25:48' MINUTE_SECOND) |
+-----------------------------------------------------------------+
| 2009-04-10 20:00:00                                             |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

  • DATEDIFF(expr1,expr2)返回expr1-expr2日期的差值(DAY)，expr1和expr2可以是日期表达式/日期时间表达式，但是只有日期部分会参与运算。

    mysql> SELECT DATEDIFF('2009-01-01','2008-01-01');
+-------------------------------------+
| DATEDIFF('2009-01-01','2008-01-01') |
+-------------------------------------+
|                                 366 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATEDIFF('2010-10-01 20:12:34','2010-09-30 12:34:22');
+-------------------------------------------------------+
| DATEDIFF('2010-10-01 20:12:34','2010-09-30 12:34:22') |
+-------------------------------------------------------+
|                                                     1 |
+-------------------------------------------------------+
1 row in set (0.01 sec)

  • DATE_FORMAT(date, format)根据format指定的格式返回日期。format是一个我们指定的模版，我们通过使用区分符来指定我们想要的某种特殊格式。

    mysql> SELECT DATE_FORMAT('2000-10-01','%b,%D,%Y');
+--------------------------------------+
| DATE_FORMAT('2000-10-01','%b,%D,%Y') |
+--------------------------------------+
| Oct,1st,2000                         |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT('2010-08-27','day of the week: %w');
+-------------------------------------------------+
| DATE_FORMAT('2010-08-27','day of the week: %w') |
+-------------------------------------------------+
| day of the week: 5                              |
+-------------------------------------------------+
1 row in set (0.00 sec)

format是一个包含有区分符(specifier)的字符串，常用的区分符有：

Specifier Description
%a Abbreviated weekday name (Sun..Sat)
%b Abbreviated month name (Jan..Dec)
%c Month, numeric (0..12)
%D Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d Day of the month, numeric (00..31)
%e Day of the month, numeric (0..31)
%f Microseconds (000000..999999)
%H Hour (00..23)
%h Hour (01..12)
%I Hour (01..12)
%i Minutes, numeric (00..59)
%j Day of year (001..366)
%k Hour (0..23)
%l Hour (1..12)
%M Month name (January..December)
%m Month, numeric (00..12)
%p AM or PM
%r Time, 12-hour (hh:mm:ss followed by AM or PM)
%S Seconds (00..59)
%s Seconds (00..59)
%T Time, 24-hour (hh:mm:ss)
%U Week (00..53), where Sunday is the first day of the week; WEEK() mode 0
%u Week (00..53), where Monday is the first day of the week; WEEK() mode 1
%V Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X
%v Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x
%W Weekday name (Sunday..Saturday)
%w Day of the week (0=Sunday..6=Saturday)
%X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y Year, numeric, four digits
%y Year, numeric (two digits)
%% A literal % character
%*x* x, for any “x” not listed above

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

    当使用INTERVAL作为第二个参数时，ADDDATE()与DATE_ADD()的用法是相同的。

    当使用days作为第二个参数时，MySQL会把days当作一个整数的天数添加到expr。

    mysql> SELECT ADDDATE('2011-10-01', 20);
+---------------------------+
| ADDDATE('2011-10-01', 20) |
+---------------------------+
| 2011-10-21                |
+---------------------------+
1 row in set (0.00 sec)

  • ADDTIME(expr1, expr2)

    expr1是一个时间表达式/日期时间表达式

    expr2是一个时间表达式

    ADDTIME()会在expr1的基础上加上expr2，并返回结果

    mysql> SELECT ADDTIME('2001-01-01 16:19:29', '00:10:01.1111');
+-------------------------------------------------+
| ADDTIME('2001-01-01 16:19:29', '00:10:01.1111') |
+-------------------------------------------------+
| 2001-01-01 16:29:30.111100                      |
+-------------------------------------------------+
1 row in set (0.00 sec)

  • DAY(date)是DAYOFMONTH()的同义词，返回date中的日期是该月的哪一天，范围1～31。

    mysql> SELECT DAY('2018-04-12 18:45:26');
+----------------------------+
| DAY('2018-04-12 18:45:26') |
+----------------------------+
|                         12 |
+----------------------------+
1 row in set (0.00 sec)

  • YEAR()返回日期所在的年份，范围1000～9999。

    mysql> SELECT YEAR('1999-03-04');
+--------------------+
| YEAR('1999-03-04') |
+--------------------+
|               1999 |
+--------------------+
1 row in set (0.00 sec)

常用的文本处理函数

  • LEFT(str,len)返回str字符串中最左侧的len个字符，如果参数为NULL则返回NULL。

    mysql> SELECT LEFT('lifetime',4);
+--------------------+
| LEFT('lifetime',4) |
+--------------------+
| life               |
+--------------------+
1 row in set (0.01 sec)

  • RIGHT(str,len)返回str字符串中最右侧的len个字符，如果参数为NULL则返回NULL。

    mysql> SELECT RIGHT('lifetime',4);
+---------------------+
| RIGHT('lifetime',4) |
+---------------------+
| time                |
+---------------------+
1 row in set (0.00 sec)

  • LENGTH(str)返回str字符串的长度，以字节计算，一个多字节字符会按照多个字节计算长度。而当使用CHAR_LENGTH()时，会将一个多字节字符以一个字节计算长度，即长度为1。

    mysql> SELECT LENGTH('lifetime');
+--------------------+
| LENGTH('lifetime') |
+--------------------+
|                  8 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH('生活');
+------------------+
| LENGTH('生活')   |
+------------------+
|                6 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT CHAR_LENGTH('生活');
+-----------------------+
| CHAR_LENGTH('生活')   |
+-----------------------+
|                     2 |
+-----------------------+
1 row in set (0.00 sec)

  • LOWER(str)根据目前的字符集将str中的所有字符转换为小写并返回。

    mysql> SELECT LOWER('HELLO');
+----------------+
| LOWER('HELLO') |
+----------------+
| hello          |
+----------------+
1 row in set (0.00 sec)

  • UPPER(str)根据目前的字符集将str中的所有字符转换为大写并返回。

    mysql> SELECT UPPER('hello');
+----------------+
| UPPER('hello') |
+----------------+
| HELLO          |
+----------------+
1 row in set (0.00 sec)

    注意：LOWER()和UPPER()对二进制字符串无效（BINARY,VARBINARY,BLOB）。转换大小写时需要将其转换为非二进制字符串。

  • LTRIM(str)将str开头处的空格移除，返回移除空格后的字符串。该函数可以用于包含多字节字符的字符串。

    mysql> SELECT LTRIM('    shop');
+-------------------+
| LTRIM('    shop') |
+-------------------+
| shop              |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT LTRIM('    商店');
+---------------------+
| LTRIM('    商店')   |
+---------------------+
| 商店                |
+---------------------+
1 row in set (0.00 sec)

  • RTRIM(str)将`str尾部的空格移除，返回移除空格后的字符串。该函数可以用于包含多字节字符的字符串。

    mysql> SELECT LTRIM('shop    ');
+-------------------+
| LTRIM('shop    ') |
+-------------------+
| shop              |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT LTRIM('商店    ');
+---------------------+
| LTRIM('商店    ')   |
+---------------------+
| 商店                |
+---------------------+
1 row in set (0.00 sec)

  • TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str),TRIM([remstr FROM] str)

    将str中所有的remstr前缀/后缀移除。如果没有使用BOTH,LEADING,TRAILING这些区分符，会假定使用BOTH。

    remstr是可选的，如果没有指定，则会移除空格。

    mysql> SELECT TRIM('   test    ');
+---------------------+
| TRIM('   test    ') |
+---------------------+
| test                |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT TRIM(LEADING '-' FROM '---author---');
+---------------------------------------+
| TRIM(LEADING '-' FROM '---author---') |
+---------------------------------------+
| author---                             |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TRIM(TRAILING '-' FROM '---author---');
+----------------------------------------+
| TRIM(TRAILING '-' FROM '---author---') |
+----------------------------------------+
| ---author                              |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TRIM(BOTH '-' FROM '---author---');
+------------------------------------+
| TRIM(BOTH '-' FROM '---author---') |
+------------------------------------+
| author                             |
+------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TRIM('-' FROM '---author---');
+-------------------------------+
| TRIM('-' FROM '---author---') |
+-------------------------------+
| author                        |
+-------------------------------+
1 row in set (0.00 sec)

  • SUBSTRING(str, pos),SUBSTRING(str FROM pos),SUBSTRING(str,pos,len),SUBSTRING(str FROM pos FOR len)返回截取后的字符串。

    1. 参数列表中没有len参数时，会返回一个从pos位置开始的字符串。
    2. 参数列表中有len参数时，会返回一个从pos位置开始，长度为len的字符串。
    3. 使用FROM的是标准写法。
    4. 当pos值为负数时，表示该位置是从字符串尾部开始计算的。
    5. 当len小于1时，返回一个空的字符串
    mysql> SELECT SUBSTRING('www.google.com', 5);
+--------------------------------+
| SUBSTRING('www.google.com', 5) |
+--------------------------------+
| google.com                     |
+--------------------------------+
1 row in set (0.01 sec)

mysql> SELECT SUBSTRING('www.google.com' FROM 5);
+------------------------------------+
| SUBSTRING('www.google.com' FROM 5) |
+------------------------------------+
| google.com                         |
+------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('www.google.com',5,6);
+---------------------------------+
| SUBSTRING('www.google.com',5,6) |
+---------------------------------+
| google                          |
+---------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('www.google.com'FROM 5 FOR 6);
+-----------------------------------------+
| SUBSTRING('www.google.com'FROM 5 FOR 6) |
+-----------------------------------------+
| google                                  |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('www.google.com',-3,3);
+----------------------------------+
| SUBSTRING('www.google.com',-3,3) |
+----------------------------------+
| com                              |
+----------------------------------+
1 row in set (0.00 sec)
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 194,911评论 5赞 460
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 82,014评论 2赞 371
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 142,129评论 0赞 320
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 52,283评论 1赞 264
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 61,159评论 4赞 357
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 46,161评论 1赞 272
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 36,565评论 3赞 382
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 35,251评论 0赞 253
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 39,531评论 1赞 292
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 34,619评论 2赞 310
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 36,383评论 1赞 326
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 32,255评论 3赞 313
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 37,624评论 3赞 299
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 28,916评论 0赞 17
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,199评论 1赞 250
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 41,553评论 2赞 342
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 40,756评论 2赞 335

推荐阅读更多精彩内容

  • 周君，我怎么如此幸运，第一天
    2017.11.02 我是如此幸运：️ 1、只有对魏先生大声嚷，每次都觉得理直气壮[害羞]今天电话里一开口就后悔了...
    老姐嘛嘛阅读 167评论 0赞 0
  • 读《钢铁是怎样炼成的》微写作
    摘抄：镇上还蒙着一层拂晓的灰色的晨雾。破落的犹太人住区的街道，一片荒凉，像一条条湿透的帆布，死沉沉的没有半个人影。...
    一路繁花W阅读 355评论 0赞 0
  • 晚年
    人啊，你心心念念的自由啊！ 你已跨越一个被囚者的禁锢顶峰并到达那触手可及的自由边缘。 缘何把这颤粟的魂灵藏在那无人...
    李牧風阅读 387评论 1赞 4
  • React Native 搭建开发环境详解
    准备工作 node -v：确认是否安装Node，若已经成功安装了，则执行下面的命令；否则先进行Node的安装。 n...
    壮壮成长记阅读 300评论 0赞 0