MYSQL事件使用 日期函式

风中追风h發表於2024-09-18
MySql事件函式

目錄
1.日期格式轉換函式
DATE_FORMAT()
STR_TO_DATE()
CURDATE() 與 NOW()
DATE_ADD() 與 DATE_SUB()
DATEDIFF() 與 TIMESTAMPDIFF()
2.字串函式
CONCAT()
SUBSTRING()
REPLACE()
LENGTH() 與 CHAR_LENGTH()
UPPER() 與 LOWER()
3.聚合函式
COUNT()
SUM()
AVG()
MAX() 與 MIN()
GROUP_CONCAT()常見場景與示例總結

1. 日期格式轉換函式

MySQL 提供了多種日期相關的函式，可以方便地對日期進行格式化、計算時間差以及加減時間。以下是常用的日期格式轉換函式。

1.1 DATE_FORMAT()

DATE_FORMAT() 函式用於將日期格式化為指定的字串格式。常見的日期格式化規則包括：

%Y: 4位的年份（例如：2024）
%m: 2位的月份（01-12）
%d: 2位的日期（01-31）
%H: 2位的小時（00-23）
%i: 2位的分鐘（00-59）
%s: 2位的秒（00-59）
示例：

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS formatted_date;

結果：

formatted_date
-------------------
2024-09-13 14:35:21

1.2 STR_TO_DATE()

STR_TO_DATE() 將字串解析為日期格式，通常用於從字串中提取日期。

示例：

SELECT STR_TO_DATE('2024-09-13 14:35:21', '%Y-%m-%d %H:%i:%s') AS date_value;

結果：

date_value
-------------------
2024-09-13 14:35:21

1.3 CURDATE()、CURTIME() 與 NOW()

CURDATE() 返回當前日期（不包含時間部分）。

CURTIME()返回當前時間
NOW() 返回當前的日期和時間。


SELECT CURDATE() AS current_date, NOW() AS current_datetime;

結果：

current_date | current_datetime
----------------|-------------------
2024-09-13 | 2024-09-13 14:35:21

1.4 DATE_ADD() 與 DATE_SUB()

函式形式：DATE_ADD(date,INTERVAL expr unit) —— DATE_SUB(date,INTERVAL expr unit)

date_sub引數參考date_add即可。

expr：表示時間間隔值

unit：單位，如下

MICROSECOND 間隔單位：毫秒
SECOND 間隔單位：秒
MINUTE 間隔單位：分鐘
HOUR 間隔單位：小時
DAY 間隔單位：天
WEEK 間隔單位：星期
MONTH 間隔單位：月
QUARTER 間隔單位：季度
YEAR 間隔單位：年
SECOND_MICROSECOND 複合型，間隔單位：秒、毫秒，expr可以用兩個值來分別指定秒和毫秒
MINUTE_MICROSECOND 複合型，間隔單位：分、毫秒
MINUTE_SECOND 複合型，間隔單位：分、秒
HOUR_MICROSECOND 複合型，間隔單位：小時、毫秒
HOUR_SECOND 複合型，間隔單位：小時、秒
HOUR_MINUTE 複合型，間隔單位：小時分
DAY_MICROSECOND 複合型，間隔單位：天、毫秒
DAY_SECOND 複合型，間隔單位：天、秒
DAY_MINUTE 複合型，間隔單位：天、分
DAY_HOUR 複合型，間隔單位：天、小時
YEAR_MONTH 複合型，間隔單位：年、月


DATE_ADD() 用於在日期上增加指定的時間間隔。
DATE_SUB() 用於在日期上減去指定的時間間隔。
SELECT DATE_ADD(CURDATE(), INTERVAL 7 DAY) AS next_week;
SELECT DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AS last_month;

結果：

next_week | last_month
----------------|------------
2024-09-20 | 2024-08-13


1.5 DATEDIFF() 與 TIMESTAMPDIFF()

DATEDIFF() 計算兩個日期之間的天數差。
TIMESTAMPDIFF() 可計算任意單位的時間差（秒、分鐘、小時、天等）。
SELECT DATEDIFF('2024-09-20', '2024-09-13') AS days_diff;
SELECT TIMESTAMPDIFF(HOUR, '2024-09-13 08:00:00', NOW()) AS hours_diff;

結果：

days_diff | hours_diff
------------|------------
7 | 6

1.6 轉換日期

2. 字串函式

字串操作是資料庫查詢中常見的需求。MySQL 提供了豐富的字串處理函式，用於連線、替換、擷取和轉換字串。

2.1 CONCAT()

CONCAT() 函式用於將多個字串連線成一個字串。

SELECT CONCAT('Hello, ', 'World!') AS greeting;
使用字串作為資料表名查詢。

2.2使用字串作為表名查詢

SET @tablename =users ;
PREPARE StMt FROM 'SELECT * FROM ?';
SET @tablename =users
EXECUTE stmt USING @tablename;
DEALLOCATE PREPARE stmt;


2.3 SUBSTRING()

SUBSTRING() 函式用於擷取字串的子串。可以指定起始位置和長度。

SELECT SUBSTRING('abcdefg', 2, 3) AS sub_string;

結果：

sub_string
-----------
bcd


2.4REPLACE()

REPLACE() 函式用於將字串中的指定子串替換為新的字串。

SELECT REPLACE('Hello World', 'World', 'MySQL') AS replaced_string;

結果：

replaced_string
----------------
Hello MySQL

2.5 LENGTH() 與 CHAR_LENGTH()

