MySQL 學習筆記：資料增刪改（INSERT/UPDATE/DELETE）語法、事務控制與最佳實踐

MySQL DML 三大核心指令系統整理：語法格式、插入原則、更新條件、刪除陷阱、事務手動控制流程。

一、INSERT 插入資料

語法

INSERT INTO 表名 [欄位列表] VALUES (值列表);

三大原則

1. 值類型與欄位定義一致
2. 值長度/範圍在欄位允許內
3. 值位置與欄位列表順序一一對應

範例

CREATE TABLE test22 (id INT, name VARCHAR(32));
INSERT INTO test22 (id, name) VALUES (3, 'huangbiao');
INSERT INTO test22 (name, id) VALUES ('huangbiao2', 5);  -- 順序可調整
INSERT INTO test22 (name, id) VALUES ('', 51);          -- 空字串 ≠ NULL
INSERT INTO test22 (name, id) VALUES (NULL, 555);       -- 顯式 NULL
INSERT INTO test22 (id) VALUES (15);                    -- 部分欄位，其餘用 DEFAULT/NULL

二、UPDATE 更新資料

語法

UPDATE 表名 SET 欄位=表達式 [, ...] [WHERE 條件];

無 WHERE = 全表更新，**極度危險**，生產環境嚴禁裸跑。

範例

CREATE TABLE employee (
  id INT, name VARCHAR(20), sex BIT, birthday DATE,
  salary FLOAT, entry_date DATE, resume TEXT
);
INSERT INTO employee VALUES (1,'aaa',0,'1977-11-11',56.8,NOW(),'hello');
INSERT INTO employee VALUES (2,'bbb',0,'1977-11-11',57.8,NOW(),'hello');
INSERT INTO employee VALUES (3,'ccc',0,'1977-11-11',56.3,NOW(),'hello');

-- 全表更新（示意，勿執行）
UPDATE employee SET salary = 2000;

-- 條件更新
UPDATE employee SET salary = 3000 WHERE name = 'zs';
UPDATE employee SET salary = salary + 100 WHERE name = 'wu';

三、DELETE 刪除資料

語法

DELETE FROM 表名 [WHERE 條件];

五大原則

1. 無 WHERE = 清空全表（慎用）
2. 無法刪除單一欄位值（需 UPDATE SET col=NULL）
3. 僅刪除記錄，保留表結構（刪表用 DROP TABLE）
4. 外鍵關聯表刪除需處理引用完整性（CASCADE/SET NULL/RESTRICT）
5. 大量刪除用 TRUNCATE TABLE（無事務、無觸發器、重置自增）

四、事務手動控制（關閉自動提交）

mysql> SET autocommit = FALSE;
Query OK, 0 rows affected

mysql> SAVEPOINT aaa;
Query OK, 0 rows affected

mysql> DELETE FROM employee;
Query OK, 3 rows affected

mysql> SELECT * FROM employee;
Empty set

mysql> ROLLBACK TO aaa;
Query OK, 0 rows affected

mysql> SELECT * FROM employee;
-- 3 筆資料恢復

五、最佳實踐清單

• 應用層**永遠使用參數化查詢**（PreparedStatement），防注入
• 批量寫入用 INSERT ... VALUES (...), (...), ... 或 LOAD DATA
• 更新/刪除**必加 WHERE**，並先 SELECT 驗證影響行數
• 事務邊界盡量小，避免長事務持有鎖
• 關鍵業務啟用 binlog_format=ROW 便於閃回恢復

DML 是資料庫寫入核心，語法簡單但威力巨大，敬畏之心，測試先行。