MySQL 與 SQL Server 語法差異完整對照：DDL、自增、查詢、函數實戰筆記

MySQL 與 SQL Server 語法差異完整對照：DDL、自增、查詢、函數實戰筆記

本文針對同時維護 MySQL 與 SQL Server 的開發者，系統整理兩者在建表、自增欄位、查詢語法、函數、資料型別顯示寬度等方面的核心差異，並附上實測驗證腳本，便於快速切換與避坑。

一、DDL 基礎語法差異

1. DEFAULT 約束

2. 自增欄位

-- SQL Server
CREATE TABLE emp ( id INT IDENTITY(1,1) );

-- MySQL：自增欄位必須為索引（主鍵或唯一鍵）
CREATE TABLE emp ( id INT PRIMARY KEY AUTO_INCREMENT ) AUTO_INCREMENT = 100;

關鍵差異：

• MySQL 自增欄位必須建立索引（主鍵/唯一/普通索引皆可）。
• 種子值用表選項 AUTO_INCREMENT = N 設定，非欄位屬性。
• 步長由系統變數控制（auto_increment_increment、auto_increment_offset），分全域/會話兩級，無法在表級別指定步長（SQL Server 可用 IDENTITY(1,2)）。

3. 自增值重啟後的行為差異（重要）

此為 InnoDB 早期版本已知 Bug (#199)，8.0 已修復。

4. 取得當前自增值的四種方法

1. SELECT MAX(id) FROM person;                    -- 特定表，最直觀
2. SELECT LAST_INSERT_ID();                       -- 會話級，跨表也可用
3. SELECT @@IDENTITY;                             -- 會話級，最近一次 INSERT 的自增值
4. SHOW TABLE STATUS LIKE 'person';               -- 查看 Auto_increment 欄位

建議：特定表用 SHOW TABLE STATUS 或 MAX(id)；應用層回寫 ID 用 LAST_INSERT_ID()（不受並發影響）。

5. 查看表結構

-- SQL Server
EXEC sp_help 'emp';

-- MySQL
DESC emp;          -- 或 SHOW COLUMNS FROM emp;

6. 修改表名

-- SQL Server
EXEC sp_rename 'emp', 'emp2';

-- MySQL
ALTER TABLE emp RENAME emp2;

7. 修改欄位型別

-- SQL Server
ALTER TABLE emp2 ALTER COLUMN id BIGINT;

-- MySQL
ALTER TABLE emp2 MODIFY id BIGINT;

8. 修改欄位名（MySQL 需同時指定型別）

-- SQL Server
EXEC sp_rename 'emp2.id', 'iid', 'COLUMN';

-- MySQL（CHANGE 同時支援改名與改型別）
ALTER TABLE emp2 CHANGE id iid BIGINT;   -- 改名+改型別
ALTER TABLE emp2 CHANGE id id BIGINT;    -- 僅改型別（新舊名相同）

9. 新增欄位（MySQL 支援位置指定）

-- 兩者基本語法相同
ALTER TABLE emp2 ADD name VARCHAR(200) NULL;

-- MySQL 獨有：指定位置
ALTER TABLE emp2 ADD name VARCHAR(200) NULL FIRST;
ALTER TABLE emp2 ADD name VARCHAR(200) NULL AFTER id;

10. 刪除欄位

-- SQL Server
ALTER TABLE emp2 DROP COLUMN name;

-- MySQL（無需 COLUMN 關鍵字）
ALTER TABLE emp2 DROP name;

11. 刪除約束（外鍵/主鍵語法不同）

-- SQL Server：統一語法
ALTER TABLE emp2 DROP CONSTRAINT fk_emp_dept;

-- MySQL：依約束類型分別指定
ALTER TABLE emp2 DROP FOREIGN KEY fk_emp_dept;
ALTER TABLE emp2 DROP PRIMARY KEY;

12. 刪除表（MySQL 支援多表與 IF EXISTS）

-- SQL Server：需逐表判斷
IF OBJECT_ID('dbo.emp2') IS NOT NULL DROP TABLE dbo.emp2;

-- MySQL：單條語句多表、自帶存在性檢查
DROP TABLE IF EXISTS emp1, emp2;

二、資料型別顯示寬度

MySQL 整數型別可指定顯示寬度（如 INT(11)），僅影響顯示格式（不足補空格），不限制儲存範圍、不影響儲存空間。SQL Server 不支援此語法會報錯。

CREATE TABLE tb_emp ( id BIGINT(1) );  -- 合法，仍佔 8 位元組，最大值仍為 BIGINT 上限

三、函數差異精選（MySQL 有、SQL Server 無）

• MOD(x,y)：支援小數取模
• TRUNCATE(x,d)：直接截斷不四捨五入（ROUND 會四捨五入）
• HEX()/UNHEX()：字串與十六進制互轉
• CHAR_LENGTH()：字元數（多位元組字元算 1）
• CONCAT_WS(sep, ...)：帶分隔符連接，自動跳過 NULL
• INSERT(str,pos,len,newstr)：替換子串（對應 SQL Server STUFF）
• LPAD/RPAD：左右填充至指定長度
• TRIM([BOTH|LEADING|TRAILING] remstr FROM str)：單指令去除兩側指定字串
• STRCMP(s1,s2)：字串比較回傳 -1/0/1
• LOCATE/POSITION/INSTR：子串位置（對應 CHARINDEX）
• ELT(n, ...)/FIELD(str, ...)/FIND_IN_SET/MAKE_SET：集合操作函數族
• MONTHNAME()/QUARTER()/UTC_DATE()/UTC_TIME()：日期時間豐富函數
• EXTRACT(type FROM date)：對應 DATEPART
• TIME_TO_SEC()/SEC_TO_TIME()：時間↔秒互轉
• IF(expr, v1, v2)/IFNULL(v1,v2)/CASE：流程控制函數（語法與 SQL Server 不同）
• PASSWORD()/MD5()/ENCODE()/DECODE()：加密函數族
• FORMAT(x,n)：數字格式化含千分位
• CONV(n, from_base, to_base)：進制轉換
• INET_ATON()/INET_NTOA()：IPv4 地址↔整數
• GET_LOCK()/RELEASE_LOCK()：應用層命名鎖
• BENCHMARK(count, expr)：效能基準測試
• UUID()：生成 UUID

四、總結與避坑清單

1. 自增欄位：MySQL 必須加索引、種子用表選項、步長只能全域/會話設定。
2. 重啟後自增值：InnoDB 5.6 及舊版會回退，8.0 已修復；MyISAM 永不回退。
3. DDL：MySQL CHANGE 改名必須帶型別、DROP 不需 COLUMN、支援 DROP TABLE IF EXISTS a,b。
4. 顯示寬度 INT(11) 僅顯示用，勿誤以為限制長度。
5. 函數庫：MySQL 內建函數極豐富，移植 SQL Server 代碼時需逐一對應或改寫。
6. 字元集：統一用 utf8mb4，避免 Emoji 存儲截斷。