MySQL 數據表操作全指南：創建、查看、修改、刪除、複製、臨時表

文件級存儲結構

• .frm : 表結構定義 (列、索引、約束)
• .ibd : InnoDB 獨立表空間數據+索引 (開啟 innodb_file_per_table)
• .MYD / .MYI : MyISAM 數據/索引文件

一、查看表信息

-- 列出庫中所有表
SHOW TABLES [FROM db_name] [LIKE 'pattern'];

-- 表結構詳情 (推薦)
SHOW CREATE TABLE tbl_name;
DESCRIBE tbl_name;  -- 或 DESC tbl_name
SHOW COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern'];
SHOW FULL COLUMNS FROM tbl_name;  -- 含權限、註釋

-- 索引信息
SHOW INDEX FROM tbl_name [FROM db_name];
SHOW KEYS FROM tbl_name;  -- 同義

-- 表狀態統計 (行數、大小、引擎、自增值、碎片率...)
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern'];
-- 關鍵列: Engine, Row_format, Rows, Avg_row_length, Data_length, Index_length, Auto_increment, Create_time, Update_time, Check_time, Collation, Checksum, Create_options, Comment

二、mysqlshow 命令行工具 (無需進入交互模式)

# 列出所有庫
mysqlshow -u root -p

# 列出庫中表
mysqlshow -u root -p db_name

# 列出表結構
mysqlshow -u root -p db_name tbl_name

# 列出表索引
mysqlshow -u root -p -k db_name tbl_name

# 顯示表狀態
mysqlshow -u root -p --status db_name

三、創建表 (CREATE TABLE)

基礎語法

CREATE TABLE [IF NOT EXISTS] tbl_name (
    col_name data_type [NOT NULL | NULL] [DEFAULT default_value] 
    [AUTO_INCREMENT] [PRIMARY KEY] [COMMENT 'comment'],
    ...
    [PRIMARY KEY (col_list)],
    [INDEX idx_name (col_list)],
    [UNIQUE uk_name (col_list)],
    [FOREIGN KEY (col) REFERENCES tbl(col) ON DELETE CASCADE]
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

實戰示例：訪客留言表

CREATE DATABASE mytest;
USE mytest;

CREATE TABLE guestbook (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    visitor VARCHAR(40) NOT NULL DEFAULT '',
    comments TEXT,
    entrydate DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

隱式列定義變更 (MySQL 自動優化)

• VARCHAR(3) → 自動轉 CHAR(3) (長度 < 4)
• 表含可變長列 (VARCHAR/TEXT/BLOB) → 所有 CHAR(>3) 自動轉 VARCHAR (節省空間、加速操作)
• TIMESTAMP 顯示尺寸強制為偶數 (2-14)，奇數向上取偶
• TIMESTAMP 列插入 NULL → 自動賦當前時間 (非標準 NULL 語義)

四、利用 SELECT 結果創建表 (CREATE TABLE ... SELECT)

-- 復制表結構+數據
CREATE TABLE new_tbl SELECT * FROM old_tbl;

-- 復制結構不復制數據 (WHERE FALSE)
CREATE TABLE empty_tbl SELECT * FROM old_tbl WHERE 0;

-- 指定主鍵+索引並復制部分列
CREATE TABLE test (
    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    KEY(b)
) SELECT b, c FROM test2;
-- 結果表含 a,b,c 三列，b 有索引

-- 臨時表複製 (會話結束自動刪除，不影響原表)
CREATE TEMPORARY TABLE tmp_tbl SELECT * FROM orig_tbl;
-- 用於測試修改、腳本隔離

注意**：CREATE TABLE ... SELECT **不複製外鍵、觸發器、分區、某些屬性**。生產環境備份/遷移請用 mysqldump 或 pt-osc。

五、修改表結構 (ALTER TABLE)

-- 增加列
ALTER TABLE tbl_name ADD col_name data_type [FIRST | AFTER col_name];

-- 刪除列
ALTER TABLE tbl_name DROP COLUMN col_name;

-- 修改列類型/屬性 (不改名)
ALTER TABLE tbl_name MODIFY col_name new_data_type;

-- 修改列名+類型 (CHANGE 必須給新名，即使不改也要寫)
ALTER TABLE tbl_name CHANGE old_col_name new_col_name new_data_type;

-- 重命名列 (類型不變)
ALTER TABLE tbl_name CHANGE old_col_name new_col_name old_data_type;

-- 重命名表
ALTER TABLE old_tbl RENAME TO new_tbl;
-- 或跨庫移動
ALTER TABLE db1.tbl RENAME TO db2.tbl;

-- 修改表引擎 (大表慎用！鎖表重建，生產用 pt-osc)
ALTER TABLE tbl_name ENGINE = InnoDB;

-- 修改字符集 (僅修改默認，不轉換現有列數據)
ALTER TABLE tbl_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
-- 轉換表及所有列數據 (極慢，大表禁用)
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

六、刪除表 (DROP TABLE)

DROP TABLE [IF EXISTS] tbl_name [, tbl_name2 ...];
-- IF EXISTS 避免不存在報錯
-- 同時刪除多表: DROP TABLE t1, t2, t3;
-- ⚠️ 所有數據、結構、索引、觸發器、權限永久刪除，無回收站，極度謹慎！

七、TRUNCATE vs DELETE 清空表對比