LENGTH() 返回字串的位元組長度。
CHAR_LENGTH() 返回字串的字元數（與字元編碼相關）。
SELECT LENGTH('Hello') AS byte_length, CHAR_LENGTH('Hello') AS char_length;

結果：

byte_length | char_length
------------|------------
5 | 5

2.6 UPPER() 與 LOWER()

UPPER() 將字串轉換為大寫，LOWER() 將字串轉換為小寫。

SELECT UPPER('hello') AS upper_case, LOWER('HELLO') AS lower_case;

結果：

upper_case | lower_case
-----------|------------
HELLO | hello

3. 聚合函式

聚合函式用於對多行記錄進行計算，常用於統計分析和報表生成。

3.1 COUNT()

COUNT() 用於計算查詢結果的行數。

SELECT COUNT(*) AS total_rows FROM users;

結果：

total_rows
-----------
100

3.2 SUM()

SUM() 用於計算某列數值的總和。

SELECT SUM(salary) AS total_salary FROM employees;

結果：

total_salary
-------------
250000

3.3 AVG()

AVG() 用於計算某列數值的平均值。

SELECT AVG(salary) AS average_salary FROM employees;

結果：

average_salary
---------------
50000


3.4 MAX() 與 MIN()

MAX() 返回某列的最大值。
MIN() 返回某列的最小值。
SELECT MAX(salary) AS highest_salary, MIN(salary) AS lowest_salary FROM employees;

結果：

highest_salary | lowest_salary
---------------|---------------
100000 | 20000


3.5 GROUP_CONCAT()

GROUP_CONCAT() 用於將一組值連線成一個字串，並以指定的分隔符分隔。

SELECT GROUP_CONCAT(username SEPARATOR ', ') AS user_list FROM users;

結果：

user_list
------------
alice, bob, charlie

4. 常見場景與示例

4.1 日期範圍查詢

假設你需要查詢過去一個月內的訂單：

SELECT * FROM orders
WHERE order_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND CURDATE();


4.2 統計使用者數量

統計每個部門的員工數量：

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;


4.3 合併使用者姓名

假設你有一張使用者表，想要將所有使用者名稱拼接成一個字串：

SELECT GROUP_CONCAT(username SEPARATOR ', ') AS all_usernames
FROM users;

5.定時任務事件語法

5.1開啟事件功能

先檢查是否開啟事件功能：

show variables like 'event_scheduler';
如果’event_scheduler’的值為 'ON’表示排程器已開啟，'OFF’為排程器未開啟

開啟事件：set global event_scheduler = on;
關閉事件：set global event_scheduler = off;

檢視任務 SHOW EVENTS;
這個是檢視現有任務，如果有任務只執行一次的話，執行之前是可以看到的，執行之後就沒有了就查不到了。

5.2建立定時任務事件語法

CREATE
        [DEFINER = { user | CURRENT_USER }]
        EVENT
        [IF NOT EXISTS]
        event_name
        ON SCHEDULE schedule
        [ON COMPLETION [NOT] PRESERVE]
        [ENABLE | DISABLE | DISABLE ON SLAVE]
        [COMMENT 'comment']
        DO event_body;

時間有很多種的，

on schedule every 1 second         -- 每秒執行1次
on schedule every 2 minute         -- 每兩分鐘執行1次
on schedule every 3 day            -- 每3天執行1次
ON schedule every 1 day starts date_add(date_add(curdate(), interval 1 day), interval 1 hour)  -- 每天凌晨1點執行
ON schedule every 1 month starts date_add(date_add(date_sub(curdate(),interval day(curdate())-1 day),interval 1 month),interval 1 hour) -- 每個月的第一天凌晨1點執行

on schedule at current_timestamp()+interval 5 day     -- 5天后執行
on schedule at current_timestamp()+interval 10 minute -- 10分鐘後執行
on schedule at '2016-10-01 21:50:00'                  -- 在2016年10月1日，晚上9點50執行
ON schedule EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + 1 WEEK -- 每 3 個月，從現在起一週後開始
ON schedule  EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK -- 每十二個小時，從現在起三十分鐘後開始，並於現在起四個星期後結束

on schedule every 1 day starts current_timestamp()+interval 5 day ends current_timestamp()+interval 1 month -- 5天后開始每天都執行執行到下個月底
on schedule every 1 day ends current_timestamp()+interval 5 day -- 從現在起每天執行，執行5天

舉例：

建立一個1分鐘後清空student_point表資料的事件 
CREATE EVENT IF NOT EXISTS dsq1
on schedule at current_timestamp()+interval 1 minute -- 1分鐘後執行
DO 
TRUNCATE TABLE student_point;


建立一個每天凌晨1點執行的任務
CREATE EVENT my_event
ON SCHEDULE
EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 1 DAY
ON COMPLETION PRESERVE
DO
-- 這裡是任務的具體操作，可以是SQL語句或儲存過程
INSERT INTO my_table (column1, column2) VALUES ('value1', 'value2');

建立一個每隔3秒往test表中插入一條資料的事件 
CREATE EVENT IF NOT EXISTS e_test_1 
ON SCHEDULE 
EVERY 3 SECOND
ON COMPLETION PRESERVE
DO 
INSERT INTO test(id,t1) VALUES(NULL,NOW());

5.3檢視全部事件：

SHOW EVENTS;

5.4刪除事件：

DROP EVENT [IF EXISTS] event_name(事件名稱);
例如：DROP EVENT event_name;

參考：https://blog.csdn.net/fudaihb/article/details/142211537

https://www.cnblogs.com/qi-yuan-008/p/12782677.html

https://www.cnblogs.com/chaishengblog/p/18138557

相關文章