七、事务与锁

7.1 事务 ACID 特性
原子性（Atomicity）：事务中的所有操作要么全部成功，要么全部失败
一致性（Consistency）：事务执行前后，数据保持一致状态
隔离性（Isolation）：并发事务之间互相隔离
持久性（Durability）：事务提交后，数据永久保存
7.2 事务操作

-- 开始事务
START TRANSACTION;
-- 或
BEGIN;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 设置保存点
SAVEPOINT sp1;
-- 回滚到保存点
ROLLBACK TO SAVEPOINT sp1;
-- 释放保存点
RELEASE SAVEPOINT sp1;

-- 示例：银行转账
START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- 检查是否成功
SELECT balance FROM accounts WHERE user_id = 1;
-- 如果余额不足，回滚
IF (SELECT balance FROM accounts WHERE user_id = 1) < 0 THEN
    ROLLBACK;
ELSE
    COMMIT;
END IF;

7.3 隔离级别

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 四种隔离级别
-- READ UNCOMMITTED（读未提交）：可能脏读、不可重复读、幻读
-- READ COMMITTED（读已提交）：避免脏读，可能不可重复读、幻读
-- REPEATABLE READ（可重复读）：MySQL 默认，避免脏读、不可重复读，可能幻读
-- SERIALIZABLE（可串行化）：最高级别，避免所有并发问题

7.4 锁机制

-- 表级锁
-- 读锁（共享锁）
LOCK TABLES users READ;
-- 写锁（排他锁）
LOCK TABLES users WRITE;
-- 解锁
UNLOCK TABLES;

-- 行级锁（InnoDB）
-- 共享锁（S锁）
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;
-- 排他锁（X锁）
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 间隙锁（Gap Lock）
-- 防止幻读，锁定记录之间的间隙
SELECT * FROM users WHERE age BETWEEN 10 AND 20 FOR UPDATE;

-- 死锁示例
-- 事务1
START TRANSACTION;
UPDATE users SET age = 20 WHERE id = 1;
UPDATE users SET age = 25 WHERE id = 2;
COMMIT;

-- 事务2（同时执行）
START TRANSACTION;
UPDATE users SET age = 30 WHERE id = 2;
UPDATE users SET age = 35 WHERE id = 1;
COMMIT;
-- MySQL 会自动检测并回滚其中一个事务

-- 查看锁信息
SHOW ENGINE INNODB STATUS;
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

八、存储过程与函数

8.1 存储过程

-- 创建存储过程
DELIMITER //

CREATE PROCEDURE GetUsersByAge(IN min_age INT, IN max_age INT)
BEGIN
    SELECT * FROM users WHERE age BETWEEN min_age AND max_age;
END //

DELIMITER ;

-- 调用存储过程
CALL GetUsersByAge(20, 30);

-- 带输出参数
DELIMITER //

CREATE PROCEDURE GetUserCount(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM users;
END //

DELIMITER ;

CALL GetUserCount(@count);
SELECT @count;

-- 带输入输出参数
DELIMITER //

CREATE PROCEDURE GetUserInfo(IN user_id INT, OUT user_name VARCHAR(50), OUT user_age INT)
BEGIN
    SELECT name, age INTO user_name, user_age FROM users WHERE id = user_id;
END //

DELIMITER ;

-- 删除存储过程
DROP PROCEDURE IF EXISTS GetUsersByAge;

-- 查看存储过程
SHOW PROCEDURE STATUS;
SHOW CREATE PROCEDURE GetUsersByAge;

8.2 函数

-- 创建函数
DELIMITER //

CREATE FUNCTION GetUserAge(user_id INT) 
RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE user_age INT;

    SELECT age INTO user_age FROM users WHERE id = user_id;
    RETURN user_age;
END //

DELIMITER ;

-- 使用函数
SELECT name, GetUserAge(id) FROM users;

-- 删除函数
DROP FUNCTION IF EXISTS GetUserAge;

-- 函数特性说明
-- DETERMINISTIC: 相同输入总是返回相同输出
-- NOT DETERMINISTIC: 可能返回不同结果
-- READS SQL DATA: 只读取数据，不修改
-- MODIFIES SQL DATA: 会修改数据

8.3 流程控制

DELIMITER //

CREATE PROCEDURE ProcessUser(IN user_id INT)
BEGIN
    DECLARE user_status VARCHAR(20);
    DECLARE user_age INT;

    -- IF 语句
    SELECT age INTO user_age FROM users WHERE id = user_id;

    IF user_age < 18 THEN
        SET user_status = 'minor';
    ELSEIF user_age < 60 THEN
        SET user_status = 'adult';
    ELSE
        SET user_status = 'senior';
    END IF;

    -- CASE 语句
    CASE user_status
        WHEN 'minor' THEN
            UPDATE users SET discount = 0.5 WHERE id = user_id;
        WHEN 'adult' THEN
            UPDATE users SET discount = 0.8 WHERE id = user_id;
        WHEN 'senior' THEN
            UPDATE users SET discount = 0.6 WHERE id = user_id;
    END CASE;

    -- WHILE 循环
    DECLARE counter INT DEFAULT 0;
    WHILE counter < 10 DO
        SET counter = counter + 1;
        -- 循环体
    END WHILE;

    -- REPEAT 循环
    REPEAT
        SET counter = counter - 1;
    UNTIL counter = 0 END REPEAT;

    -- LOOP 循环
    loop_label: LOOP
        IF counter = 0 THEN
            LEAVE loop_label;
        END IF;
        SET counter = counter - 1;
    END LOOP;
END //

DELIMITER ;

九、触发器与事件

9.1 触发器

-- 创建触发器
DELIMITER //

CREATE TRIGGER update_user_timestamp 
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END //

-- 审计日志触发器
CREATE TRIGGER user_audit 
AFTER DELETE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_audit_log (user_id, username, deleted_at, deleted_by)
    VALUES (OLD.id, OLD.username, NOW(), CURRENT_USER());
END //

DELIMITER ;

-- 查看触发器
SHOW TRIGGERS;
SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_NAME = 'update_user_timestamp';

-- 删除触发器
DROP TRIGGER IF EXISTS update_user_timestamp;

-- 触发器类型
-- BEFORE INSERT / AFTER INSERT
-- BEFORE UPDATE / AFTER UPDATE
-- BEFORE DELETE / AFTER DELETE

9.2 事件调度器

-- 开启事件调度器
SET GLOBAL event_scheduler = ON;

-- 创建事件
CREATE EVENT daily_cleanup
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 00:00:00'
DO
    DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- 定时执行
CREATE EVENT hourly_summary
ON SCHEDULE EVERY 1 HOUR
DO
    INSERT INTO user_summary (hour, user_count)
    SELECT HOUR(NOW()), COUNT(*) FROM users WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 HOUR);

-- 指定时间执行一次
CREATE EVENT one_time_event
ON SCHEDULE AT '2024-12-31 23:59:59'
DO
    UPDATE users SET status = 'expired';

-- 查看事件
SHOW EVENTS;
SELECT * FROM information_schema.EVENTS;

-- 删除事件
DROP EVENT IF EXISTS daily_cleanup;

-- 修改事件
ALTER EVENT daily_cleanup 
ON SCHEDULE EVERY 1 WEEK;

来源：
https://app-ad0bpnnq0o3l.appmiaoda.com