MySQL 系列：第4篇增删改有章法（DML）-编程阁

IT策士 10余年一线大厂经验，专注 IT 思维、架构、职场进阶。我会在各个平台持续发布最新文章，助你少走弯路。

前面的文章我们学会了建库、建表、选类型，相当于盖好了房子、规划了房间。今天终于要往里面“放家具”和“挪家具”了——这就是 DML（Data Manipulation Language），包含INSERT、UPDATE、DELETE三大操作。别小看这三板斧，用不好轻则数据错乱，重则误删全表。我们将用 Python 逐一拆解，并给出生产环境下的安全准则与性能优化技巧。

1. INSERT：把数据塞进去

1.1 单行插入，最基础的姿势

importmysql.connector conn=mysql.connector.connect(host="127.0.0.1",port=3306,user="root",password="MyNewPass123!",database="shop")cursor=conn.cursor()# 先准备好一张产品表（复用第3篇的结构）cursor.execute(""" CREATE TABLE IF NOT EXISTS products(idINT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(200)NOT NULL UNIQUE, price DECIMAL(10,2)NOT NULL, stock INT DEFAULT0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)ENGINE=InnoDB""")# 单行插入sql="INSERT INTO products (title, price, stock) VALUES (%s, %s, %s)"cursor.execute(sql,("Python 入门书",59.90,100))conn.commit()print(f"✅ 插入成功，新 ID = {cursor.lastrowid}")

预期输出：

要点：

参数化查询：%s是占位符，不要用字符串拼接，防止 SQL 注入。
必须 commit：mysql-connector-python默认自动提交关闭，忘记 commit 会导致数据丢失。

1.2 批量插入：效率提升百倍

如果要插入 1000 条数据，单条循环插入会产生 1000 次网络往返。用executemany一次性提交：

books=[("Python 高级编程",79.00,50),("MySQL 实战",69.00,80),("Redis 深度历险",49.00,120),("Linux 私房菜",89.00,30),]sql="INSERT INTO products (title, price, stock) VALUES (%s, %s, %s)"cursor.executemany(sql, books)conn.commit()print(f"✅ 批量插入 {cursor.rowcount} 条记录")

预期输出：

性能对比实验（不妨动手试试）：

用time模块计时，插入 10000 条数据，单条循环 vsexecutemany的耗时可能相差几十倍。这就是批量操作的威力。

1.3 存在即更新：ON DUPLICATE KEY UPDATE

当唯一键冲突时，我们不想报错，而是希望更新某些字段。例如同一本书再次入库时，累加库存：

sql=""" INSERT INTO products(title, price, stock)VALUES(%s, %s, %s)ON DUPLICATE KEY UPDATE stock=stock + VALUES(stock)""" cursor.execute(sql,("Python 入门书",59.90,50))conn.commit()print(f"✅ 库存已累加，受影响行数: {cursor.rowcount}")

预期输出：

注意：如果冲突发生且执行了更新，rowcount返回 2；如果是全新插入返回 1。可据此判断操作类型。

1.4 INSERT IGNORE：冲突时静默跳过

INSERT IGNORE INTO ...遇到主键或唯一键冲突时直接忽略，不报错也不更新。

cursor.execute("INSERT IGNORE INTO products (title, price, stock) VALUES (%s, %s, %s)",("Python 入门书",59.90,10))conn.commit()print(f"受影响行数: {cursor.rowcount}")# 0，因为已存在

2. UPDATE：修改已有数据

2.1 基础语法与 Python 示例

UPDATE 表名 SET 列1=值1, 列2=值2 WHERE 条件;

无 WHERE 的 UPDATE 会修改全表！这是生产事故的高发区。来看一个安全示例：

# 将 id=1 的商品价格上调 10%cursor.execute("UPDATE products SET price = price * 1.1 WHERE id = %s",(1,))conn.commit()print(f"✅ 更新了 {cursor.rowcount} 行")

2.2 安全准则：先查后改

在大批量更新前，最好先用相同 WHERE 条件执行 SELECT，确认受影响的行数：

