MySQL 流程控制

流程控制主要用于存储过程、函数和触发器。

IF

IF score >= 90 THEN
  SET grade = 'A';
ELSEIF score >= 80 THEN
  SET grade = 'B';
ELSE
  SET grade = 'C';
END IF;

CASE

CASE
  WHEN score >= 90 THEN SET grade = 'A';
  WHEN score >= 80 THEN SET grade = 'B';
  ELSE SET grade = 'C';
END CASE;

查询里也可以使用 CASE 表达式：

SELECT
  name,
  CASE
    WHEN status = 1 THEN 'enabled'
    WHEN status = 0 THEN 'disabled'
    ELSE 'unknown'
  END AS status_text
FROM users;

WHILE

WHILE i <= total DO
  INSERT INTO logs (message) VALUES (CONCAT('row ', i));
  SET i = i + 1;
END WHILE;

LOOP

read_loop: LOOP
  SET i = i + 1;

  IF i > total THEN
    LEAVE read_loop;
  END IF;
END LOOP;

REPEAT

REPEAT
  SET i = i + 1;
UNTIL i >= total
END REPEAT;

LEAVE 和 ITERATE

• LEAVE：跳出循环，类似 break。
• ITERATE：进入下一次循环，类似 continue。

read_loop: WHILE i < total DO
  SET i = i + 1;

  IF MOD(i, 2) = 1 THEN
    ITERATE read_loop;
  END IF;

  INSERT INTO logs (message) VALUES (CONCAT('even ', i));
END WHILE;