SQL INSERT INTO 语句的用法及注意事项

SQL INSERT INTO 语句详解

一、基本语法

-- 指定所有列（推荐）
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

-- 不指定列名（必须按表结构顺序提供所有值）
INSERT INTO table_name
VALUES (value1, value2, value3, ...);

-- 只插入部分列（其他列使用默认值或NULL）
INSERT INTO table_name (column1, column2)
VALUES (value1, value2);

-- 单条语句插入多行
INSERT INTO table_name (column1, column2)
VALUES (value1, value2),
       (value3, value4),
       (value5, value6);

-- 将查询结果插入到表中
INSERT INTO table_name (column1, column2)
SELECT column_a, column_b
FROM source_table
WHERE condition;

二、注意事项

-- 正确：数据类型匹配
INSERT INTO employees (id, name, salary, hire_date)
VALUES (1, '张三', 5000.00, '2024-01-15');

-- 错误：数据类型不匹配
INSERT INTO employees (id, name)
VALUES ('A100', 5000);  -- id是数字类型，却传了字符串

-- 明确插入NULL
INSERT INTO products (id, name, price, description)
VALUES (1, '商品A', 100.00, NULL);

-- 依赖默认值（description列有DEFAULT约束）
INSERT INTO products (id, name, price)
VALUES (2, '商品B', 200.00);

-- 避免主键重复
INSERT INTO users (user_id, username) VALUES (101, 'john');
-- 如果再次执行相同的user_id会报错

-- 使用ON DUPLICATE KEY UPDATE（MySQL）
INSERT INTO users (user_id, username, login_count)
VALUES (101, 'john', 1)
ON DUPLICATE KEY UPDATE login_count = login_count + 1;

三、高级用法

-- 单条语句批量插入（性能更好）
INSERT INTO orders (customer_id, amount, order_date)
VALUES 
    (1001, 150.00, '2024-01-01'),
    (1002, 200.00, '2024-01-01'),
    (1003, 300.00, '2024-01-02');

-- 而不是多次执行单行插入

-- MySQL：使用IGNORE忽略重复键错误
INSERT IGNORE INTO table_name (id, name)
VALUES (1, 'John'), (2, 'Jane');

-- PostgreSQL：使用ON CONFLICT
INSERT INTO table_name (id, name)
VALUES (1, 'John')
ON CONFLICT (id) DO NOTHING;

-- PostgreSQL：返回自动生成的ID
INSERT INTO products (name, price)
VALUES ('新产品', 99.99)
RETURNING id;

-- SQL Server
INSERT INTO products (name, price)
OUTPUT INSERTED.id
VALUES ('新产品', 99.99);

四、常见问题及解决方案

-- 先确保父表存在相关记录
INSERT INTO departments (dept_id, dept_name) VALUES (10, '销售部');

-- 再插入子表记录
INSERT INTO employees (emp_id, name, dept_id)
VALUES (1001, '张三', 10);  -- dept_id必须存在于departments表

-- 正确处理单引号
INSERT INTO comments (id, content)
VALUES (1, 'It''s a nice day');  -- 使用两个单引号转义

-- 或者使用参数化查询（推荐）
-- 在应用程序中使用预处理语句

-- 大批量插入时考虑
1. 使用事务包裹批量插入
BEGIN TRANSACTION;
INSERT INTO ... VALUES (...);
INSERT INTO ... VALUES (...);
COMMIT;

2. 临时禁用索引和约束（谨慎使用）
ALTER TABLE table_name DISABLE TRIGGER ALL;
-- 执行批量插入
ALTER TABLE table_name ENABLE TRIGGER ALL;

五、最佳实践

始终指定列名

-- 好
INSERT INTO table (col1, col2) VALUES (val1, val2);

-- 不好（表结构变更时容易出错）
INSERT INTO table VALUES (val1, val2, ...);

使用参数化查询防止SQL注入

# Python示例
cursor.execute(
    "INSERT INTO users (name, email) VALUES (%s, %s)",
    (name, email)
)

验证数据完整性

-- 插入前检查
IF NOT EXISTS (SELECT 1 FROM departments WHERE dept_id = 10)
BEGIN
    RAISEERROR('部门不存在', 16, 1);
END

INSERT INTO employees (emp_id, name, dept_id)
VALUES (1001, '张三', 10);

考虑使用UPSERT操作

-- MySQL
INSERT INTO table (id, value) 
VALUES (1, 'a')
ON DUPLICATE KEY UPDATE value = 'a';

-- PostgreSQL
INSERT INTO table (id, value)
VALUES (1, 'a')
ON CONFLICT (id) DO UPDATE SET value = 'a';

六、各数据库差异

通过遵循这些指南和最佳实践，可以确保INSERT INTO语句的正确性、安全性和性能。