MySQL 數據庫與表操作核心命令大全：創建、查看、修改、刪除、複製

MySQL 數據庫與表操作核心命令大全：創建、查看、修改、刪除、複製

掌握 MySQL DDL (Data Definition Language) 核心命令是資料庫開發與運維的基礎技能。本文系統整理資料庫級與表級的創建、查看、修改、刪除、複製、臨時表等全方位操作命令，附帶條件創建、字符集指定、引擎選擇等生產級最佳實踐。

一、資料庫級操作

1. 查看資料庫列表

SHOW DATABASES;
-- 或
SELECT SCHEMA_NAME FROM information_schema.SCHEMATA;

2. 創建資料庫

-- 基礎創建
CREATE DATABASE db_test;

-- 指定字符集與排序規則（推薦）
CREATE DATABASE db_test 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

-- 條件創建（避免報錯）
CREATE DATABASE IF NOT EXISTS db_test 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

3. 使用/切換資料庫

USE db_test;
-- 後續無前綴的表操作默認針對此庫

4. 查看資料庫定義

SHOW CREATE DATABASE db_test;
-- 輸出包含字符集、排序規則

5. 修改資料庫編碼

ALTER DATABASE db_test 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;
-- 僅影響後續新建表，不轉換現有表數據

6. 刪除資料庫

DROP DATABASE db_test;
-- 條件刪除
DROP DATABASE IF EXISTS db_test;

二、表級操作

1. 創建表（完整語法示例）

CREATE TABLE tb_test (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
    firstname VARCHAR(25) NOT NULL COMMENT '名',
    lastname VARCHAR(25) NOT NULL COMMENT '姓',
    email VARCHAR(45) NOT NULL COMMENT '郵箱',
    phone VARCHAR(11) NOT NULL COMMENT '手機',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
    PRIMARY KEY (id),
    UNIQUE KEY uk_email (email),
    KEY idx_name (firstname, lastname)
) ENGINE=InnoDB 
  DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_general_ci 
  COMMENT='測試用戶表';

2. 跨庫創建表（無需 USE）

CREATE TABLE db_test.tb_test (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    firstname VARCHAR(25) NOT NULL,
    -- 其他欄位...
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3. 條件創建表（避免報錯）

CREATE TABLE IF NOT EXISTS db_test.tb_test (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    firstname VARCHAR(25) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 返回 "Query OK, 0 rows affected, 1 warning" 表示表已存在

4. 複製表結構與數據

-- 完整複製（結構+數據+索引）
CREATE TABLE tb_test2 SELECT * FROM db_test.tb_test;
-- 注意：不複製 PRIMARY KEY、AUTO_INCREMENT、FOREIGN KEY、TRIGGER、VIEW

-- 僅複製結構（無數據）
CREATE TABLE tb_test3 LIKE db_test.tb_test;
-- 完整複製表定義，含索引、約束、引擎、字符集

-- 指定列複製（結構+數據）
CREATE TABLE tb_test4 SELECT id, firstname, lastname, email FROM tb_test;

-- 異構複製：不同資料庫間
CREATE TABLE other_db.tb_copy SELECT * FROM db_test.tb_test;

5. 創建臨時表

-- 會話級別臨時表，斷開連接自動銷毀
CREATE TEMPORARY TABLE temp_emp 
SELECT firstname, lastname FROM tb_test;

-- 手動刪除
DROP TEMPORARY TABLE temp_emp;

6. 查看資料庫中所有表

SHOW TABLES;
-- 或指定庫
SHOW TABLES FROM db_test;

-- 顯示表類型
SHOW FULL TABLES;
-- Table_type: BASE TABLE / VIEW

7. 查看表結構

-- 方式 1：DESCRIBE (簡潔)
DESCRIBE tb_test;
-- 或簡寫
DESC tb_test;

-- 方式 2：SHOW COLUMNS (詳細)
SHOW COLUMNS FROM tb_test;
SHOW FULL COLUMNS FROM tb_test;  -- 含權限、註釋

-- 方式 3：SHOW CREATE TABLE (完整 DDL)
SHOW CREATE TABLE tb_test\G
-- 可直接複製執行重建表

8. 刪除表

DROP TABLE tb_test;
-- 多表同時刪除
DROP TABLE tb_test, tb_test2, tb_test3;
-- 條件刪除
DROP TABLE IF EXISTS tb_test;
-- 刪除臨時表
DROP TEMPORARY TABLE IF EXISTS temp_emp;

9. 修改表結構 (ALTER TABLE)

-- 9.1 添加欄位
ALTER TABLE tb_test ADD COLUMN address VARCHAR(100) COMMENT '地址';
-- 指定位置
ALTER TABLE tb_test ADD COLUMN address VARCHAR(100) FIRST;           -- 首列
ALTER TABLE tb_test ADD COLUMN address VARCHAR(100) AFTER email;     -- 在 email 後

-- 9.2 修改欄位定義（類型、長度、約束、註釋）
ALTER TABLE tb_test MODIFY COLUMN email VARCHAR(100) NOT NULL COMMENT '郵箱地址';
-- 或 CHANGE (可同時改名)
ALTER TABLE tb_test CHANGE COLUMN email email_addr VARCHAR(100) NOT NULL;

-- 9.3 刪除欄位
ALTER TABLE tb_test DROP COLUMN address;

-- 9.4 重命名表
RENAME TABLE tb_test TO tb_user;
-- 或
ALTER TABLE tb_test RENAME TO tb_user;

-- 9.5 修改表選項
ALTER TABLE tb_test ENGINE=InnoDB;
ALTER TABLE tb_test CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE tb_test COMMENT='用戶信息表';

-- 9.6 添加/刪除索引
ALTER TABLE tb_test ADD INDEX idx_phone (phone);
ALTER TABLE tb_test ADD UNIQUE KEY uk_phone (phone);
ALTER TABLE tb_test DROP INDEX idx_phone;

-- 9.7 添加/刪除外鍵（謹慎使用）
ALTER TABLE orders ADD CONSTRAINT fk_user 
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
ALTER TABLE orders DROP FOREIGN KEY fk_user;

三、進階操作與最佳實踐

1. 批次操作腳本模板

-- 禁用外鍵檢查（加速導入/結構變更）
SET FOREIGN_KEY_CHECKS = 0;

-- 批次執行多條 DDL
CREATE TABLE t1 (...);
CREATE TABLE t2 (...);
ALTER TABLE t1 ADD INDEX ...;
-- ...

-- 恢復外鍵檢查
SET FOREIGN_KEY_CHECKS = 1;

2. 線上 DDL 線上變更工具

大表結構變更建議使用：

• pt-online-schema-change (Percona Toolkit)：觸發器+影子表，無鎖變更
• gh-ost (GitHub)：Binlog 同步+影子表，無觸發器，更安全
• MySQL 8.0+ Instant DDL：部分操作（如添加欄位、索引）支持 Instant，無拷貝

3. 信息_schema 批量生成 DDL

-- 生成所有表添加 updated_at 欄位的 SQL
SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, 
  '` ADD COLUMN `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'db_test' 
  AND TABLE_TYPE = 'BASE TABLE'
  AND TABLE_NAME NOT LIKE 'temp_%';

四、常見錯誤與避坑

熟練掌握上述 DDL 命令，能覆蓋 95% 日常資料庫結構管理需求。生產環境變更請遵循：備份先行 → 測試驗證 → 低峰執行 → 監控觀察 → 回滾預案。大表結構變更必須使用線上變更工具，避免長時間鎖表影響業務。