•【Part 4】MySQL 進階實戰：Trigger 觸發器、Stored Procedure、View 動態視圖與交易控制實戰
Mysql 實務操作
☑️ TRIGGER 觸發器
通常一旦建立了觸發器，就不能直接修改觸發器的定義，要重新建立
SHOW TRIGGERS
DROP TRIGGER employee_age_sum
CREATE TRIGGER trigger_name
{BEFORE/AFTER} {INSERT/UPDATE/DELETE} ON table_name
FOR EACH ROW
{{FOLLOWS/PROCEDES} existing_trigger_name}
[[some statement we want to execute by trigger]]
{WHERE column_name = {OLD/NEW}.column_name}
PS 當插入新的TRIGGER，不能搭配OLD、刪除TRIGGER，不能搭配NEW，因為 DELETE 觸發器是在刪除行之前觸發的，沒有新的行可參照
可以先創建 new_user
設置權限
每次有新增員工，就將新增的 employee 員工年齡加總
CREATE TRIGGER employee_age_sum
BEFORE INSERT
ON employee
FOR EACH ROW
SET @sum = @sum + NEW.age ;
通常會搭配DELIMITER 一起使用
DELIMITER // 會暫時將 SQL 語句的結束符從 ; 更改為 //
視為一個單一的語句，直到看到 // 才結束
DROP TRIGGER if exists member_purc_trigger ;
DELIMITER //
CREATE TRIGGER member_purc_trigger
BEFORE INSERT
ON member_purchase
FOR EACH ROW
BEGIN
insert into member_permission(member_id,permission)
value( NEW.purchase_member_id , NEW.purchase_permission);
END
// DELIMITER ;
練習
假設電商網站要固定更新庫存，已經有兩張表
CREATE TABLE customer_purchases (
customer_purchase_id BIGINT UNSIGNED NOT NULL, -- BIGINT UNSIGNED，無符號的大整數，能夠儲存非常大的正數，適合用作 ID
customer_id BIGINT UNSIGNED NOT NULL,
inventory_id BIGINT UNSIGNED NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=UTF8MB4; -- 預設字符集為 UTF8MB4，支持廣泛字符（包括表情符號和多語言字符）
INSERT INTO customer_purchases VALUES
(1, 1, 3),
(2, 1, 2),
(3, 1, 4),
(4, 1, 7),
(5, 2, 5),
(6, 3, 1),
(7, 4, 6),
(8, 5, 8),
(9, 6, 9),
(10, 2, 3),
(11, 1, 2),
(12, 1, 6)
;
COMMIT;
CREATE TABLE purchase_summary (
purchase_summary_id BIGINT UNSIGNED NOT NULL,
customer_id BIGINT UNSIGNED NOT NULL,
total_purchases BIGINT UNSIGNED NOT NULL,
purchase_excluding_last BIGINT UNSIGNED NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=UTF8MB4;
INSERT INTO purchase_summary VALUES
(1, 1, 6, 5),
(2, 2, 2, 1),
(3, 3, 1, 0),
(4, 4, 1, 0),
(5, 5, 1, 0),
(6, 6, 1, 0)
;
COMMIT;
當對 customer_purchases 表執行 INSERT 操作時，這個觸發器會被觸
CREATE TRIGGER PurchaseUpdatePurchaseSummary_before
BEFORE INSERT ON customer_purchases
FOR EACH ROW
UPDATE purchase_summary
SET purchase_excluding_last = (
SELECT COUNT(customer_purchase_id)
FROM customer_purchases WHERE customer_purchases.customer_id = purchase_summary.customer_id
)
WHERE customer_id = NEW.customer_id
AND purchase_summary_id > 0;
CREATE TRIGGER PurchaseUpdatePurchaseSummary_after
AFTER INSERT ON customer_purchases
FOR EACH ROW
UPDATE purchase_summary
SET total_purchases = (
SELECT COUNT(customer_purchase_id)
FROM customer_purchases WHERE customer_purchases.customer_id = purchase_summary.customer_id
)
WHERE customer_id = NEW.customer_id
AND purchase_summary_id > 0;
select * from purchase_summary;
select * from customer_purchases;
INSERT INTO customer_purchases VALUES
(15,1,4);
練習
假設電商網站的訂單系統，已經有table order_items
我又創建出整理的表 orders，再設置觸發器
create table order_items (
order_item_id BIGINT,
created_at DATETIME,
order_id BIGINT,
price_usd DECIMAL(6,2),
cogs_usd DECIMAL(6,2),
website_session_id BIGINT,
PRIMARY KEY (order_item_id)
);
CREATE TABLE orders (
order_id BIGINT,
created_at DATETIME,
website_session_id BIGINT,
primary_product_id BIGINT,
item_purchased BIGINT,
price_usd DECIMAL(6,2),
cogs_usd DECIMAL(6,2),
PRIMARY KEY (order_id)
);
CREATE TRIGGER insert_new_orders
AFTER INSERT ON order_items
FOR EACH ROW
REPLACE INTO orders
SELECT
order_id,
MIN(created_at) AS created_at,
MIN(website_session_id) AS website_session_id,
SUM(CASE
WHEN is_primary_items = 1 THEN product_id
ELSE NULL
END) AS primary_product_id,
COUNT(order_item_id) AS item_purchased,
SUM(price_usd) AS price_usd,
SUM(cogs_usd) AS cogs_usd
FROM order_items
WHERE order_id - new.order_id
GROUP BY 1
ORDER BY 1;
☑️ Stored Procedure 存儲過程
PS 如果要查詢和報告數據，使用 CREATE VIEW，建立一個「虛擬的表」，可以重複查詢它
如果要執行較複雜的指令、控制事務，或者高級的數據庫操作，使用 Stored Procedure，並再之後可以調用
DELIMITER //
DROP PROCEDURE IF EXISTS custimer_rentals;
CREATE PROCEDURE custimer_rentals
(IN custid BIGINT)
BEGIN
SELECT * FROM rental
WHERE customer_id = custid;
END //
DELIMITER ;
CALL custimer_rentals(20);
# 方法一
DELIMITER //
CREATE PROCEDURE order_performance()
BEGIN
SELECT
COUNT(order_id) AS total_orders,
SUM(price_usd) AS total_revenue
FROM orders
WHERE DATE(created_at) BETWEEN '2013-11-01' AND '2013-12-31';
END //
DELIMITER ;
CALL order_performance;
# 方法二
DELIMITER //
CREATE PROCEDURE order_performance
(IN startdate DATE, IN enddate DATE)
BEGIN
SELECT
COUNT(order_id) AS total_orders,
SUM(price_usd) AS total_revenue
FROM orders
WHERE DATE(created_at) BETWEEN startdate AND enddate;
END //
DELIMITER ;
CALL order_performance('2013-11-01','2013-12-31');
☑️ VIEW 動態視圖
CREATE VIEW 不會複製資料，佔用空間
VIEW是一個動態的查詢，會即時更新。如果直接複製表格，複製的表格內容不會自動更新
CREATE VIEW country_averages AS
SELECT
country,
AVG(years_experience) AS yrs_experience,
AVG (CASE WHEN is_manager = 'Yes' THEN 1 ELSE 0 END) AS pct_mgrs,
AVG (CASE WHEN education_level = 'Masters' THEN 1 ELSE 0 END) AS pct_masters
FROM salary_survey
GROUP BY 1
ORDER BY 2 DESC;
`
☑️ EVENT… ON SCHEDULE DO 定時任務
表格中藥有 DATETIME
CREATE TABLE sillytable (
timestamps_via_event DATETIME
);
CREATE EVENT myfirstevent
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 7 second
DO
INSERT INTO sillytable VALUES (NOW());
SELECT * FROM sillytable;
CREATE EVENT mysecondevent
ON SCHEDULE EVERY 5 second
DO INSERT INTO sillytable VALUES (NOW());
☑️ 開始(begin or start transaction)、提交(commit)、回溯(rollback)、建立保存點(savepoint
<> 刪除 release savepoint)、回復保存點(rollback to [savepoint])
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50),
salary DECIMAL(10, 2)
);
-- 開始
START TRANSACTION;
-- 插入數據
INSERT INTO employees (employee_id, employee_name, salary)
VALUES (1, 'Alice', 50000.00);
-- 建立保存點
SAVEPOINT my_savepoint;
-- 試試插入有衝突的數據
INSERT INTO employees (employee_id, employee_name, salary)
VALUES (1, 'Bob', 55000.00);
-- 如果有錯，回復保存點
IF @@ERROR_COUNT > 0 THEN
ROLLBACK TO my_savepoint;
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '回復保存點';
ELSE
-- 如果沒錯，提交
COMMIT;
END IF;
☑️ 複製表
CREATE TABLE NEW_FOOD LIKE FOOD;
INSERT INTO NEW_FOOD SELECT * FROM FOOD;
