-- 事务A（未提交）
BEGIN;
UPDATE users SET balance=500 WHERE id=1;  -- 修改未提交
-- 事务B（脏读）
BEGIN;
SELECT balance FROM users WHERE id=1;     -- 读到500（脏数据）
-- 事务A回滚
ROLLBACK;
 
 

-- 事务A
BEGIN;
SELECT * FROM products WHERE price > 100;
-- 返回结果：id=1, price=150
 
-- 同时事务B插入新数据
INSERT INTO products VALUES(2, '新商品', 200);
COMMIT;
 
-- 事务A再次查询
SELECT * FROM products WHERE price > 100;
-- 现在返回：id=1, price=150 和 id=2, price=200
-- 出现了"幻影行"id=2
COMMIT;

-- 事务A
BEGIN;
SELECT * FROM products WHERE price > 100 FOR UPDATE; -- 锁定price>100的范围
-- 此时会阻止其他事务在price>100范围内插入数据
 
-- 事务B尝试插入会被阻塞
INSERT INTO products VALUES(3, '新商品2', 180); -- 阻塞直到事务A提交

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.dept_id = departments.id;

SELECT customers.customer_name, orders.order_date
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;
 

SELECT departments.department_name, employees.name
FROM employees
RIGHT JOIN departments ON employees.dept_id = departments.id;

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id
UNION
SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id
WHERE table1.id IS NULL;

-- 二级索引 (age, name, city) 覆盖了查询列
SELECT name, city FROM user WHERE age = 25;
-- Extra: Using index（不再回表）
 

当前记录 → [undo记录1] → [undo记录2] → [undo记录3]
           (旧版本)      (更旧版本)    (最早版本)

新插入行：
- DB_TRX_ID = 当前事务ID
- DB_ROLL_PTR = null
 

实际执行逻辑删除：
1. 将行的DB_TRX_ID设为当前事务ID
2. 设置删除标记为1
3. 通过回滚指针维护版本链
 

1. 先对原记录做DELETE标记
2. 插入新记录（DB_TRX_ID=当前事务ID）
3. 通过回滚指针连接新旧版本
 

-- 检查版本链长度
SELECT COUNT(*) FROM information_schema.INNODB_TRX
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;

[mysqld]
innodb_max_undo_log_size=1G      # 控制undo表空间大小
innodb_purge_threads=4          # 根据CPU核心数设置
transaction-isolation=READ-COMMITTED  # 根据业务需求设置

客户端 → 连接器 → 解析器 → 优化器 → 执行器 → (InnoDB: 缓冲池/索引/锁) → 返回结果

SELECT table_name, row_format
FROM information_schema.tables
WHERE table_schema = 'your_db';

-- 查看表空间分布
SELECT table_name,
       data_length/1024/1024 as data_mb,
       index_length/1024/1024 as index_mb
FROM information_schema.tables
WHERE table_schema = 'your_db';

-- 开始事务
START TRANSACTION;
 
-- 从Alice的账户中扣除100元
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
 
-- 向Bob的账户中增加100元
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
 
-- 提交事务
COMMIT;
 

-- 事务A
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- 未提交
 
-- 事务B（READ UNCOMMITTED）
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;  -- 可能读到事务A未提交的修改

-- 事务A
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;  -- 第一次读取，值为1000
 
-- 事务B
START TRANSACTION;
UPDATE accounts SET balance = 900 WHERE id = 1;
COMMIT;
 
-- 事务A再次读取
SELECT balance FROM accounts WHERE id = 1;  -- 读到新值900（不可重复读）

 
-- 事务A
START TRANSACTION;
SELECT * FROM accounts WHERE balance > 1000;  -- 返回2条记录
 
-- 事务B
START TRANSACTION;
INSERT INTO accounts(id, balance) VALUES(3, 1500);  -- 被间隙锁阻塞
COMMIT;
 
