MySQL 實用 SQL 技巧大全：數字輔助表、生日計算、工作日統計與常用語句

MySQL 實用 SQL 技巧大全

收集整理高頻實用的 MySQL SQL 技巧，涵蓋數字輔助表構建、不連續 ID 分組填充、生日/年齡計算、工作日統計、常用 DDL/DML 模板等，可直接複製適配業務。

一、數字輔助表：高效生成連續序列

1. 儲存過程快速填充

CREATE TABLE `num` (`id` INT UNSIGNED NOT NULL PRIMARY KEY);

DELIMITER //
CREATE PROCEDURE pnum(cnt INT UNSIGNED)
BEGIN
  DECLARE i INT UNSIGNED DEFAULT 1;
  INSERT INTO num VALUES (1);
  WHILE i * 2 < cnt DO
    INSERT INTO num SELECT i + id FROM num;
    SET i = i * 2;
  END WHILE;
END//
DELIMITER ;

CALL pnum(1000000);  -- 秒級生成 100 萬行

原理：倍增法，log2(N) 輪迴圈完成。

2. 不連續 ID 分組與填充

場景：表 a(id) 值為 1,2,3,100,101,110,111，需找出缺口並分組。

-- 1. 產生連續行號
SET @q = 0;
SELECT id, @q := @q + 1 AS rn FROM a;

-- 2. 差值分組：連續段的 id - rn 恆定
SET @a = 0;
SELECT MIN(id) AS start_id, MAX(id) AS end_id
FROM (
  SELECT id, @a := @a + 1 AS rn, id - @a AS diff
  FROM a
) t
GROUP BY diff;

-- 3. 找出缺口範圍
SELECT a + 1 AS gap_start,
       (SELECT MIN(a) - 1 FROM pincer w WHERE w.a > q.a) AS gap_end
FROM pincer q
WHERE NOT EXISTS (SELECT 1 FROM pincer p WHERE p.a = q.a + 1)
  AND a < (SELECT MAX(a) FROM pincer);

3. 連續出現次數統計（如連續登入天數）

SELECT id, num, ranknum, diff
FROM (
  SELECT id, num, ranknum, num - ranknum AS diff
  FROM (
    SELECT id, num,
           IF(@id = id, @rn := @rn + 1, @rn := 1) AS ranknum,
           @id := id
    FROM tt, (SELECT @rn := 0, @id := NULL) v
  ) x
) y
GROUP BY id, diff
HAVING COUNT(*) >= 2;

二、生日/年齡計算

下一個生日日期（含閏年 2/29 特判）

SELECT name, birthday,
       IF(cur > today, cur, nxt) AS next_birthday
FROM (
  SELECT name, birthday, today,
         DATE_ADD(cur, INTERVAL IF(DAY(birthday)=29 AND DAY(cur)=28, 1, 0) DAY) AS cur,
         DATE_ADD(nxt, INTERVAL IF(DAY(birthday)=29 AND DAY(nxt)=28, 1, 0) DAY) AS nxt
  FROM (
    SELECT name, birthday, today,
           DATE_ADD(birthday, INTERVAL diff YEAR) AS cur,
           DATE_ADD(birthday, INTERVAL diff + 1 YEAR) AS nxt
    FROM (
      SELECT CONCAT(last_name, ' ', first_name) AS name,
             birth_date AS birthday,
             YEAR(NOW()) - YEAR(birth_date) AS diff,
             NOW() AS today
      FROM employees
    ) a
  ) b
) c;

精確年齡（歲）

SELECT FLOOR(DATEDIFF(CURDATE(), birth_date) / 365.25) AS age FROM employees;

三、工作日統計

1. 以 1900-01-01 為基準按週彙總

SELECT 
  DATE_ADD('1900-01-01', INTERVAL FLOOR(DATEDIFF(dt, '1900-01-01')/7)*7 DAY) AS week_start,
  DATE_ADD('1900-01-01', INTERVAL FLOOR(DATEDIFF(dt, '1900-01-01')/7)*7 + 6 DAY) AS week_end,
  SUM(cost) AS weekly_cost
FROM sales
GROUP BY week_start, week_end;

2. 兩日期間工作日天數（存儲過程）

CREATE PROCEDURE p_get_workdays(s DATETIME, e DATETIME)
BEGIN
  SELECT FLOOR(days/7)*5 + days%7
         - CASE WHEN 6 BETWEEN wd AND wd + days%7 - 1 THEN 1 ELSE 0 END
         - CASE WHEN 7 BETWEEN wd AND wd + days%7 - 1 THEN 1 ELSE 0 END AS workdays
  FROM (SELECT DATEDIFF(e, s) + 1 AS days, WEEKDAY(s) + 1 AS wd) t;
END;

呼叫：CALL p_get_workdays('2024-01-01', '2024-01-31');

四、常用 SQL 語句速查模板

五、進階技巧：利用 INFORMATION_SCHEMA 動態生成 SQL

-- 批量生成 TRUNCATE 語句
SELECT CONCAT('TRUNCATE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test_db' AND TABLE_TYPE = 'BASE TABLE';

-- 批量生成 ADD INDEX 語句
SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' ADD INDEX idx_', COLUMN_NAME, '(', COLUMN_NAME, ');')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'test_db' AND COLUMN_KEY = '' AND DATA_TYPE IN ('int','bigint','varchar');

六、避坑提醒

• GROUP BY 選擇非聚合欄位需設定 sql_mode=ONLY_FULL_GROUP_BY（MySQL 5.7+ 預設開啟）
• NOT IN 子查詢含 NULL 會導致空結果，改用 NOT EXISTS 或 LEFT JOIN ... IS NULL
• 大量 DELETE 分批提交，避免鎖升級與 Undo Log 膨脹
• COUNT(*) vs COUNT(col)：前者含 NULL，後者不含
• 隱式類型轉換導致索引失效：WHERE varchar_col = 123 應寫 WHERE varchar_col = '123'