专栏名称: 金正皓

教导主任

MySQ基础入门系列之——字符与日期数据处理

金正皓 · 掘金 · · 2017-12-11 08:53

正文

请到「今天看啥」查看全文

今天这一篇分享MySQL中的字符串处理工具与日期时间处理，这一部分内容虽然看似不多，但是往往是输出处理中的的痛点。

我的MySQL数据库中已经建好了一个包含两列日期与一列薪资范围字段。（深感通过R或者Python建表然后写入MySQL的效率要比使用MySQL自己的编辑器写方便！）

MySQL中的日期与时间操作涉及到的函数相当多，实际应用需要按照需要和场景来选择该使用哪一类函数，这一篇我只挑选了三类重要的日期与时间函数进行总结。

1、日期格式化：

因为本地文本文件中存储的日期导入数据库，很多情况下是当做文本的，当需要使用日期参与计算的时候需要使用日期格式化函数进行格式转化。

MySQL默认支持的日期格式是YYYY-MM-DD或者YYYY/MM/DD，所以当导进去的日期是此种格式的话，直接使用date函数即可执行。

date()

select date('2017-05-01') as date_d; 
2017-05-01
select date('2017/05/01') as date_d;
2017-05-01

str_to_date()

当日期/时间格式比较复杂或者与常用的日期结构差异较大时，可以使用str_to_date函数进行原始日期格式的声明

select str_to_date('12/08/2017','%m/%d/%Y')
2017-12-08
select str_to_date('20170422154706','%Y%m%d%H%i%s')
2017-04-22 15:47:06
select str_to_date('2017-04-22 15:47:06','%Y-%m-%d %H:%i:%s')
2017-04-22 15:47:06

函数中第二个参数的声明方式要严格与输入的日期格式对应。

DATE_FORMAT()

如果你已经得到了一个标准日期格式的字段，想要自定义日期显示的格式，可以使用DATE_FORMAT函数，

SELECT DATE_FORMAT(date('2017-04-22 15:47:06'),'%m-%Y-%d')
04-2017-22
SELECT DATE_FORMAT('2017-04-22 15:47:06','%m-%Y')
04-2017
SELECT DATE_FORMAT(date('2017-04-22 15:47:06'),'%M')
April

FROM_UNIXTIME()

如果你拿到的原始数据是10位数值的时间戳，需要使用这个函数来进行标准时间的转化。

SELECT FROM_UNIXTIME(1504567890, '%Y-%m-%d %H:%i:%S')
2017-09-05 07:31:30

2、日期计算

date_add()  -- 加
date_sub()  -- 减
date_diff() -- 日期差值
select start_date,date_add(start_date,INTERVAL 10 DAY) as add_date from db1.str_date;
select start_date,date_sub(start_date,INTERVAL 10 DAY) as sub_date from db1.str_date;
SELECT start_date,end_date,DATEDIFF(end_date,start_date) AS DiffDate from db1.str_date;

3、日期元素的输出：

关于日期其中部分元素的输出，除了使用上面那个DATE_FORMAT()函数之外，可以直接使用单个元素函数进行提取。

year
month
day

select start_date,year(start_date) as date_year from db1.str_date;
select start_date,month(start_date) as date_month from db1.str_date;
select start_date,day(start_date) as date_year from db1.str_date;

quarter

weekday

select start_date,quarter(start_date) as date_quarter from db1.str_date;
select start_date,weekday(start_date) as date_weekday from db1.str_date;

hour

minute

second

select '2017-04-22 15:47:06',hour('2017-04-22 15:47:06') as date_hour;
2017-04-22 15:47:06    15
select '2017-04-22 15:47:06',minute('2017-04-22 15:47:06') as date_minute;
2017-04-22 15:47:06    47
select '2017-04-22 15:47:06',second('2017-04-22 15:47:06') as date_second;
2017-04-22 15:47:06    6

第二部分：

字符串格式化函数：

LENGTH()-- 计算字符长度
RTRIM() -- 去空格
LTRIM() -- 去左空格
TRIM()  -- 去两边的空格
UPPER() -- 字母大写
LOWER() -- 字符小写
SELECT LENGTH('SPACE   ') as len1,LENGTH(RTRIM('SPACE   ')) as len2;
len1   len2
8    5
SELECT LENGTH('   SPACE') as len1,LENGTH(LTRIM('SPACE   ')) as len2;
len1   len2
8    5
SELECT LENGTH('   SPACE   ') as len1,LENGTH(TRIM('   SPACE   ')) as len2;
len1   len2
11    5
SELECT LOWER('LOWER') as lows;
lower
SELECT UPPER('upper') as ups;
UPPER

字符串操纵函数：

concate()                 -- 合并与拼接字符串
CONCAT_WS()               -- 按照某种固定分隔符分割字符串
substring_index()         -- 按照固定模式分割字符串
SELECT CONCAT('2017','12','10') as date_now;
20171210
SELECT CONCAT_WS('-','2017','12','10') as date_now;
2017-12-10
SELECT CONCAT_WS('/','2017','12','10') as date_now;
2017/12/10

可以看到CONCAT_WS允许自定义拼接的间隔符，所以实用性更强一些。

substring_index函数运行我们按照特定字符出现的位置进行截取，很多时候可以起到关键作用。

使用substring_index函数处理日期元素输出：

SELECT start_date,substring_index(start_date,'-',1) as date_year from db1.str_date; -- 截取年份
SELECT start_date,substring_index(substring_index(start_date,'-',1),'-',1) as date_month from db1.str_date; -- 截取月份
SELECT start_date,substring_index(start_date,'-',-1) as date_year from db1.str_date; -- 截取天

使用substring_index函数处理带有字母的薪资区间字段：

SELECT slary,substring_index(substring_index(slary,'-',1),'k',1) as slary_low from db1.str_date; -- 截取薪资下限
SELECT slary,substring_index(substring_index(slary,'-',-1),'k',1) as slary_low from db1.str_date; -- 截取薪资上限

以下还剩余两类字符串处理函数，分别是字符串定位函数，字符串截取函数

locate()                             -- 定位特定字符位置
left()/right()/mid(Start,Length)  -- 从特定位置截取字符串
substring(string,startlength    -- 特定位置截取固定长度字符串
replace()                          -- 字符串替换函数

locate函数可以给出字符串中某个特定字符串相对于整个字符串长度的精确位置。
left()/right()/mid()函数则可以分别从左侧、右侧或者中间某个特定位置截取固定长度的字符内容。
substring()函数则是根据某个特定字符串位置开始截取固定长度字符内容。

使用locate函数+left()/right()/mid()函数处理薪资上下限问题。

SELECT
    slary,
    LEFT (slary, LOCATE('k', slary) - 1) AS salary_low,
    replace(
        RIGHT (
            slary,
            LENGTH(slary) - LOCATE('-', slary)
        ),'k',''
    ) AS salary_upFROM
    db1.str_date;

使用substring函数处理薪资上下限问题：

SELECT
    slary,
    SUBSTRING(
        slary,1,locate('k', slary) - 1
    ) AS salary_low,
     SUBSTRING(
        slary,locate('-', slary) + 1,
        length(slary) - locate('-', slary) - 1
    ) AS salary_upFROM
    db1.str_date;

以上便是本篇关于MySQL日期/时间与字符串处理的相关学习笔记与心得总结，分享给大家，希望能够一起学习、一起进步！

在线课程请点击文末原文链接：

Hellobi Live | R语言可视化在商务场景中的应用

往期案例数据请移步本人GitHub： github.com/ljtyduyu/Da…