您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 數據庫與表操作命令全攻略：從新手到精通

編輯：MySQL 資料庫相關知識

前言

熟練掌握 MySQL 客戶端常用命令是 DBA 與開發者的基本功。本文系統整理庫/表級 DDL、DML、元數據查詢、導入導出等高頻操作，附帶實戰參數與避坑提示。

一、數據庫級操作

-- 1. 查看所有庫
SHOW DATABASES;

-- 2. 創建庫 (指定字符集/校對)
CREATE DATABASE IF NOT EXISTS `app_db` 
    DEFAULT CHARACTER SET utf8mb4 
    COLLATE utf8mb4_unicode_ci;

-- 3. 查看庫創建語句
SHOW CREATE DATABASE `app_db`;

-- 4. 切換當前庫
USE `app_db`;

-- 5. 修改庫字符集
ALTER DATABASE `app_db` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 6. 刪除庫 (慎用！)
DROP DATABASE IF EXISTS `app_db`;

二、表結構操作 (DDL)

1. 創建表 — 規範模板

CREATE TABLE IF NOT EXISTS `user` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
    `username` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '用戶名',
    `email` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '郵箱',
    `status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1啟用 0禁用',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_username` (`username`),
    KEY `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用戶表';

2. 條件創建 (避免報錯)

CREATE TABLE IF NOT EXISTS `log_2024` (...) ENGINE=InnoDB;

3. 複製表結構/數據

-- 僅結構
CREATE TABLE `user_archive` LIKE `user`;

-- 結構 + 數據
CREATE TABLE `user_bak` AS SELECT * FROM `user`;

-- 指定列複製
CREATE TABLE `user_simple` AS SELECT id, username FROM `user`;

4. 臨時表 (會話級自動清理)

CREATE TEMPORARY TABLE `tmp_active_user` 
SELECT id, username FROM `user` WHERE status=1 AND last_login > DATE_SUB(NOW(), INTERVAL 7 DAY);

5. 查看表信息

SHOW TABLES;                    -- 當前庫所有表
SHOW TABLES FROM `other_db`;    -- 指定庫表列表
DESC `user`;                    -- 結構簡視圖
SHOW CREATE TABLE `user`;       -- 完整建表語句 (含索引/外鍵/分區)
SHOW COLUMNS FROM `user`;       -- 列詳情
SHOW TABLE STATUS LIKE 'user';  -- 統計信息：行數、大小、碎片、自增值

6. 修改表結構 (ALTER TABLE)

-- 增加列
ALTER TABLE `user` ADD COLUMN `phone` VARCHAR(16) NOT NULL DEFAULT '' AFTER `email`;
ALTER TABLE `user` ADD COLUMN `nickname` VARCHAR(32) NOT NULL DEFAULT '' FIRST;

-- 修改列定義 (類型/默認值/註釋)
ALTER TABLE `user` MODIFY COLUMN `status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '狀態';

-- 重命名列 (需重新指定完整定義)
ALTER TABLE `user` CHANGE COLUMN `phone` `mobile` VARCHAR(16) NOT NULL DEFAULT '';

-- 刪除列
ALTER TABLE `user` DROP COLUMN `mobile`;

-- 索引操作
ALTER TABLE `user` ADD INDEX `idx_status` (`status`);
ALTER TABLE `user` ADD UNIQUE KEY `uk_mobile` (`mobile`);
ALTER TABLE `user` DROP INDEX `idx_status`;

-- 修改表選項
ALTER TABLE `user` ENGINE=InnoDB;           -- 轉換引擎 (重建表)
ALTER TABLE `user` AUTO_INCREMENT = 10000;  -- 設自增起始值
ALTER TABLE `user` COMMENT = '用戶核心表';   -- 修改表註釋

-- 重命名表
RENAME TABLE `user` TO `user_v1`, `user_new` TO `user`;  -- 原子交換，支持跨庫

7. 刪除/清空表

DROP TABLE IF EXISTS `user_old`;      -- 刪除表結構+數據+索引
TRUNCATE TABLE `user_log`;            -- 清空數據，重置自增值，不記錄 Binlog (不可回滾)
DELETE FROM `user_log`;               -- 逐行刪除，記錄 Binlog，可回滾，不重置自增

三、數據操作 (DML) 進階

插入變體

-- REPLACE: 主鍵/唯一鍵衝突時 DELETE + INSERT (慎用，觸發器行為異常)
REPLACE INTO `user` (id, username) VALUES (1, 'new_name');