-- 事务A再次查询
SELECT * FROM accounts WHERE balance > 1000;  -- 仍返回2条记录（无幻读）
 

-- 事务A（SERIALIZABLE隔离级别）
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;
-- 实际执行：SELECT balance FROM accounts WHERE id = 1 FOR SHARE;
-- 对id=1的行加S锁（共享锁）
 
-- 事务B（任何隔离级别）
START TRANSACTION;
UPDATE accounts SET balance = 800 WHERE id = 1;
-- 尝试对id=1的行加X锁（排他锁），但S锁与X锁互斥，因此被阻塞
-- 直到事务A提交或超时（innodb_lock_wait_timeout）

 
-- 1. 查看当前隔离级别
SELECT @@transaction_isolation;
 
-- 2. 设置SERIALIZABLE级别（当前会话）
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 
-- 3. 监控锁等待
SHOW ENGINE INNODB STATUS;  -- 查看LATEST DETECTED DEADLOCK段
 

START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- 显式加X锁
COMMIT;

CREATE TABLE t (
  a INT, b INT, c INT, d INT,
  INDEX idx_a_b_c (a, b, c)
);

-- 完全匹配规则 --
-- ✅ 完全匹配（使用全部索引）
SELECT * FROM table WHERE a=1 AND b=2 AND c=3;
 
-- ✅ 顺序不影响（优化器自动调整）
SELECT * FROM table WHERE b=2 AND a=1 AND c=3;
 
-- 最左前缀规则 --
-- ✅ 使用a列索引
SELECT * FROM table WHERE a=1;
 
-- ✅ 使用a,b列索引
SELECT * FROM table WHERE a=1 AND b=2;
 
-- ❌ 缺少最左列a（索引失效）
SELECT * FROM table WHERE b=2;
 
-- ❌ 跳过中间列b（只能用到a）
SELECT * FROM table WHERE a=1 AND c=3;
 
-- 范围查询后的列失效 --
-- ✅ 使用a,b列索引（c失效）
SELECT * FROM table WHERE a=1 AND b>2 AND c=3;
 
-- ✅ 使用a,b,c索引（LIKE右匹配不算范围）
SELECT * FROM table WHERE a=1 AND b LIKE 'abc%' AND c=3;
 
-- ❌ 范围查询放前面（只能用到a）
SELECT * FROM table WHERE a>1 AND b=2 AND c=3;
 

-- ORDER BY优化 --
-- ✅ 使用完整索引排序
SELECT * FROM table ORDER BY a, b, c;
 
-- ✅ 使用前缀索引排序
SELECT * FROM table ORDER BY a, b;
 
-- ❌ 非最左前缀排序（filesort）
SELECT * FROM table ORDER BY b, c;
 
 
-- 混合排序方向 --
-- ✅ 方向一致可使用索引
SELECT * FROM table ORDER BY a ASC, b ASC, c ASC;
SELECT * FROM table ORDER BY a DESC, b DESC, c DESC;
 
-- ❌ 方向不一致导致filesort
SELECT * FROM table ORDER BY a ASC, b DESC;

-- ✅ 使用覆盖索引（无需回表）
SELECT a, b FROM table WHERE a=1 AND b=2;
 
-- ❌ 需要回表（查c列但条件不含c）
SELECT a, b, c FROM table WHERE a=1 AND b=2;

-- 函数/运算导致失效
-- ❌ 索引失效
SELECT * FROM table WHERE YEAR(a)=2023;
SELECT * FROM table WHERE b+1=10;
 
-- 类型不匹配
-- ❌ 假设a是字符串类型
SELECT * FROM table WHERE a=123;
 
-- 不等于/IS NOT NULL
-- ❌ 索引可能失效
SELECT * FROM table WHERE a!=1;
SELECT * FROM table WHERE b IS NOT NULL;
 
 

