MySQL/MariaDB 基礎 DDL 操作速查：建庫建表、增刪改列、修改屬性、註釋管理

MySQL/MariaDB 基礎 DDL 操作速查：建庫建表、增刪改列、修改屬性、註釋管理

DDL（數據定義語言）是數據庫結構變更的核心，本文彙總 MySQL/MariaDB 最常用的 DDL 語句模板，含創建、修改、刪除庫表列、調整屬性順序、管理註釋，適合日常開發運維快速查閱。

一、數據庫操作

-- 創建數據庫（指定字符集校對規則）
CREATE DATABASE IF NOT EXISTS verifyidear CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 刪除數據庫
DROP DATABASE IF EXISTS verifyidear;

-- 查看數據庫創建語句
SHOW CREATE DATABASE verifyidear;

二、表操作

1. 創建表

CREATE TABLE IF NOT EXISTS verifyidear.MyTable (
    ID BIGINT(8) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    Updatetime DATETIME,
    name VARCHAR(128)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COMMENT='測試用表';

2. 切換數據庫

USE verifyidear;

3. 增加列

-- 末尾增加
ALTER TABLE MyTable ADD COLUMN IF NOT EXISTS adcolumn VARCHAR(64) DEFAULT '' COMMENT '新增列';

-- 指定位置增加（AFTER 現有列 / FIRST 首列）
ALTER TABLE MyTable ADD COLUMN new_col INT DEFAULT 0 AFTER name;

4. 刪除列

ALTER TABLE MyTable DROP COLUMN IF EXISTS adcolumn;

5. 修改表註釋

ALTER TABLE MyTable COMMENT = '更新後的表註釋';

三、列屬性修改（核心高頻操作）

1. CHANGE COLUMN：可改名、改類型、改屬性、改位置

-- 完整語法：舊列名 新列名 類型 屬性 位置
ALTER TABLE `MyTable` 
CHANGE COLUMN `name` `username` VARCHAR(128) 
DEFAULT NULL COLLATE 'utf8mb4_general_ci' 
COMMENT '用戶名' AFTER `thTime`;

2. MODIFY COLUMN：不改名，只改類型/屬性/位置

ALTER TABLE MyTable 
MODIFY COLUMN username VARCHAR(128) 
DEFAULT NULL COLLATE 'utf8mb4_general_ci' 
COMMENT '用戶名' AFTER thtime;

3. 修改默認值

ALTER TABLE MyTable ALTER COLUMN username SET DEFAULT 'guest';

4. 刪除默認值

ALTER TABLE MyTable ALTER COLUMN username DROP DEFAULT;

5. 修改自增起始值

ALTER TABLE MyTable AUTO_INCREMENT = 10000;

四、索引操作

-- 增加普通索引
ALTER TABLE MyTable ADD INDEX idx_name (username);

-- 增加唯一索引
ALTER TABLE MyTable ADD UNIQUE INDEX uk_email (email);

-- 增加組合索引（最左前綴原則）
ALTER TABLE MyTable ADD INDEX idx_dept_status (dept_id, status);

-- 刪除索引
ALTER TABLE MyTable DROP INDEX idx_name;

-- 查看索引
SHOW INDEX FROM MyTable;

五、外鍵操作（InnoDB 專用）

-- 添加外鍵
ALTER TABLE orders 
ADD CONSTRAINT fk_orders_user 
FOREIGN KEY (user_id) REFERENCES users(id) 
ON DELETE RESTRICT ON UPDATE CASCADE;

-- 刪除外鍵
ALTER TABLE orders DROP FOREIGN KEY fk_orders_user;

六、分區操作（5.1+）

-- RANGE 分區
ALTER TABLE logs PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 刪除分區（數據同步刪除）
ALTER TABLE logs DROP PARTITION p2023;

-- 截斷分區（保留結構，清空數據，極快）
ALTER TABLE logs TRUNCATE PARTITION p2023;

七、查看表結構

DESC MyTable;
SHOW CREATE TABLE MyTable\G
SHOW COLUMNS FROM MyTable;

八、最佳實踐提醒

1. 大表 DDL 使用在線工具：pt-online-schema-change、gh-ost 避免長時間鎖表。
2. 字符集統一 utf8mb4：建庫建表顯式指定，避免隱式轉換亂碼。
3. 註釋即文檔：表註釋、列註釋嚴格維護，SHOW CREATE TABLE 即可見業務含義。
4. 主鍵無業務含義：BIGINT UNSIGNED AUTO_INCREMENT，業務唯一鍵用 UNIQUE INDEX。
5. 外鍵慎用：高併發下鎖爭用嚴重，應用層維護引用完整性更靈活。