-- INSERT IGNORE: 衝突靜默忽略，不報錯
INSERT IGNORE INTO `user` (username) VALUES ('exist_user');

-- ON DUPLICATE KEY UPDATE: 衝突時更新 (推薦，原子操作)
INSERT INTO `user` (username, email, login_count) 
VALUES ('zhangsan', 'zs@ex.com', 1)
ON DUPLICATE KEY UPDATE 
    login_count = login_count + 1,
    updated_at = CURRENT_TIMESTAMP;

批量操作優化

批量 INSERT 每次 500-1000 行，單條 SQL 長度 < 4MB (max_allowed_packet)
大量刪除分批 DELETE ... LIMIT 1000 避免長事務鎖表
大量更新同理分批，配合索引覆蓋

四、導入導出實戰

mysqldump 邏輯備份

# 全庫 (含結構+數據+例程+觸發器+事件+GTID位點)
mysqldump -uroot -p --single-transaction --routines --triggers --events --master-data=2 \
    --databases db1 db2 > full_$(date +%F_%H%M).sql

# 單表
mysqldump -uroot -p --single-transaction db1 user > user.sql

# 僅結構
mysqldump -uroot -p -d db1 > schema.sql

# 僅數據 (無 CREATE TABLE)
mysqldump -uroot -p -t db1 user > user_data.sql

# WHERE 條件導出子集
mysqldump -uroot -p --where="created_at > '2024-01-01'" db1 user > user_2024.sql

導入還原

# 方式一：客戶端 source (需先進入 mysql)
mysql> USE db1;
mysql> SOURCE /path/to/backup.sql;

# 方式二：管道導入 (非交互，適合腳本)
mysql -uroot -p db1 < /path/to/backup.sql

# 方式三：壓縮文件直接導入
zcat backup.sql.gz | mysql -uroot -p db1

CSV 導入導出

-- 導出 CSV
SELECT * FROM `user` INTO OUTFILE '/tmp/user.csv' 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

-- 導入 CSV (需 secure_file_priv 目錄權限)
LOAD DATA INFILE '/tmp/user.csv' INTO TABLE `user`
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES;  -- 跳過表頭

五、視圖、存儲過程、觸發器速查

-- 視圖
CREATE OR REPLACE VIEW `v_user_active` AS 
SELECT id, username, email FROM `user` WHERE status=1;

-- 存儲過程
DELIMITER //
CREATE PROCEDURE `sp_user_login`(IN p_username VARCHAR(32), OUT p_id BIGINT)
BEGIN
    SELECT id INTO p_id FROM `user` WHERE username = p_username AND status=1;
END //
DELIMITER ;

-- 觸發器
CREATE TRIGGER `trg_user_before_insert` BEFORE INSERT ON `user`
FOR EACH ROW
BEGIN
    IF NEW.username = '' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '用戶名不能為空';
    END IF;
END;

六、權限管理

-- 創建用戶
CREATE USER 'app_rw'@'10.0.%' IDENTIFIED BY 'StrongPass#2024';

-- 授權
GRANT SELECT, INSERT, UPDATE, DELETE ON `app_db`.* TO 'app_rw'@'10.0.%';
GRANT SELECT ON `app_db`.* TO 'app_ro'@'10.0.%';

-- 查看權限
SHOW GRANTS FOR 'app_rw'@'10.0.%';

-- 回收權限
REVOKE DELETE ON `app_db`.* FROM 'app_rw'@'10.0.%';
FLUSH PRIVILEGES;

七、維護與監控命令

-- 表維護
ANALYZE TABLE `user`;       -- 更新統計信息 (InnoDB 抽樣)
OPTIMIZE TABLE `user`;      -- 整理碎片 (重建表)
CHECK TABLE `user`;         -- 檢查損壞
REPAIR TABLE `user`;        -- 修復 (MyISAM)

-- 狀態查看
SHOW PROCESSLIST;                    -- 當前線程
SHOW ENGINE INNODB STATUS\G;         -- InnoDB 詳細狀態
SHOW GLOBAL STATUS LIKE 'Innodb_%';  -- 關鍵指標
SHOW VARIABLES LIKE 'max_connections';

將上述命令內化為肌肉記憶，配合 \G 垂直顯示、tee /tmp/mysql.log 記錄會話，能大幅提升線上排障與變更操作效率。