EXPLAIN SELECT * FROM t WHERE a=1 AND b>2 ORDER BY c;
-- 优化器可能选 idx_a_b_c 利用 a,b 过滤，但排序需要 filesort
-- 把索引改成 (a,b,c) 或 (a,c,b) 才能避免 filesort，取决于过滤 vs 排序谁更重要

# at 4
#250708 10:00:00 server id 1  end_log_pos 123 CRC32 0x12345678  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1688790000/*!*/;
INSERT INTO test VALUES (1, 'Kimi')
/*!*/;
# at 123
#250708 10:00:01 server id 1  end_log_pos 234 CRC32 0x87654321  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1688790001/*!*/;
UPDATE test SET name = 'Moonshot' WHERE id = 1
/*!*/;
# at 234
#250708 10:00:02 server id 1  end_log_pos 345 CRC32 0x11223344  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1688790002/*!*/;
DELETE FROM test WHERE id = 1
/*!*/;

# at 4
#250708 10:00:00 server id 1  end_log_pos 123 CRC32 0x12345678  Table_map: `test`.`test` mapped to number 1
#250708 10:00:00 server id 1  end_log_pos 234 CRC32 0x87654321  Write_rows: table id 1 flags: STMT_END_F
### INSERT INTO `test`.`test`
### SET
###   @1=1
###   @2='Kimi'
# at 234
#250708 10:00:01 server id 1  end_log_pos 345 CRC32 0x11223344  Update_rows: table id 1 flags: STMT_END_F
### UPDATE `test`.`test`
### WHERE
###   @1=1
###   @2='Kimi'
### SET
###   @1=1
###   @2='Moonshot'
# at 345
#250708 10:00:02 server id 1  end_log_pos 456 CRC32 0x44556677  Delete_rows: table id 1 flags: STMT_END_F
### DELETE FROM `test`.`test`
### WHERE
###   @1=1
###   @2='Moonshot'

# at 4
#250708 10:00:00 server id 1  end_log_pos 123 CRC32 0x12345678  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1688790000/*!*/;
INSERT INTO test VALUES (1, 'Kimi')
/*!*/;
# at 123
#250708 10:00:01 server id 1  end_log_pos 234 CRC32 0x87654321  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1688790001/*!*/;
UPDATE test SET name = 'Moonshot' WHERE id = 1
/*!*/;
# at 234
#250708 10:00:02 server id 1  end_log_pos 345 CRC32 0x11223344  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1688790002/*!*/;
DELETE FROM test WHERE id = 1
/*!*/;

-- 加表读锁（其他会话可读不可写）
LOCK TABLE users READ;
-- 加表写锁（其他会话不可读写）
LOCK TABLE users WRITE;
-- 解锁
UNLOCK TABLES;

-- 共享锁（其他事务可读不可写）
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;
 
-- 排他锁（其他事务不可读写）
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

BEGIN;
-- 自动在表上加IX锁，在行id=1上加X锁
SELECT * FROM users WHERE id = 1 FOR UPDATE;
 
-- 另一个事务尝试加表锁时会检查意向锁
LOCK TABLE users WRITE; -- 会阻塞，因为检测到IX锁存在
 

-- 事务1：下单扣库存
BEGIN;
-- 锁定商品库存记录
SELECT stock FROM products WHERE product_id = 1001 FOR UPDATE;
-- 检查库存
IF stock > 0 THEN
  UPDATE products SET stock = stock - 1 WHERE product_id = 1001;
END IF;
COMMIT;
 

-- 假设现有id为1,5,10的记录
BEGIN;
SELECT * FROM products WHERE id BETWEEN 3 AND 7 FOR UPDATE;
-- 这会锁定(1,5)和(5,10)这两个间隙
-- 其他事务不能插入id=4或id=6的记录

-- 1. 使用STRAIGHT_JOIN强制顺序
SELECT * FROM small_table STRAIGHT_JOIN large_table ON...
 
-- 2. 调整FROM子句顺序(优化器会重排)
SELECT * FROM table1, table2 WHERE...
 
