您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 基礎 CRUD 操作完整指南：INSERT、UPDATE、DELETE 實戰與效能技巧

編輯：MySQL 綜合教學

資料準備

CREATE DATABASE crud_demo;
USE crud_demo;
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT 插入資料

單行插入

INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');
-- 指定列順序、省略自增/預設列

多行批次插入（高效）

INSERT INTO users (username, email) VALUES
('bob', 'bob@example.com'),
('charlie', 'charlie@example.com'),
('david', 'david@example.com');
-- 單條語句插入多行，減少網路往返與解析開銷

INSERT ... SELECT 複製表資料

INSERT INTO users_archive (username, email, status, created_at)
SELECT username, email, status, created_at FROM users WHERE status = 0;

ON DUPLICATE KEY UPDATE 覆蓋寫入

INSERT INTO users (username, email, status) VALUES ('alice', 'alice_new@example.com', 1)
ON DUPLICATE KEY UPDATE email = VALUES(email), status = VALUES(status), updated_at = CURRENT_TIMESTAMP;
-- 唯一鍵衝突時轉為更新，實現 UPSERT

INSERT IGNORE 靜默忽略衝突

INSERT IGNORE INTO users (username, email) VALUES ('alice', 'ignore@example.com');
-- 衝突不報錯、不更新、不計入 affected_rows

UPDATE 更新資料

基本更新

UPDATE users SET status = 0 WHERE username = 'alice';
-- 必須加 WHERE，防誤更新全表

多表關聯更新

UPDATE users u
INNER JOIN orders o ON u.id = o.user_id
SET u.last_order_at = o.created_at
WHERE o.status = 'completed';

分批更新大表（避免長事務）

-- 假設主鍵連續
UPDATE users SET status = 2 WHERE id BETWEEN 1 AND 10000;
-- 循環執行，每批 5000-10000 行，提交間隔 sleep(0.1)

DELETE 刪除資料

條件刪除

DELETE FROM users WHERE status = 0 AND created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

TRUNCATE 快速清空全表

TRUNCATE TABLE users;
-- DDL 操作，重建表，釋放空間，自增歸零，不可回滾，不觸發觸發器

多表關聯刪除

DELETE u FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;  -- 刪除無訂單用戶

效能與安全最佳實踐

場景	建議
大量寫入	批次 INSERT、關閉二進制日誌 `SET SESSION sql_log_bin=0`、使用 `LOAD DATA INFILE`
大量更新/刪除	分批處理、低峰期、備份先行、監控 `Innodb_row_lock_time`
防誤操作	生產環境先 `SELECT` 驗證影響行、使用事務包裹、工單審批流程
索引維護	避免更新主鍵/唯一鍵/外鍵列；大批量操作後 `ANALYZE TABLE` 更新統計