1. INSERT ON DUPLICATE KEY UPDATE
MySQL 插入、删除和更新操作详解
MySQL 中的插入(INSERT)、删除(DELETE)和更新(UPDATE)操作是数据库管理的基础操作，下面我将详细介绍这些操作的知识点，并提供代码示例。
一、插入数据(INSERT)
1. 基本插入语法
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...); 示例：
-- 向employees表中插入一条记录
INSERT INTO employees (emp_id, name, department, salary)
VALUES (101, '张三', '销售部', 5000); 2. 插入多行数据
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...), (value1, value2, ...), ...; 示例：
-- 一次性插入多条记录
INSERT INTO employees (emp_id, name, department, salary)
VALUES (102, '李四', '技术部', 8000), (103, '王五', '市场部', 6000), (104, '赵六', '人事部', 5500); 3. 从其他表插入数据
INSERT INTO table_name1 (column1, column2, ...)
SELECT column1, column2, ... FROM table_name2 WHERE condition; 示例：
-- 从temp_employees表中选择数据插入到employees表
INSERT INTO employees (emp_id, name, department, salary)
SELECT emp_id, name, department, salary FROM temp_employees WHERE hire_date > '2022-01-01'; 4. 插入NULL值和默认值
-- 插入NULL值
INSERT INTO employees (emp_id, name, department, salary, bonus) VALUES (105, '钱七', '财务部', 7000, NULL);
-- 使用默认值
INSERT INTO employees (emp_id, name, department, salary) VALUES (106, '孙八', DEFAULT, 6500); 二、更新数据(UPDATE)
1. 基本更新语法
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; 示例：
-- 更新特定员工的工资
UPDATE employees SET salary = 7500 WHERE emp_id = 101; -- 更新多个字段 UPDATE employees SET department = '研发部', salary = salary * 1.1 WHERE department = '技术部'; 2. 使用子查询更新
UPDATE table_name SET column1 = (SELECT ...) WHERE condition; 示例：
-- 根据部门平均工资调整员工工资
UPDATE employees e SET salary = salary * 1.05 WHERE salary < ( SELECT AVG(salary) FROM employees WHERE department = e.department ); 3. 批量更新注意事项
对于大批量更新，建议：
- 在低峰期执行
- 分批更新，每次更新少量数据
- 添加适当的sleep时间避免锁争用
示例：
-- 分批更新示例
UPDATE products SET price = price * 1.1
WHERE id BETWEEN 1 AND 100; DO SLEEP(0.1);
UPDATE products SET price = price * 1.1 WHERE id BETWEEN 101 AND 200; DO SLEEP(0.1); 三、删除数据(DELETE)
1. 基本删除语法
DELETE FROM table_name WHERE condition; 示例：
-- 删除特定员工记录
DELETE FROM employees WHERE emp_id = 105;
-- 删除特定条件的记录
DELETE FROM employees WHERE department = '临时部门'; 2. 清空表数据
-- 使用DELETE清空表(可回滚)
DELETE FROM table_name;
-- 使用TRUNCATE清空表(更快，不可回滚)
TRUNCATE TABLE table_name; 3. 使用外键约束的删除
当表有外键约束时，可以使用ON DELETE CASCADE自动删除相关记录：
-- 创建表时设置级联删除
CREATE TABLE orders ( order_id INT PRIMARY KEY, emp_id INT, order_date DATE, FOREIGN KEY (emp_id) REFERENCES employees(emp_id) ON DELETE CASCADE );
-- 删除员工时，其相关订单也会自动删除
DELETE FROM employees WHERE emp_id = 101; 四、高级操作技巧
1. INSERT ON DUPLICATE KEY UPDATE
当插入数据时如果主键冲突，则执行更新操作：
INSERT INTO employees (emp_id, name, department, salary)
VALUES (101, '张三', '销售部', 5500) ON DUPLICATE KEY UPDATE name = VALUES(name), department = VALUES(department), salary = VALUES(salary); 2. REPLACE INTO
如果记录存在则先删除再插入，不存在则直接插入：
REPLACE INTO employees (emp_id, name, department, salary)
VALUES (101, '张三', '销售部', 6000); 3. 使用事务保证操作原子性
START TRANSACTION; INSERT INTO orders (order_id, emp_id, order_date, amount) VALUES (1001, 101, '2023-05-01', 1000);
UPDATE employees SET sales_count = sales_count + 1 WHERE emp_id = 101; COMMIT; -- 如果出现错误可以执行 ROLLBACK; 五、性能优化建议
- 批量操作：尽量使用批量插入而非单条插入
- 索引影响：无索引时装载比索引装载更快
- LOAD DATA：大批量数据导入时，LOAD DATA比INSERT快约20倍
- 锁定策略：大批量更新时考虑分批进行，避免长时间锁表
- 禁用索引：大批量插入前可暂时禁用索引，完成后再重建
示例：
-- 禁用索引
ALTER TABLE employees DISABLE KEYS;
-- 执行大批量插入
INSERT INTO employees ...;
-- 启用索引
ALTER TABLE employees ENABLE KEYS; 总结
MySQL的增删改操作是数据库管理的核心功能。合理使用这些操作，并结合事务、索引优化等技术，可以显著提高数据库操作效率和安全性。在实际应用中，应根据具体场景选择最适合的操作方式，并注意性能优化和数据一致性问题。