-- 3. 使用优化器提示
SELECT /*+ JOIN_ORDER(t1, t2) */ * FROM t1 JOIN t2...

-- 低效: 使用子查询
SELECT * FROM orders WHERE customer_id IN
(SELECT id FROM customers WHERE status='active');
 
-- 高效: 改写为JOIN
SELECT o.* FROM orders o JOIN customers c
ON o.customer_id=c.id WHERE c.status='active';

-- 分批删除
SET @batch_size = 10000;
SET @offset = 0;
 
WHILE (1) DO
  DELETE FROM your_table
  LIMIT @batch_size OFFSET @offset;
 
  SET @offset = @offset + @batch_size;
 
  -- 暂停一段时间，避免对服务器造成过大压力
  DO SLEEP(1);
END WHILE;

SELECT a.*, b.*
FROM table_a a
JOIN table_b b ON a.id = b.a_id
WHERE a.status = 'active';

-- 先查询 table_a 中符合条件的记录
SELECT id, name
FROM table_a
WHERE status = 'active';
 
-- 然后根据查询结果逐条查询 table_b
SELECT *
FROM table_b
WHERE a_id IN (SELECT id FROM table_a WHERE status = 'active');

-- 原查询
SELECT * FROM your_table WHERE status = 'active';
 
-- 优化后的查询
SELECT id, name, status
FROM your_table
WHERE status = 'active';

-- 查询表 A
SELECT id, name
FROM table_a
WHERE status = 'active';
 
-- 查询表 B
SELECT id, a_id, value
FROM table_b
WHERE a_id IN (SELECT id FROM table_a WHERE status = 'active');
 

-- 原查询
SELECT * FROM your_table FOR UPDATE;
 
-- 优化后的查询
SELECT id, name
FROM your_table
WHERE status = 'active' FOR UPDATE;

-- 第一页（常规查询）
SELECT * FROM orders ORDER BY id LIMIT 20;
 
-- 后续分页（记住上次最大ID）
SELECT * FROM orders
WHERE id > 上一页最后一条记录的ID
ORDER BY id
LIMIT 20;

-- 按时间+ID排序的分页
SELECT * FROM posts
WHERE (created_at, id) > ('2023-01-01 12:00:00', 1000)
ORDER BY created_at, id
LIMIT 20;

-- 原子查询
SELECT a.*
FROM table_a a
WHERE a.id IN (SELECT b.a_id FROM table_b b WHERE b.status = 'active');
 
-- 优化后的关联查询
SELECT a.*
FROM table_a a
JOIN table_b b ON a.id = b.a_id
WHERE b.status = 'active';

-- 原查询
SELECT user_id, COUNT(*)
FROM orders
GROUP BY user_id;
 
-- 索引优化
CREATE INDEX idx_user_id ON orders(user_id);
 
-- 查询优化
SELECT user_id, COUNT(*)
FROM orders
GROUP BY user_id;

-- 原查询（低效）
SELECT department_name, COUNT(*)
FROM employees
GROUP BY department_name;
 
-- 优化后（高效）
SELECT d.department_name, COUNT(*)
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY e.department_id;  -- 使用关联键分组

-- 原查询（使用非索引列分组）
SELECT c.country_name, COUNT(*)
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.country_name;
 
-- 优化方案1：使用客户ID分组
SELECT c.country_name, COUNT(*)
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY o.customer_id;  -- 订单表已有customer_id索引
 
-- 优化方案2：使用覆盖索引
ALTER TABLE customers ADD INDEX idx_country (country_name, customer_id);
 
SELECT c.country_name, COUNT(*)
FROM customers c
WHERE customer_id IN (SELECT customer_id FROM orders)
GROUP BY c.country_name;  -- 现在country_name在索引中

-- 原查询
SELECT user_id, COUNT(*)
FROM orders
GROUP BY user_id
ORDER BY user_id;
 
