数据处理函数
1.文本处理函数
比如UPPER函数是将小写变成大写。
mysql> SELECT request_uri,UPPER(request_uri) from sys_log; +---------------------------+---------------------------+ | request_uri | UPPER(request_uri) | +---------------------------+---------------------------+ | /a/cms/article/ | /A/CMS/ARTICLE/ | | /a/cms/article/draftsSave | /A/CMS/ARTICLE/DRAFTSSAVE | | /a/cms/article/save | /A/CMS/ARTICLE/SAVE | | /a/cms/guestbook/ | /A/CMS/GUESTBOOK/ | | /a/cms/guestbook/save | /A/CMS/GUESTBOOK/SAVE | | /a/cms/tree | /A/CMS/TREE | | /a/gen/genTable/ | /A/GEN/GENTABLE/ | | /a/oa/leave/list | /A/OA/LEAVE/LIST | | /a/sys/user/info | /A/SYS/USER/INFO | | /a/sys/user/info | /A/SYS/USER/INFO | | /hyd/a/sys/office/list | /HYD/A/SYS/OFFICE/LIST | | /hyd/a/sys/user/info | /HYD/A/SYS/USER/INFO | +---------------------------+---------------------------+ 12 rows in set
类似的文本处理函数有：
| 名称 | 调用示例 | 示例结果 | 描述 |
|---|---|---|---|
| LEFT | LEFT(‘abc123’, 3) | abc | 返回从左边取指定长度的子串 |
| RIGHT | RIGHT(‘abc123’, 3) | 123 | 返回从右边取指定长度的子串 |
| LENGTH | LENGTH(‘abc’) | 3 | 返回字符串的长度 |
| LOWER | LOWER(‘ABC’) | abc | 返回小写格式字符串 |
| UPPER | UPPER(‘abc’) | ABC | 返回大写格式字符串 |
| LTRIM | LTRIM(’ abc’) | abc | 将字符串左边空格去除后返回 |
| RTRIM | RTRIM(‘abc ‘) | abc | 将字符串右边空格去除后返回 |
| SUBSTRING | SUBSTRING(‘abc123’, 2, 3) | bc1 | 从字符串第2位开始截取3位字符 |
| CONCAT | CONCAT(‘abc’, ‘123’, ‘xyz’) | abc123xyz | 将各个字符串参数拼接成一个新的字符串 |
比如：
mysql> SELECT LEFT('abc123',3); +------------------+ | LEFT('abc123',3) | +------------------+ | abc | +------------------+ 1 row in set mysql> select RIGHT('abc123', 3); +--------------------+ | RIGHT('abc123', 3) | +--------------------+ | 123 | +--------------------+ 1 row in set mysql> select length('abc'); +---------------+ | length('abc') | +---------------+ | 3 | +---------------+ 1 row in set mysql> select ltrim(' abc'); +---------------+ | ltrim(' abc') | +---------------+ | abc | +---------------+ 1 row in set mysql> select substring('1234567',2,3) ; +--------------------------+ | substring('1234567',2,3) | +--------------------------+ | 234 | +--------------------------+ 1 row in set mysql> select concat('a','b','c','d'); +-------------------------+ | concat('a','b','c','d') | +-------------------------+ | abcd | +-------------------------+ 1 row in set
2.日期和时间处理函数
常见的时间计算类型:
| SECOND | 秒 | SECONDS |
|---|---|---|
| MINUTE | 分钟 | MINUTES |
| HOUR | 时间 | HOURS |
| DAY | 天 | DAYS |
| MONTH | 月 | MONTHS |
| YEAR | 年 | YEARS |
| MINUTE_SECOND | 分钟和秒 | “MINUTES:SECONDS” |
| HOUR_MINUTE | 小时和分钟 | “HOURS:MINUTES” |
| DAY_HOUR | 天和小时 | “DAYS HOURS” |
| YEAR_MONTH | 年和月 | “YEARS-MONTHS” |
| HOUR_SECOND | 小时, 分钟， | “HOURS:MINUTES:SECONDS” |
| DAY_MINUTE | 天, 小时, 分钟 | “DAYS HOURS:MINUTES” |
| DAY_SECOND | 天, 小时, 分钟, 秒 | “DAYS HOURS:MINUTES:SECONDS” |
常见的日期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 位 |
常见的日期函数如下：
| 名称 | 调用示例 | 结果 | 描述 |
|---|---|---|---|
| now() | now() | 2018-09-27 14:22:48 | 获得当前日期+时间（date + time） |
| sysdate() | sysdate() | 获得当前日期+时间（date + time）函数 | |
| current_timestamp | 示例见下 | 获得当前时间戳函数 | |
| current_timestamp() | 示例见下 | 获得当前时间戳函数 | |
| date_format(date,format) | 示例见下 | 日期/时间转换为字符串 | |
| time_format(time,format) | 示例见下 | 日期/时间转换为字符串 | |
| str_to_date(str, format) | 示例见下 | 字符串转换为日期 | |
| to_days(date) | 示例见下 | （日期、天数）转换函数 | |
| from_days(days) | 示例见下 | （日期、天数）转换函数 | |
| time_to_sec(time) | 示例见下 | （时间、秒）转换函数 | |
| sec_to_time(seconds) | 示例见下 | （时间、秒）转换函数 | |
| makedate(year,dayofyear) | 示例见下 | 拼凑日期 | |
| maketime(hour,minute,second) | 示例见下 | 拼凑日期 | |
| unix_timestamp() | 示例见下 | MySQL （Unix 时间戳、日期）转换函数 | |
| unix_timestamp(date) | 示例见下 | MySQL （Unix 时间戳、日期）转换函数 | |
| from_unixtime(unix_timestamp) | 示例见下 | MySQL （Unix 时间戳、日期）转换函数 | |
| from_unixtime(unix_timestamp,format) | 示例见下 | MySQL （Unix 时间戳、日期）转换函数 | |
| date_add() | 示例见下 | 为日期增加一个时间间隔 | |
| adddate() | 示例见下 | 可以用 date_add() 来替代 | |
| addtime() | 示例见下 | 可以用 date_add() 来替代 | |
| date_sub() | 示例见下 | 为日期减去一个时间间隔 | |
| datediff(date1,date2) | 示例见下 | 日期、时间相减函数 | |
| timediff(time1,time2) | 示例见下 | 日期、时间相减函数 | |
| timestamp(date) | 示例见下 | date to timestamp | |
| timestamp(dt,time) | 示例见下 | – dt + time | |
| timestampadd(unit,interval,datetime_expr) | 示例见下 | 时间戳加法 | |
| timestampdiff(unit,datetime_expr1,datetime_expr2) | 示例见下 | 时间戳减法 | |
| convert_tz(dt,from_tz,to_tz) | 示例见下 | MySQL 时区（timezone）转换函数 | |
mysql> select now(); +---------------------+ | now() | +---------------------+ | 2018-09-27 14:22:48 | +---------------------+ 1 row in set
获得当前日期+时间（date + time）函数：sysdate()
sysdate() 日期时间函数跟 now() 类似，不同之处在于：now() 在执行开始时值就得到了， sysdate() 在函数执行时动态得到值。sysdate() 日期时间函数，一般情况下很少用到。看下面的例子就明白了：
mysql> select now(), sleep(3), now(); +---------------------+----------+---------------------+ | now() | sleep(3) | now() | +---------------------+----------+---------------------+ | 2018-09-27 14:26:19 | 0 | 2018-09-27 14:26:19 | +---------------------+----------+---------------------+ 1 row in set mysql> select sysdate(), sleep(3), sysdate(); +---------------------+----------+---------------------+ | sysdate() | sleep(3) | sysdate() | +---------------------+----------+---------------------+ | 2018-09-27 14:26:37 | 0 | 2018-09-27 14:26:40 | +---------------------+----------+---------------------+ 1 row in setmysql> select current_timestamp, current_timestamp(); +---------------------+---------------------+ | current_timestamp | current_timestamp() | +---------------------+---------------------+ | 2018-09-27 14:42:44 | 2018-09-27 14:42:44 | +---------------------+---------------------+ 1 row in setmysql> select date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s'); +----------------------------------------------------+ | date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s') | +----------------------------------------------------+ | 20080808222301 | +----------------------------------------------------+ 1 row in set mysql> select time_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s'); +----------------------------------------------------+ | time_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s') | +----------------------------------------------------+ | 00000000222301 | +----------------------------------------------------+ 1 row in setmysql> select str_to_date('08/09/2008', '%m/%d/%Y'); +---------------------------------------+ | str_to_date('08/09/2008', '%m/%d/%Y') | +---------------------------------------+ | 2008-08-09 | +---------------------------------------+ 1 row in set mysql> select str_to_date('08/09/08' , '%m/%d/%y'); +--------------------------------------+ | str_to_date('08/09/08' , '%m/%d/%y') | +--------------------------------------+ | 2008-08-09 | +--------------------------------------+ 1 row in set mysql> select str_to_date('08.09.2008', '%m.%d.%Y'); +---------------------------------------+ | str_to_date('08.09.2008', '%m.%d.%Y') | +---------------------------------------+ | 2008-08-09 | +---------------------------------------+ 1 row in set mysql> select str_to_date('08:09:30', '%h:%i:%s'); +-------------------------------------+ | str_to_date('08:09:30', '%h:%i:%s') | +-------------------------------------+ | 08:09:30 | +-------------------------------------+ 1 row in set mysql> select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); +---------------------------------------------------------+ | str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s') | +---------------------------------------------------------+ | 2008-08-09 08:09:30 | +---------------------------------------------------------+ 1 row in setmysql> select from_days(733627); +-------------------+ | from_days(733627) | +-------------------+ | 2008-08-08 | +-------------------+ 1 row in set mysql> select to_days('2008-08-08'); +-----------------------+ | to_days('2008-08-08') | +-----------------------+ | 733627 | +-----------------------+ 1 row in setmysql> select time_to_sec('01:00:00'); +-------------------------+ | time_to_sec('01:00:00') | +-------------------------+ | 3600 | +-------------------------+ 1 row in set mysql> select sec_to_time(3600); +-------------------+ | sec_to_time(3600) | +-------------------+ | 01:00:00 | +-------------------+ 1 row in setmysql> select makedate(2001,31); +-------------------+ | makedate(2001,31) | +-------------------+ | 2001-01-31 | +-------------------+ 1 row in set mysql> select makedate(2001,32); +-------------------+ | makedate(2001,32) | +-------------------+ | 2001-02-01 | +-------------------+ 1 row in set mysql> select maketime(12,15,30); +--------------------+ | maketime(12,15,30) | +--------------------+ | 12:15:30 | +--------------------+ 1 row in setmysql> select unix_timestamp(); +------------------+ | unix_timestamp() | +------------------+ | 1538032704 | +------------------+ 1 row in set mysql> select unix_timestamp('2008-08-08'); +------------------------------+ | unix_timestamp('2008-08-08') | +------------------------------+ | 1218124800 | +------------------------------+ 1 row in set mysql> select unix_timestamp('2008-08-08 12:30:00'); +---------------------------------------+ | unix_timestamp('2008-08-08 12:30:00') | +---------------------------------------+ | 1218169800 | +---------------------------------------+ 1 row in set mysql> select from_unixtime(1218290027); +---------------------------+ | from_unixtime(1218290027) | +---------------------------+ | 2008-08-09 21:53:47 | +---------------------------+ 1 row in set mysql> select from_unixtime(1218124800); +---------------------------+ | from_unixtime(1218124800) | +---------------------------+ | 2008-08-08 00:00:00 | +---------------------------+ 1 row in set mysql> select from_unixtime(1218169800); +---------------------------+ | from_unixtime(1218169800) | +---------------------------+ | 2008-08-08 12:30:00 | +---------------------------+ 1 row in set mysql> select from_unixtime(1218169800, '%Y %D %M %h:%i:%s %x'); +---------------------------------------------------+ | from_unixtime(1218169800, '%Y %D %M %h:%i:%s %x') | +---------------------------------------------------+ | 2008 8th August 12:30:00 2008 | +---------------------------------------------------+ 1 row in setmysql> select @dt; +---------------------+ | @dt | +---------------------+ | 2018-09-27 15:28:15 | +---------------------+ 1 row in set mysql> select date_add(@dt, interval 1 day); +-------------------------------+ | date_add(@dt, interval 1 day) | +-------------------------------+ | 2018-09-28 15:28:15 | +-------------------------------+ 1 row in set mysql> select date_add(@dt, interval 1 hour); +--------------------------------+ | date_add(@dt, interval 1 hour) | +--------------------------------+ | 2018-09-27 16:28:15 | +--------------------------------+ 1 row in set mysql> select date_add(@dt, interval 1 minute); +----------------------------------+ | date_add(@dt, interval 1 minute) | +----------------------------------+ | 2018-09-27 15:29:15 | +----------------------------------+ 1 row in set mysql> select date_add(@dt, interval 1 second); +----------------------------------+ | date_add(@dt, interval 1 second) | +----------------------------------+ | 2018-09-27 15:28:16 | +----------------------------------+ 1 row in set mysql> select date_add(@dt, interval 1 microsecond); +---------------------------------------+ | date_add(@dt, interval 1 microsecond) | +---------------------------------------+ | 2018-09-27 15:28:15.000001 | +---------------------------------------+ 1 row in set mysql> select date_add(@dt, interval 1 week); +--------------------------------+ | date_add(@dt, interval 1 week) | +--------------------------------+ | 2018-10-04 15:28:15 | +--------------------------------+ 1 row in set mysql> select date_add(@dt, interval 1 month); +---------------------------------+ | date_add(@dt, interval 1 month) | +---------------------------------+ | 2018-10-27 15:28:15 | +---------------------------------+ 1 row in set mysql> select date_add(@dt, interval 1 quarter); +-----------------------------------+ | date_add(@dt, interval 1 quarter) | +-----------------------------------+ | 2018-12-27 15:28:15 | +-----------------------------------+ 1 row in set mysql> select date_add(@dt, interval 1 year); +--------------------------------+ | date_add(@dt, interval 1 year) | +--------------------------------+ | 2019-09-27 15:28:15 | +--------------------------------+ 1 row in set mysql> select date_add(@dt, interval -1 day); +--------------------------------+ | date_add(@dt, interval -1 day) | +--------------------------------+ | 2018-09-26 15:28:15 | +--------------------------------+ 1 row in setmysql> set @dt = '2008-08-09 12:12:33'; Query OK, 0 rows affected mysql> select date_add(@dt, interval '01:15:30' hour_second); +------------------------------------------------+ | date_add(@dt, interval '01:15:30' hour_second) | +------------------------------------------------+ | 2008-08-09 13:28:03 | +------------------------------------------------+ 1 row in set mysql> select date_add(@dt, interval '1 01:15:30' day_second); +-------------------------------------------------+ | date_add(@dt, interval '1 01:15:30' day_second) | +-------------------------------------------------+ | 2008-08-10 13:28:03 | +-------------------------------------------------+ 1 row in setmysql> select date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second); +----------------------------------------------------------------+ | date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second) | +----------------------------------------------------------------+ | 1997-12-30 22:58:59 | +----------------------------------------------------------------+ 1 row in setmysql> select datediff('2008-08-08', '2008-08-01'); +--------------------------------------+ | datediff('2008-08-08', '2008-08-01') | +--------------------------------------+ | 7 | +--------------------------------------+ 1 row in set mysql> select datediff('2008-08-01', '2008-08-08'); +--------------------------------------+ | datediff('2008-08-01', '2008-08-08') | +--------------------------------------+ | -7 | +--------------------------------------+ 1 row in setmysql> select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00'); +--------------------------------------------------------+ | timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00') | +--------------------------------------------------------+ | 08:08:08 | +--------------------------------------------------------+ 1 row in set -- 注意：timediff(time1,time2) 函数的两个参数类型必须相同。 mysql> select timediff('08:08:08', '00:00:00'); +----------------------------------+ | timediff('08:08:08', '00:00:00') | +----------------------------------+ | 08:08:08 | +----------------------------------+ 1 row in setmysql> select timestamp('2008-08-08'); +-------------------------+ | timestamp('2008-08-08') | +-------------------------+ | 2008-08-08 00:00:00 | +-------------------------+ 1 row in set mysql> select timestamp('2008-08-08 08:00:00', '01:01:01'); +----------------------------------------------+ | timestamp('2008-08-08 08:00:00', '01:01:01') | +----------------------------------------------+ | 2008-08-08 09:01:01 | +----------------------------------------------+ 1 row in set mysql> select timestamp('2008-08-08 08:00:00', '10 01:01:01'); +-------------------------------------------------+ | timestamp('2008-08-08 08:00:00', '10 01:01:01') | +-------------------------------------------------+ | 2008-08-18 09:01:01 | +-------------------------------------------------+ 1 row in set mysql> select timestampadd(day, 1, '2008-08-08 08:00:00'); +---------------------------------------------+ | timestampadd(day, 1, '2008-08-08 08:00:00') | +---------------------------------------------+ | 2008-08-09 08:00:00 | +---------------------------------------------+ 1 row in set mysql> select date_add('2008-08-08 08:00:00', interval 1 day); +-------------------------------------------------+ | date_add('2008-08-08 08:00:00', interval 1 day) | +-------------------------------------------------+ | 2008-08-09 08:00:00 | +-------------------------------------------------+ 1 row in set mysql> select timestampdiff(year,'2002-05-01','2001-01-01'); +-----------------------------------------------+ | timestampdiff(year,'2002-05-01','2001-01-01') | +-----------------------------------------------+ | -1 | +-----------------------------------------------+ 1 row in set mysql> select timestampdiff(day ,'2002-05-01','2001-01-01'); +-----------------------------------------------+ | timestampdiff(day ,'2002-05-01','2001-01-01') | +-----------------------------------------------+ | -485 | +-----------------------------------------------+ 1 row in set -- MySQL timestampadd() 函数类似于 date_add() mysql> select timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00'); +-----------------------------------------------------------------+ | timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00') | +-----------------------------------------------------------------+ | -12 | +-----------------------------------------------------------------+ 1 row in set mysql> select datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00'); +--------------------------------------------------------+ | datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00') | +--------------------------------------------------------+ | 7 | +--------------------------------------------------------+ 1 row in setmysql> select convert_tz('2008-08-08 12:00:00', '+08:00', '+00:00'); +-------------------------------------------------------+ | convert_tz('2008-08-08 12:00:00', '+08:00', '+00:00') | +-------------------------------------------------------+ | 2008-08-08 04:00:00 | +-------------------------------------------------------+ 1 row in set-- 时区转换也可以通过 date_add, date_sub, timestampadd 来实现 mysql> select date_add('2008-08-08 12:00:00', interval -8 hour); +---------------------------------------------------+ | date_add('2008-08-08 12:00:00', interval -8 hour) | +---------------------------------------------------+ | 2008-08-08 04:00:00 | +---------------------------------------------------+ 1 row in set mysql> select date_sub('2008-08-08 12:00:00', interval 8 hour); +--------------------------------------------------+ | date_sub('2008-08-08 12:00:00', interval 8 hour) | +--------------------------------------------------+ | 2008-08-08 04:00:00 | +--------------------------------------------------+ 1 row in set mysql> select timestampadd(hour, -8, '2008-08-08 12:00:00'); +-----------------------------------------------+ | timestampadd(hour, -8, '2008-08-08 12:00:00') | +-----------------------------------------------+ | 2008-08-08 04:00:00 | +-----------------------------------------------+ 1 row in set
3.数值处理函数
| Abs() | 返回一个数的绝对值 |
|---|---|
| Cos() | 返回一个角度的余弦 |
| Sin() | 返回一个角度的正弦 |
| Tan() | 返回一个角度的正切 |
| Exp() | 返回一个数的指数值 |
| Mod() | 返回除操作的余数 |
| Pi() | 返回圆周率 |
| Rand() | 返回一个随机数 |
| Sqrt() | 返回一个数的平方根 |
川长思鸟来
朱雀
还没有评论，来说两句吧...