插入数据INSERT - 元素码农

发布时间: 2025-04-02 22:52

↑

# 插入数据INSERT

本文将详细介绍如何在SQLite数据库中插入数据，包括基本插入、批量插入以及插入时的注意事项和最佳实践。

## 基本插入语法

### 单行插入

1. **基本语法**
   ```sql
   INSERT INTO table_name (column1, column2, ...) 
   VALUES (value1, value2, ...);
   ```

2. **示例**
   ```sql
   INSERT INTO users (username, email, created_at)
   VALUES ('john_doe', 'john@example.com', CURRENT_TIMESTAMP);
   ```

3. **插入所有列**
   ```sql
   -- 如果提供所有列的值，可以省略列名
   INSERT INTO users
   VALUES (1, 'john_doe', 'john@example.com', CURRENT_TIMESTAMP);
   ```

### 默认值处理

1. **使用DEFAULT关键字**
   ```sql
   INSERT INTO users (username, email, status)
   VALUES ('jane_doe', 'jane@example.com', DEFAULT);
   ```

2. **省略带默认值的列**
   ```sql
   INSERT INTO users (username, email)
   VALUES ('jane_doe', 'jane@example.com');
   ```

## 批量插入

### 多行插入

1. **单条语句插入多行**
   ```sql
   INSERT INTO users (username, email)
   VALUES 
       ('user1', 'user1@example.com'),
       ('user2', 'user2@example.com'),
       ('user3', 'user3@example.com');
   ```

2. **使用事务**
   ```sql
   BEGIN TRANSACTION;
   
   INSERT INTO users (username, email)
   VALUES ('user1', 'user1@example.com');
   
   INSERT INTO users (username, email)
   VALUES ('user2', 'user2@example.com');
   
   COMMIT;
   ```

### 从其他表插入

1. **INSERT INTO ... SELECT**
   ```sql
   INSERT INTO active_users (user_id, username, email)
   SELECT id, username, email
   FROM users
   WHERE status = 'active';
   ```

2. **带条件的插入**
   ```sql
   INSERT INTO premium_users (user_id, username)
   SELECT id, username
   FROM users
   WHERE subscription_type = 'premium'
   AND expiry_date > CURRENT_DATE;
   ```

## 特殊插入操作

### REPLACE INTO

1. **基本用法**
   ```sql
   REPLACE INTO users (id, username, email)
   VALUES (1, 'john_doe', 'new_email@example.com');
   ```

2. **与UNIQUE约束配合**
   ```sql
   -- 如果email已存在，则替换记录
   REPLACE INTO users (username, email)
   VALUES ('john_doe', 'existing@example.com');
   ```

### INSERT OR 语句

1. **INSERT OR IGNORE**
   ```sql
   -- 如果记录已存在，则忽略
   INSERT OR IGNORE INTO users (username, email)
   VALUES ('john_doe', 'john@example.com');
   ```

2. **INSERT OR REPLACE**
   ```sql
   -- 功能同REPLACE INTO
   INSERT OR REPLACE INTO users (username, email)
   VALUES ('john_doe', 'john@example.com');
   ```

## 插入时的约束处理

### 主键处理

1. **自增主键**
   ```sql
   -- 自动生成主键
   INSERT INTO users (username, email)
   VALUES ('new_user', 'new@example.com');
   ```

2. **指定主键**
   ```sql
   -- 明确指定主键值
   INSERT INTO users (id, username, email)
   VALUES (100, 'new_user', 'new@example.com');
   ```

### 外键约束

1. **检查引用完整性**
   ```sql
   -- 确保department_id存在
   INSERT INTO employees (name, department_id)
   VALUES ('John Doe', 1);
   ```

2. **级联插入**
   ```sql
   BEGIN TRANSACTION;
   
   -- 先插入部门
   INSERT INTO departments (id, name)
   VALUES (1, 'IT');
   
   -- 再插入员工
   INSERT INTO employees (name, department_id)
   VALUES ('John Doe', 1);
   
   COMMIT;
   ```

## 性能优化

### 批量插入优化

1. **使用事务**
   ```sql
   BEGIN TRANSACTION;
   
   -- 批量插入多条记录
   INSERT INTO logs (message, created_at)
   SELECT 'Log ' || num, CURRENT_TIMESTAMP
   FROM (SELECT value AS num FROM generate_series(1, 1000));
   
   COMMIT;
   ```

2. **预编译语句**
   ```sql
   -- 在应用代码中使用预编译语句
   -- 示例代码（伪代码）
   prepare_statement = db.prepare(
       "INSERT INTO users (username, email) VALUES (?, ?)"
   );
   ```

### 索引处理

1. **延迟索引更新**
   ```sql
   -- 大批量插入前禁用索引
   PRAGMA defer_foreign_keys = ON;
   
   -- 插入数据
   
   -- 完成后重建索引
   REINDEX table_name;
   ```

2. **选择性索引**
   ```sql
   -- 只为必要的列创建索引
   CREATE INDEX idx_users_email ON users(email);
   ```

## 错误处理

### 常见错误

1. **唯一约束冲突**
   ```sql
   -- 处理唯一约束冲突
   INSERT OR IGNORE INTO users (username, email)
   VALUES ('existing_user', 'existing@example.com');
   ```

2. **外键约束违反**
   ```sql
   -- 确保引用的数据存在
   INSERT INTO orders (user_id, total)
   SELECT id, 100
   FROM users
   WHERE id = 1;
   ```

### 数据验证

1. **插入前验证**
   ```sql
   -- 检查数据有效性
   INSERT INTO products (name, price)
   SELECT name, price
   FROM temp_products
   WHERE price > 0
   AND name IS NOT NULL;
   ```

2. **使用CHECK约束**
   ```sql
   CREATE TABLE products (
       id INTEGER PRIMARY KEY,
       name TEXT NOT NULL,
       price REAL CHECK (price > 0)
   );
   ```

## 最佳实践

### 数据安全

1. **参数化查询**
   - 使用预编译语句
   - 避免SQL注入
   - 正确处理特殊字符

2. **事务管理**
   - 使用事务确保数据一致性
   - 适当的事务大小
   - 错误时回滚

### 性能考虑

1. **批量操作**
   - 合适的批次大小
   - 使用事务包装
   - 监控性能

2. **资源管理**
   - 控制并发插入
   - 管理索引影响
   - 定期维护优化

## 总结

插入数据操作需要注意：

- 选择合适的插入方式
- 正确处理约束
- 注意性能优化
- 做好错误处理

建议：

- 使用参数化查询
- 合理使用事务
- 批量操作时注意性能
- 保持数据一致性