-- 优化后的查询
SELECT user_id, COUNT(*)
FROM orders
GROUP BY user_id
ORDER BY NULL;

-- 原查询
SELECT user_id, COUNT(*)
FROM orders
GROUP BY user_id WITH ROLLUP;
 
-- 优化后的查询
SELECT user_id, COUNT(*)
FROM orders
GROUP BY user_id;
 
-- 在应用程序中处理超级聚合

-- 创建最左匹配索引
ALTER TABLE users ADD INDEX idx_name_age (last_name, age);
 
-- 有效查询
SELECT * FROM users
WHERE last_name='Smith' AND age>30;  -- 使用索引
 
-- 无效查询
SELECT * FROM users WHERE age>30;    -- 不使用索引

-- 低效写法
SELECT * FROM orders
WHERE total_amount/100 > 50;
 
-- 高效写法
SELECT * FROM orders
WHERE total_amount > 50*100;

-- 离散值用IN
SELECT * FROM products
WHERE category_id IN (1, 3, 5);
 
-- 连续范围用BETWEEN
SELECT * FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';

-- 低效OR
SELECT * FROM logs
WHERE type='error' OR priority=1;
 
-- 优化方案1：UNION ALL
SELECT * FROM logs WHERE type='error'
UNION ALL
SELECT * FROM logs WHERE priority=1;
 
-- 优化方案2：改写为IN
SELECT * FROM logs
WHERE type='error' OR (type='warning' AND priority=1);

-- 低效（可能不使用索引）
PREPARE stmt FROM 'SELECT * FROM users WHERE age > ?';
SET @age = 30;
EXECUTE stmt USING @age;
 
-- 高效（强制索引）
PREPARE stmt FROM 'SELECT * FROM users FORCE INDEX(idx_age) WHERE age > ?';

DELIMITER //
CREATE PROCEDURE GetUsers(IN minAge INT)
BEGIN
    -- 使用提示强制索引
    SELECT * FROM users USE INDEX(idx_age)
    WHERE age > minAge;
END //
DELIMITER ;

-- 事务1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- 获取id=1的X锁
 
-- 同时事务2
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2; -- 获取id=2的X锁
 
-- 事务1尝试获取id=2的锁
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- 等待事务2释放锁
 
-- 事务2尝试获取id=1的锁
UPDATE accounts SET balance = balance + 50 WHERE id = 1; -- 等待事务1释放锁
 
-- 此时MySQL检测到死锁，会回滚其中一个事务
 

-- 低效(可能锁全表)
UPDATE orders SET status = 'shipped' WHERE customer_id = 100;
 
-- 优化后
ALTER TABLE orders ADD INDEX idx_customer (customer_id);
UPDATE orders USE INDEX(idx_customer) SET status = 'shipped' WHERE customer_id = 100;
 

// 错误示范
@Transactional
public void processOrder() {
    // 复杂业务逻辑
    // 远程API调用
    // 文件操作
}
 
// 正确做法
public void businessProcess() {
    // 非事务操作
    remoteService.call();
 
    // 仅数据库操作在事务内
    transactionalService.updateData();
}
 

# 所有事务按固定顺序操作表
def transfer_funds(sender, receiver):
    lock_order = sorted([sender, receiver])
    with transaction.atomic():
        withdraw(lock_order[0])
        deposit(lock_order[1])

-- 粗粒度锁(不推荐)
SELECT * FROM products WHERE category = 'electronics' FOR UPDATE;
 
-- 细粒度锁(推荐)
SELECT * FROM products WHERE sku = 'ABC123' FOR UPDATE;

UPDATE inventory
SET stock = stock - 1,
    version = version + 1
WHERE item_id = 100 AND version = 5;

-- 将事务隔离级别降为READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
-- 或精确化查询条件避免间隙锁
UPDATE orders SET status = 'shipped' WHERE id IN (1, 2, 3);

