工作中常用SQL片段整理（一）

获取数据集起止时间

select DATE_FORMAT(t.acquisition_time,’%Y%m%d’) from ((SELECT
acquisition_time
FROM stress_datainfo
ORDER BY
acquisition_time
ASC limit 1)
UNION
(SELECT
acquisition_time
FROM stress_datainfo
ORDER BY
acquisition_time
DESC limit 1))t

获取补全的起止日期

select concat(‘stress_datainfo_’,DATE_FORMAT(k.time,’%Y%m’)) tb_name from
(
SELECT @date := DATE_ADD(@date, interval 1 month) as time from
(SELECT @date := DATE_ADD((SELECT
acquisition_time as date
FROM stress_datainfo
ORDER BY acquisition_time
ASC limit 1), interval -1 month) from config)days
)k
where k.time
BETWEEN
(SELECT
acquisition_time
FROM stress_datainfo
ORDER BY
acquisition_time
ASC limit 1)
AND
(SELECT
DATE_ADD(acquisition_time, interval 1 month)
FROM stress_datainfo
ORDER BY
acquisition_time
DESC limit 1)

ON DUPLICATE KEY UPDATE 不存在新增，存在更新值

INSERT INTO config (type, name, sequence, int_value, str_value) VALUES (‘partition_table’, ‘stress_datainfo’, 0, 0, ‘2020-08-01 04:41:08’)
ON DUPLICATE KEY UPDATE type = ‘partition_table’,name = ‘press_history’,str_value=‘xxxxx’;