MySQL 核心操作進階實戰：庫表結構管理、約束設計、數據操作與備份恢復全攻略

一、 數據庫 (Schema) 級操作

-- 創建 (指定字符集/校對規則)
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 查看
SHOW DATABASES;
SHOW CREATE DATABASE mydb;

-- 修改屬性 (不可改名)
ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 刪除
DROP DATABASE mydb;

-- 邏輯備份 (mysqldump)
mysqldump -uroot -p --single-transaction --master-data=2 --routines --triggers mydb > mydb_$(date +%F).sql

-- 邏輯恢復
mysql -uroot -p mydb < mydb_20231001.sql
-- 或 客戶端內
SOURCE /path/mydb_20231001.sql;

二、 表結構精細管理

1. 規範建表模板

CREATE TABLE orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
    order_no VARCHAR(32) NOT NULL COMMENT '訂單號',
    user_id BIGINT UNSIGNED NOT NULL COMMENT '用戶ID',
    amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '金額',
    status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '狀態: 0待支付 1已支付 2已取消',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
    PRIMARY KEY (id),
    UNIQUE KEY uk_order_no (order_no),
    KEY idx_user_id (user_id),
    KEY idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='訂單表';

設計鐵律： 1) 必有主鍵 (推薦 BIGINT UNSIGNED AUTO_INCREMENT) 2) 業務唯一性加 UK 3) 查詢高頻列加索引 4) 所有表/列加 COMMENT 5) 時間戳用 DATETIME 非 TIMESTAMP (避免 2038 年問題) 6) 金額用 DECIMAL 7) 狀態用 TINYINT + 字典註釋。

2. 表結構變更 (DDL) 實戰

-- 添加索引
ALTER TABLE orders ADD INDEX idx_status (status);
ALTER TABLE orders ADD UNIQUE INDEX uk_user_order (user_id, order_no);  -- 聯合唯一

-- 刪除索引
ALTER TABLE orders DROP INDEX idx_status;

-- 外鍵 (謹慎使用，高併發性能殺手，建議應用層保證)
ALTER TABLE order_items ADD CONSTRAINT fk_order_id FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE;

-- 刪除外鍵
ALTER TABLE order_items DROP FOREIGN KEY fk_order_id;

-- 查看索引
SHOW INDEX FROM orders\G;

三、 數據操作 (DML) 核心模式

https://doc.mysql.cn/
MySQL 官方文檔 (英文權威)：dev.mysql.com/doc/refman/8.0/en/
MariaDB 知識庫：mariadb.com/kb/en/