MySQL高级语句

MySQL 高级语句：冲突处理、表操作与数据导入导出

在日常数据库操作中，除了基础的增删改查，还会遇到主键冲突处理、表备份、数据迁移等场景。MySQL 提供了一系列高级语句来简化这些操作，本文详细解析常用高级语句的用法与场景。

主键 / 唯一键冲突处理语句

当插入数据时遇到主键（PRIMARY KEY）或唯一键（UNIQUE）冲突，MySQL 提供了三种处理方式，避免直接报错。

1. INSERT IGNORE INTO：冲突时忽略插入

• 作用：若插入的数据与现有主键 / 唯一键冲突，则忽略当前插入操作（不报错，也不修改数据）。
• 适用场景：希望保留旧数据，跳过重复插入的场景（如批量导入时去重）。

示例：

-- 表结构（id为主键）
CREATE TABLE staff (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

-- 初始数据
INSERT INTO staff VALUES (1, '张三', 20);

-- 插入冲突数据（id=1已存在）
INSERT IGNORE INTO staff VALUES (1, '李四', 25);  -- 无报错，数据不变
SELECT * FROM staff;  -- 仍为 (1, '张三', 20)

2. INSERT ... ON DUPLICATE KEY UPDATE：冲突时更新

• 作用：若插入冲突，则执行 UPDATE 语句更新指定字段（保留旧数据的部分字段，更新其他字段）。
• 适用场景：需要用新数据更新旧数据的场景（如用户信息更新）。

示例：

-- 插入冲突时，更新name和age
INSERT INTO staff (id, name, age) 
VALUES (1, '李四', 18) 
ON DUPLICATE KEY UPDATE 
  name = '李四',  -- 用新值更新name
  age = age + 1;  -- 也可基于旧值计算（此处旧age=20 → 20+1=21）

SELECT * FROM staff;  -- 结果：(1, '李四', 21)

3. REPLACE INTO：冲突时替换

• 作用：若插入冲突，则先删除旧记录，再插入新记录（相当于 “替换”）。
• 注意：
  • 会破坏自增主键连续性（删除旧记录后，自增 ID 不会回退）。
  • 若表有外键，可能触发级联删除，需谨慎使用。

示例：

-- 替换id=1的记录
REPLACE INTO staff (id, name, age) VALUES (1, '王五', 30);

SELECT * FROM staff;  -- 结果：(1, '王五', 30)（旧记录被删除，新记录插入）

表结构与数据复制语句

快速复制表结构或数据，避免重复编写 CREATE TABLE 和 INSERT 语句。

1. 复制表结构与数据：CREATE TABLE ... SELECT

• 作用：创建新表，并将查询结果导入新表（包含结构和数据）。
• 注意：新表的字段类型由查询结果自动推断，可能与原表有差异（如主键、索引不会自动复制）。

示例：

-- 复制user_info表的结构和所有数据到user_info_backup
CREATE TABLE user_info_backup 
SELECT * FROM user_info;

-- 复制部分字段和数据（如复制id>100的用户）
CREATE TABLE user_info_large 
SELECT id, username FROM user_info WHERE id > 100;

2. 仅复制表结构：CREATE TABLE ... LIKE

• 作用：创建与原表结构完全一致的空表（包括主键、索引、约束等），不含数据。
• 适用场景：创建备份表或测试表（需保留原表结构）。

示例：

-- 复制user表的结构（不含数据）到user_v2
CREATE TABLE user_v2 LIKE user;

-- 验证：新表结构与user一致，无数据
DESC user_v2;
SELECT * FROM user_v2;  -- 空表

3. 从查询结果导入数据：INSERT INTO ... SELECT

• 作用：将一个表的查询结果插入到另一个已存在的表（需保证字段匹配）。
• 适用场景：数据迁移、批量导入（如将旧表数据同步到新表）。

示例：

-- 假设user_v2已存在，导入user表中status=1的用户
INSERT INTO user_v2 (id, username, email)
SELECT id, username, email FROM user WHERE status = 1;

使用注意事项

1. 主键冲突语句的性能：
   • INSERT IGNORE 和 REPLACE 可能导致全表扫描（无索引时），大数据量表需确保主键 / 唯一键有索引。
   • ON DUPLICATE KEY UPDATE 性能优于先 DELETE 再 INSERT，适合高频更新场景。
2. 表复制的限制：
   • CREATE TABLE ... SELECT 不会复制原表的索引、触发器、外键等（仅复制字段和数据）。
   • CREATE TABLE ... LIKE 会复制索引和约束，但不会复制数据和触发器。
3. 自增主键的影响：
   • REPLACE 会删除旧记录，导致自增 ID 跳号（如原 ID=1 被删除，新记录 ID 可能为 2），不适合依赖连续 ID 的场景。
4. 数据一致性：
   • 批量复制数据时，建议加锁或在事务中执行（如 BEGIN; INSERT ... SELECT; COMMIT;），避免数据不一致