MySQL 簡單語法速查：建表、自增、修改、刪除、查看、引擎選擇

MySQL 簡單語法速查：建表、自增、修改、刪除、查看、引擎選擇

針對 MySQL 常用 DDL 操作，提供最小可用語法範例，涵蓋預設值、自增、重命名、類型變更、增刪欄位、外鍵/主鍵管理、引擎指定、多表批次刪除。

1. 建表基礎

-- 預設值
CREATE TABLE emp (
  id INT DEFAULT 12
);

-- 自增主鍵 (InnoDB 必須是索引列)
CREATE TABLE emp (
  id INT PRIMARY KEY AUTO_INCREMENT
) AUTO_INCREMENT = 100;  -- 種子值

-- 指定引擎
CREATE TABLE test (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  data0 VARCHAR(20),
  data1 VARCHAR(20),
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- MyISAM 範例
CREATE TABLE test_myisam (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(10) NOT NULL
) ENGINE=MyISAM AUTO_INCREMENT=100;

2. 查看表結構

DESC emp;
SHOW CREATE TABLE emp\G

3. 修改表名

ALTER TABLE emp RENAME emp2;

4. 修改欄位類型

ALTER TABLE emp2 MODIFY id BIGINT;

5. 重命名欄位 (MySQL 必須帶類型)

-- 只改名
ALTER TABLE emp2 CHANGE id id BIGINT;
-- 改名 + 改類型
ALTER TABLE emp2 CHANGE id emp_id BIGINT UNSIGNED;

6. 新增/刪除欄位

ALTER TABLE emp2 ADD name VARCHAR(200) NULL;
ALTER TABLE emp2 DROP name;

7. 刪除約束

-- 外鍵
ALTER TABLE emp2 DROP FOREIGN KEY fk_emp_dept;
-- 主鍵
ALTER TABLE emp2 DROP PRIMARY KEY;

8. 刪除表

DROP TABLE emp2;
DROP TABLE IF EXISTS emp1, emp2;  -- 批次

9. 自增變數查看與重置

-- 查看下一個自增值
SHOW TABLE STATUS LIKE 'emp'\G
-- 或
SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA='db' AND TABLE_NAME='emp';

-- 重置自增值 (表必須為空或新值 > 現有最大值)
ALTER TABLE emp AUTO_INCREMENT = 1000;

10. 取得最後插入 ID 的四種方式

-- 1. 特定表
SELECT MAX(id) FROM person;

-- 2. 函數 (Session 級)
SELECT LAST_INSERT_ID();

-- 3. 系統變數 (Session 級，連線未斷)
SELECT @@identity;

-- 4. 表狀態
SHOW TABLE STATUS LIKE 'person'\G

11. InnoDB vs MyISAM 自增重啟行為差異

結語：DDL 語法看似簡單，但「自增種子/步長、引擎差異、CHANGE 必須帶類型、批次刪表、線上安全變更」等細節直接關係生產穩定。建議將常用片段封裝為遷移腳本 (Flyway/Liquibase)，實現版本化、可回滾、可審計的結構變更流程。