-- 按固定顺序操作（先父表后子表）
START TRANSACTION;
UPDATE parent_table ...;
UPDATE child_table ...;
COMMIT;
 

# my.cnf配置
[mysqld]
innodb_print_all_deadlocks = ON      # 记录所有死锁到错误日志
innodb_lock_wait_timeout = 30        # 锁等待超时(秒)

-- 查看最近死锁
SHOW ENGINE INNODB STATUS\G
/* 重点观察
LATEST DETECTED DEADLOCK
TRANSACTION TRX_ID
WAITING FOR THIS LOCK
*/

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-08-20 14:25:36 0x7f8e3c2e6700
*** (1) TRANSACTION:
TRANSACTION 42156, ACTIVE 12 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 32, OS thread handle 139887, query id 1234 192.168.1.1 root updating
UPDATE accounts SET balance = balance - 100 WHERE id = 1
 
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 45 page no 3 n bits 72 index PRIMARY of table `test`.`accounts` trx id 42156 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 
*** (1) WAITING FOR THIS LOCK:
RECORD LOCKS space id 45 page no 4 n bits 72 index PRIMARY of table `test`.`accounts` trx id 42156 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 
*** (2) TRANSACTION:
TRANSACTION 42157, ACTIVE 10 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 33, OS thread handle 139888, query id 1235 192.168.1.1 root updating
UPDATE accounts SET balance = balance + 100 WHERE id = 2
 
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 45 page no 4 n bits 72 index PRIMARY of table `test`.`accounts` trx id 42157 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 
*** (2) WAITING FOR THIS LOCK:
RECORD LOCKS space id 45 page no 3 n bits 72 index PRIMARY of table `test`.`accounts` trx id 42157 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 
*** WE ROLL BACK TRANSACTION (2)

-- 1. 终止阻塞的事务（需SUPER权限）
KILL 33;
 
-- 2. 调整锁等待超时（临时生效）
SET SESSION innodb_lock_wait_timeout = 30;

// 统一资源访问顺序示例
public void transferMoney(int fromId, int toId, BigDecimal amount) {
    // 固定按ID排序获取锁
    int firstId = Math.min(fromId, toId);
    int secondId = Math.max(fromId, toId);
 
    synchronized(getLockObject(firstId)) {
        synchronized(getLockObject(secondId)) {
            // 执行转账操作
        }
    }
}

CREATE TABLE Logs(
    id  INT PRIMARY KEY AUTO_INCREMENT,
    num INT NOT NULL
);

SELECT DISTINCT l1.num AS ConsecutiveNums
FROM Logs l1
JOIN Logs l2 ON l1.id = l2.id + 1 AND l1.num = l2.num
JOIN Logs l3 ON l1.id = l3.id + 2 AND l1.num = l3.num;
 

WITH cte AS (
    SELECT
        num,
        LEAD(num,1) OVER (ORDER BY id) AS n1,
        LEAD(num,2) OVER (ORDER BY id) AS n2
    FROM Logs
)
SELECT DISTINCT num AS ConsecutiveNums
FROM cte
WHERE num = n1 AND num = n2;
 

SELECT DISTINCT num AS ConsecutiveNums
FROM (
    SELECT
        num,
        @cnt   := IF(@prev = num, @cnt + 1, 1) AS cnt,
        @prev  := num
    FROM Logs, (SELECT @cnt := 0, @prev := NULL) init
    ORDER BY id
) AS t
WHERE cnt >= 3;
 

WITH RECURSIVE r AS (
    -- 锚点：从第 1 行开始
    SELECT id, num, 1 AS cnt
    FROM Logs
    WHERE id = 1
 
    UNION ALL
 
    -- 递归：下一行
    SELECT l.id, l.num,
           CASE WHEN l.num = r.num THEN cnt + 1 ELSE 1 END
    FROM Logs l
    JOIN r ON l.id = r.id + 1
)
SELECT DISTINCT num AS ConsecutiveNums
FROM r
WHERE cnt >= 3;