萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL DML 深度解析:INSERT、REPLACE、UPDATE、DELETE、TRUNCATE 語法與陷阱

INSERT 與 REPLACE:新增資料的兩種語義

INSERT 兩種語法

-- 標準 SQL:欄位與值分離
INSERT INTO users (id, name, age) VALUES (123, '姚明', 25);

-- MySQL 擴充:SET 語法,欄位值成對
INSERT INTO users SET id = 123, name = '姚明', age = 25;
  • 自增/預設欄位可省略。
  • INSERT INTO users () VALUES (); 全用預設值。
  • INSERT INTO users VALUES (1,'A',20); 必須匹配**所有欄位順序與數量**。

批次多行 INSERT(MySQL 專屬,極速)

INSERT INTO users (name, age) VALUES 
('姚明', 25), ('比爾蓋茨', 50), ('火星人', 600);

每組值獨立括號,逗號分隔。**錯誤寫法**:VALUES (1,2,3,4,5) 會被視為單行 5 欄位。

REPLACE:UPSERT 語義(主鍵/唯一鍵衝突則刪舊插新)

REPLACE INTO users (id, name, age) VALUES (123, '趙本山', 50);
-- 批次
REPLACE INTO users (id, name, age) VALUES (123,'A',10), (124,'B',20);
-- SET 語法
REPLACE INTO users SET id = 123, name = '趙本山', age = 50;
  • 表**必須有主鍵或唯一索引**,且該欄位 NOT NULL,否則退化為 INSERT。
  • 返回影響行數:1=純插入,2=刪1插1,>2=多唯一鍵衝突刪多插1。
  • 原子操作:隱性事務,無需額外 DELETE+INSERT 事務包裹。

UPDATE:修改現有資料

UPDATE users SET age = 24, name = 'Mike' WHERE id = 123;
  • 無 WHERE 將更新**全表**——極度危險。
  • 型別不匹配:數值超範圍截取邊界值、字串超長截斷、非 NULL 欄位設 NULL 變預設值(0/空串)。
  • 不更新情況:WHERE 無匹配行、新值等於舊值。
  • 返回匹配行數(預設)或實際變更行數(API 可選)。
  • TIMESTAMP 欄位若未顯式賦值,自動更新為當前時間。

DELETE vs TRUNCATE TABLE

特性DELETETRUNCATE TABLE
類型DMLDDL
WHERE 支援是否(清空全表)
觸發器觸發 FOR EACH ROW不觸發
事務/回滾支援不支援(隱性 COMMIT)
自增 ID保留歸零
外鍵約束受限制有 FK 則失敗
速度慢(逐行、記 Binlog)極快(重建表)
空間回收高水位線不變釋放/歸零

選型決策樹

  • 刪除**部分**資料 → DELETE WHERE ...(大量分批 Commit)。
  • 清空**全表**、無 FK、無需回滾 → TRUNCATE TABLE。
  • 清空全表、有 FK/需觸發器/需回滾 → DELETE FROM table(慎用)。
  • 表結構不再需要 → DROP TABLE。
copyright © 萬盛學電腦網 all rights reserved