MySQL 包含大量非標準擴展語法，這賦予了開發便利性，但也帶來了遷移風險。理解哪些是標準 SQL、哪些是 MySQL 專屬、如何編寫可移植代碼、如何啟用 ANSI 模式，是跨資料庫架構設計的必修課。本文系統梳理 MySQL 擴展、標準差異、缺失功能與移植策略。
MySQL 專屬擴展語法清單 (部分)
| 類別 | 擴展語法 | 標準替代/備註 |
|---|
| 資料類型 | MEDIUMINT, TINYBLOB/TEXT, MEDIUMBLOB/TEXT, LONGBLOB/TEXT | 標準僅有 SMALLINT/INT/BIGINT, BLOB/TEXT |
| ENUM, SET | 標準無對應，可用 CHECK + 字典表 |
| BINARY, UNSIGNED, ZEROFILL 屬性 | 標準無 UNSIGNED/ZEROFILL |
| AUTO_INCREMENT | 標準: GENERATED ALWAYS AS IDENTITY (SQL:2003) |
| 字串比較 | 預設忽略大小寫 (依字元集排序規則) | 標準區分大小寫；用 BINARY/字元集控制 |
| 字串常數支援雙引號 "..." | 標準雙引號為識別符，單引號為字串 |
| 轉義字元 \ | 標準無統一轉義，依實現 |
| DDL | CREATE TABLE ... INDEX/KEY 內建索引定義 | 標準: CREATE INDEX 獨立語句 |
| CREATE TEMPORARY TABLE / IF NOT EXISTS | 標準無 TEMPORARY/IF NOT EXISTS |
| ALTER TABLE 多重 ADD/ALTER/DROP/CHANGE 子句 | 標準單一子句；MySQL 批次優化 |
| DROP TABLE IF EXISTS / 多表單語句 | 標準單表、無 IF EXISTS |
| DML | INSERT ... ON DUPLICATE KEY UPDATE | 標準: MERGE (SQL:2003) |
| REPLACE INTO | MySQL 專屬 (DELETE+INSERT) |
| INSERT/REPLACE/DELETE/UPDATE LOW_PRIORITY | 標準無優先級提示 |
| INSERT DELAYED | MySQL 專屬 (8.0 移除) |
| DELETE/UPDATE LIMIT n | 標準無 LIMIT 子句 |
| 查詢 | SELECT ... INTO OUTFILE | 標準無檔案導出 |
| STRAIGHT_JOIN, SQL_SMALL_RESULT | 優化器提示，標準無 |
| EXPLAIN SELECT | 標準無 EXPLAIN (各廠商自定) |
| 函數 | IF(), IFNULL(), COALESCE() 擴展參數 | 標準 CASE WHEN / COALESCE |
| MD5(), SHA1(), ENCODE(), DECODE(), AES_ENCRYPT() | 標準無加密函數 |
| DATE_FORMAT(), TIMESTAMPDIFF(), PERIOD_ADD() | 標準日期函數較少 |
| 運算子 | || 為 OR (非字串連接), && 為 AND | 標準 || 為字串連接；用 CONCAT() |
| 註解 | # 註解, /* */, -- (需空格) | 標準 -- 註解 |
可移植寫法：條件註解 /*! ... */
MySQL 解析並執行 `/*! ... */` 內的代碼，其他資料庫視為註解忽略。
-- 僅 MySQL 使用 STRAIGHT_JOIN 強制連接順序
SELECT /*! STRAIGHT_JOIN */ col1 FROM t1, t2 WHERE t1.id = t2.id;
-- 版本條件：僅 3.23.02+ 執行 TEMPORARY
CREATE /*!32302 TEMPORARY */ TABLE t (a INT);
標準差異陷阱 (MySQL 行為 ≠ ANSI SQL)
| 場景 | MySQL 行為 | ANSI SQL 標準 | 影響 |
|---|
| VARCHAR 尾部空格 | **儲存時截斷尾部空格** | 保留尾部空格 | 比較/排序/長度計算差異 |
| CHAR 隱式轉 VARCHAR | 某些情況自動轉換 | 不轉換 | 儲存長度/比較語義變化 |
| DROP TABLE 權限不自動撤銷 | 需顯式 REVOKE | 通常級聯撤銷 | 殘留權限風險 |
| 註解 -- | **需後接空格** (-- comment) | -- 即啟動註解 | 自動生成 SQL 易出錯 (如 1--1) |
| GROUP BY 非聚合欄位 | 允許 (ONLY_FULL_GROUP_BY 關閉時) | 禁止 (SQL:1999+) | 結果不確定性 |
| || 運算子 | 邏輯 OR | 字串連接 | 遷移極易語義錯誤 |
MySQL 缺失的標準功能 (歷史版本，現代版本多已支援)
| 功能 | 標準語法 | MySQL 替代方案 | 支援版本 |
|---|
| 子查詢 | SELECT * FROM t1 WHERE id IN (SELECT id FROM t2) | JOIN / 臨時表 / CONCAT 構建 DELETE | 4.1+ 完整支援 |
| SELECT INTO TABLE | SELECT * INTO new_tbl FROM src | CREATE TABLE ... SELECT / INSERT ... SELECT | -- |
| 事務 (COMMIT/ROLLBACK) | START TRANSACTION / COMMIT / ROLLBACK | LOCK TABLES / 自動提交控制 | 3.23+ (InnoDB/BDB) |
| 存儲過程 | CREATE PROCEDURE | 應用層封裝 / UDF | 5.0+ |
| 觸發器 | CREATE TRIGGER | 應用層邏輯 | 5.0+ |
| 外鍵約束 | FOREIGN KEY ... ON DELETE CASCADE | 應用層維護 / 僅語法相容不執行 | 3.23+ (InnoDB 實際執行) |
| 視圖 | CREATE VIEW | -- | 5.0+ |
| 游標 | DECLARE CURSOR | 應用層迭代 | 5.0+ (存儲程序內) |
ANSI 模式啟動與行為變更
# 啟動參數
mysqld --ansi
# 或配置檔
[mysqld]
sql_mode = ANSI
ANSI 模式關鍵行為變更
| 特性 | ANSI 模式 | 預設模式 |
|---|
| || 運算子 | 字串連接 (標準) | 邏輯 OR |
| 函數名與括號間空格 | 允許任意空格 | 無空格要求 |
| " 雙引號 | 識別符引號 (標準) | 字串引號 |
| REAL 類型 | FLOAT 同義詞 | DOUBLE 同義詞 |
| 保留字 | 所有函數名變保留字 | 部分保留 |
移植策略：從 MySQL 遷移至 PostgreSQL/Oracle/SQL Server
1. 型別映射表
| MySQL | PostgreSQL | Oracle | SQL Server |
|---|
| TINYINT | SMALLINT | NUMBER(3) | TINYINT |
| MEDIUMINT | INTEGER | NUMBER(7) | SMALLINT |
| ENUM('a','b') | CHECK (col IN ('a','b')) | CHECK (col IN ('a','b')) | CHECK (col IN ('a','b')) |
| SET('a','b') | ARRAY / 關聯表 | 關聯表 | 關聯表 |
| AUTO_INCREMENT | SERIAL / IDENTITY | SEQUENCE / IDENTITY (12c+) | IDENTITY |
| DATETIME | TIMESTAMP | DATE / TIMESTAMP | DATETIME2 |
| TIMESTAMP | TIMESTAMPTZ | TIMESTAMP WITH TIME ZONE | DATETIMEOFFSET |
2. 函數重寫對照
-- MySQL -- PostgreSQL/Oracle
IF(cond, t, f) → CASE WHEN cond THEN t ELSE f END
IFNULL(a, b) → COALESCE(a, b)
DATE_FORMAT(d, '%Y-%m') → TO_CHAR(d, 'YYYY-MM') / EXTRACT
TIMESTAMPDIFF(DAY, a,b) → (b - a) / EXTRACT(DAY FROM b-a)
LAST_INSERT_ID() → currval('seq') / RETURNING id
CONCAT(a,b,c) → a || b || c
MD5(str) → md5(str) / DBMS_CRYPTO.HASH
AES_ENCRYPT(str, key) → pgp_sym_encrypt / DBMS_CRYPTO.ENCRYPT
3. 遷移工具鏈
- Schema 遷移：
mysqldump --no-data --compatible=postgresql → 手工修正 → 目標庫執行
- 資料遷移：
pgloader (MySQL→PostgreSQL)、SQLines、AWS DMS、Oracle SQL Developer Migration
- 驗證：行數對比、檢查和對比、關鍵業務查詢結果對比
總結
MySQL 的擴展語法極大提升了開發效率，但也鎖定了廠商。採用**條件註解隔離擴展、核心業務 SQL 遵循標準、型別/函數映射表標準化、CI/CD 整合遷移驗證**，可在享受 MySQL 便利的同時保留遷移彈性。現代 MySQL 8.0+ 已支援絕大多數標準功能 (CTE、窗函數、遞歸查詢、JSON、GIS)，差距大幅縮小，遷移成本隨版本演進持續降低。