# 安全三步走：查看 → 确认 → 更新cursor.execute("SELECT COUNT(*) FROM products WHERE stock < 10")count=cursor.fetchone()[0]print(f"⚠️ 即将清空 {count} 件低库存商品的库存，是否继续？")# 实际生产中这里应该有确认逻辑ifcount<100: cursor.execute("UPDATE products SET stock = 0 WHERE stock < 10")conn.commit()print(f"✅ 已将 {cursor.rowcount} 件商品库存置 0")

2.3 利用 LIMIT 防止误更新大量数据

UPDATE products SET stock=0WHERE stock<10LIMIT10;

分批更新，每次只影响少量行，降低事故爆炸半径。

3. DELETE：删数据是件严肃的事

3.1 DELETE 基础

同样，无 WHERE 会清空整个表。建议开启 MySQL 的--safe-updates模式（或设置sql_safe_updates=1），在没带 WHERE 或 LIMIT 时拒绝执行。

# 删除 id=5 的记录cursor.execute("DELETE FROM products WHERE id = %s",(5,))conn.commit()print(f"✅ 删除了 {cursor.rowcount} 行")

3.2 软删除 vs 硬删除

生产环境很少物理删除数据，通常采用软删除：增加is_deleted字段，标记为 1。

ALTER TABLE products ADD COLUMN is_deleted TINYINT DEFAULT0;

删除操作变为：

cursor.execute("UPDATE products SET is_deleted = 1 WHERE id = %s",(3,))conn.commit()

所有查询后面追加WHERE is_deleted = 0，数据可恢复，符合审计要求。

3.3 TRUNCATE：快速清空表

TRUNCATE TABLE products相当于DROP + CREATE，速度极快，不记日志（因此无法回滚），且重置自增 ID。适合清空测试数据，生产慎用。

4. 批量数据导入导出思路

实际工作中经常需要将 CSV 或外部数据导入 MySQL，或者导出备份。这里给出 Python 实现方案。

4.1 从 CSV 文件导入

假设有products.csv文件：

title,price,stock 机器学习,79.00,40 深度学习,89.00,30

importcsv with open("products.csv","r",encoding="utf-8")as f: reader=csv.DictReader(f)data=[(row['title'], row['price'], row['stock'])forrowinreader]sql="INSERT INTO products (title, price, stock) VALUES (%s, %s, %s)"cursor.executemany(sql, data)conn.commit()print(f"✅ 从 CSV 导入了 {cursor.rowcount} 行")

4.2 导出到 CSV

cursor.execute("SELECT title, price, stock FROM products WHERE is_deleted = 0")rows=cursor.fetchall()with open("export_products.csv","w",newline='',encoding="utf-8")as f: writer=csv.writer(f)writer.writerow(["title","price","stock"])writer.writerows(rows)print("✅ 导出完成")

更高效的方式是使用 MySQL 自带的SELECT INTO OUTFILE，但需服务器文件权限。Python 方案更灵活。

5. 事务在 DML 中的角色

DML 操作通常在事务中执行，保证原子性：要么全成功，要么全回滚。Python 默认需要在 DML 后显式commit()，否则关闭连接时自动回滚。

try: cursor.execute("UPDATE products SET stock = stock - 10 WHERE id = 1")cursor.execute("UPDATE products SET stock = stock + 10 WHERE id = 2")conn.commit()except Exception as e: conn.rollback()print(f"❌ 事务回滚: {e}")

这是实现转账、库存扣减等业务的基础。后续文章会深入讲解事务隔离级别与锁。

6. 动手试试：搭建一个小型进货系统

模拟一个图书进货流程：

插入新书，如果已存在则累加库存。
更新库存时，如果更新后库存为负则回滚（使用 Python 检查）。
将所有价格低于 60 元的书打 9 折。
导出当前全部库存到 CSV。

你可以基于前面的products表练习，参考代码框架如下：

# 进货book=("Python 入门书",59.90,20)cursor.execute(""" INSERT INTO products(title, price, stock)VALUES(%s,%s,%s)ON DUPLICATE KEY UPDATE stock=stock + VALUES(stock)""", book)# 安全扣减库存cursor.execute("SELECT stock FROM products WHERE id = 1")stock=cursor.fetchone()[0]ifstock>=5: cursor.execute("UPDATE products SET stock = stock - 5 WHERE id = 1")conn.commit()else: print("库存不足")# 打折cursor.execute("UPDATE products SET price = price * 0.9 WHERE price < 60")conn.commit()# 导出（代码见前）