1732 字
约 5 分钟
14
➕ MySQL 插入数据
2026-09-19
MySQL
无标签

➕ MySQL 插入数据

数据插入概述

INSERT 语句用于向数据库表中插入新的数据行。MySQL 提供了多种插入数据的方式，包括单行插入、多行插入、从其他表插入等。

INSERT 基本语法

单行插入

-- 基本语法
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

-- 示例
INSERT INTO users (username, email, age)
VALUES ('john_doe', '[email protected]', 25);

-- 插入所有列（按表结构顺序）
INSERT INTO users 
VALUES (1, 'jane_smith', '[email protected]', 30, '2024-01-15 10:30:00');

多行插入

-- 一次插入多行数据
INSERT INTO users (username, email, age) VALUES
    ('alice', '[email protected]', 28),
    ('bob', '[email protected]', 32),
    ('charlie', '[email protected]', 24);

-- 大批量插入示例
INSERT INTO products (name, price, category_id) VALUES
    ('iPhone 15', 999.99, 1),
    ('Samsung Galaxy S24', 899.99, 1),
    ('MacBook Pro', 1999.99, 2),
    ('Dell XPS 13', 1299.99, 2),
    ('iPad Air', 599.99, 3);

高级插入操作

INSERT IGNORE

-- 忽略重复键错误
INSERT IGNORE INTO users (username, email) VALUES
    ('existing_user', '[email protected]'),
    ('new_user', '[email protected]');

-- 如果 username 或 email 已存在，该行将被忽略
-- 不会产生错误，继续插入其他行

ON DUPLICATE KEY UPDATE

-- 重复时更新数据
INSERT INTO users (id, username, email, login_count) 
VALUES (1, 'john_doe', '[email protected]', 1)
ON DUPLICATE KEY UPDATE 
    login_count = login_count + 1,
    last_login = NOW();

-- 批量插入时的重复键处理
INSERT INTO product_stats (product_id, view_count, sale_count) VALUES
    (1, 10, 2),
    (2, 15, 3),
    (3, 8, 1)
ON DUPLICATE KEY UPDATE 
    view_count = view_count + VALUES(view_count),
    sale_count = sale_count + VALUES(sale_count);

REPLACE 语句

-- REPLACE = DELETE + INSERT
REPLACE INTO users (id, username, email) 
VALUES (1, 'john_updated', '[email protected]');

-- 等同于
-- DELETE FROM users WHERE id = 1;
-- INSERT INTO users (id, username, email) VALUES (1, 'john_updated', '[email protected]');

从其他表插入数据

INSERT INTO ... SELECT

-- 从另一个表复制数据
INSERT INTO backup_users (username, email, created_at)
SELECT username, email, created_at 
FROM users 
WHERE created_at < '2024-01-01';

-- 带条件的复制
INSERT INTO active_users (user_id, username, email)
SELECT id, username, email 
FROM users 
WHERE status = 'active' AND last_login > DATE_SUB(NOW(), INTERVAL 30 DAY);

-- 跨数据库复制
INSERT INTO archive_db.old_orders
SELECT * FROM current_db.orders 
WHERE order_date < '2023-01-01';

复杂查询插入

-- 聚合数据插入
INSERT INTO monthly_sales (year, month, total_amount, order_count)
SELECT 
    YEAR(order_date) as year,
    MONTH(order_date) as month,
    SUM(total_amount) as total_amount,
    COUNT(*) as order_count
FROM orders 
WHERE order_date >= '2024-01-01'
GROUP BY YEAR(order_date), MONTH(order_date);

-- 连接查询插入
INSERT INTO user_order_summary (user_id, username, total_orders, total_spent)
SELECT 
    u.id,
    u.username,
    COUNT(o.id) as total_orders,
    COALESCE(SUM(o.total_amount), 0) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

特殊数据类型插入

日期和时间

-- 插入日期时间数据
INSERT INTO events (name, event_date, event_time, created_at) VALUES
    ('Meeting', '2024-03-15', '14:30:00', NOW()),
    ('Conference', '2024-04-20', '09:00:00', CURRENT_TIMESTAMP),
    ('Workshop', CURDATE(), CURTIME(), SYSDATE());

-- 使用函数插入
INSERT INTO logs (message, log_level, created_at) VALUES
    ('System started', 'INFO', NOW()),
    ('User login', 'DEBUG', UTC_TIMESTAMP()),
    ('Error occurred', 'ERROR', CURRENT_TIMESTAMP);

JSON 数据

-- 插入 JSON 数据
INSERT INTO user_profiles (user_id, profile_data) VALUES
    (1, '{"age": 25, "city": "Beijing", "interests": ["reading", "travel"]}'),
    (2, JSON_OBJECT('age', 30, 'city', 'Shanghai', 'married', true)),
    (3, JSON_ARRAY('hobby1', 'hobby2', 'hobby3'));

-- 使用 JSON 函数
INSERT INTO settings (user_id, config) VALUES
    (1, JSON_OBJECT(
        'theme', 'dark',
        'language', 'zh-CN',
        'notifications', JSON_OBJECT('email', true, 'sms', false)
    ));

二进制数据

-- 插入二进制数据
INSERT INTO files (filename, file_data, file_size) VALUES
    ('document.pdf', LOAD_FILE('/path/to/document.pdf'), 1024000);

-- 使用十六进制字符串
INSERT INTO binary_data (data) VALUES
    (0x48656C6C6F),  -- 'Hello' in hex
    (X'576F726C64');  -- 'World' in hex

批量插入优化

大批量插入技巧

-- 1. 使用多行 VALUES
INSERT INTO large_table (col1, col2, col3) VALUES
    (val1, val2, val3),
    (val4, val5, val6),
    -- ... 可以插入数千行
    (val_n1, val_n2, val_n3);

-- 2. 调整批次大小（建议每批1000-5000行）
-- 分批插入而不是一次性插入所有数据

-- 3. 禁用自动提交（事务批处理）
START TRANSACTION;
INSERT INTO table_name VALUES (...);
INSERT INTO table_name VALUES (...);
-- ... 多个插入语句
COMMIT;

LOAD DATA INFILE

-- 从 CSV 文件批量导入
LOAD DATA INFILE '/path/to/data.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS  -- 跳过标题行
(username, email, age);

-- 本地文件导入
LOAD DATA LOCAL INFILE '/local/path/data.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
(name, price, description);

-- 处理数据转换
LOAD DATA INFILE '/path/to/sales.csv'
INTO TABLE sales
FIELDS TERMINATED BY ','
(product_id, @sale_date, quantity, @price)
SET 
    sale_date = STR_TO_DATE(@sale_date, '%Y-%m-%d'),
    unit_price = @price,
    total_amount = quantity * @price;

性能优化建议

🚀 插入性能优化批量插入：使用多行 VALUES 而不是多个单行 INSERT事务控制：将多个插入操作包装在事务中禁用索引：大批量插入时临时禁用非必要索引调整参数：增加 bulk_insert_buffer_size使用 LOAD DATA：对于大文件，使用 LOAD DATA INFILE

-- 大批量插入优化示例
-- 1. 禁用自动提交
SET autocommit = 0;

-- 2. 临时禁用唯一性检查（谨慎使用）
SET unique_checks = 0;

-- 3. 临时禁用外键检查（谨慎使用）
SET foreign_key_checks = 0;

-- 4. 执行批量插入
INSERT INTO large_table (col1, col2, col3) VALUES
    -- 大量数据...
;

-- 5. 提交事务
COMMIT;

-- 6. 恢复设置
SET unique_checks = 1;
SET foreign_key_checks = 1;
SET autocommit = 1;

常见错误和解决方案

错误 1：重复键错误

-- 错误信息
ERROR 1062 (23000): Duplicate entry 'value' for key 'PRIMARY'

-- 解决方案1：使用 INSERT IGNORE
INSERT IGNORE INTO table_name (columns) VALUES (values);

-- 解决方案2：使用 ON DUPLICATE KEY UPDATE
INSERT INTO table_name (columns) VALUES (values)
ON DUPLICATE KEY UPDATE column = VALUES(column);

-- 解决方案3：使用 REPLACE
REPLACE INTO table_name (columns) VALUES (values);

错误 2：数据类型不匹配

-- 错误信息
ERROR 1366 (HY000): Incorrect integer value

-- 解决方案：确保数据类型匹配
-- 错误示例
INSERT INTO users (age) VALUES ('not_a_number');

-- 正确示例
INSERT INTO users (age) VALUES (25);
-- 或使用类型转换
INSERT INTO users (age) VALUES (CAST('25' AS UNSIGNED));

错误 3：列数不匹配

-- 错误信息
ERROR 1136 (21S01): Column count doesn't match value count

-- 解决方案：确保列数和值数匹配
-- 错误示例
INSERT INTO users (username, email) VALUES ('john');

-- 正确示例
INSERT INTO users (username, email) VALUES ('john', '[email protected]');
-- 或明确指定列
INSERT INTO users (username) VALUES ('john');

实际应用示例

电商订单插入

-- 创建订单（事务处理）
START TRANSACTION;

-- 插入订单主表
INSERT INTO orders (user_id, total_amount, status, created_at) 
VALUES (123, 299.98, 'pending', NOW());

-- 获取订单ID
SET @order_id = LAST_INSERT_ID();

-- 插入订单明细
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
    (@order_id, 1, 2, 99.99),
    (@order_id, 2, 1, 99.99);

-- 更新库存
UPDATE products SET stock_quantity = stock_quantity - 2 WHERE id = 1;
UPDATE products SET stock_quantity = stock_quantity - 1 WHERE id = 2;

COMMIT;

日志记录插入

-- 应用日志插入
INSERT INTO application_logs (level, message, user_id, ip_address, user_agent, created_at)
VALUES 
    ('INFO', 'User login successful', 123, '192.168.1.100', 'Mozilla/5.0...', NOW()),
    ('WARNING', 'Failed login attempt', NULL, '192.168.1.200', 'curl/7.68.0', NOW()),
    ('ERROR', 'Database connection failed', NULL, '127.0.0.1', 'Internal', NOW());

-- 性能监控数据插入
INSERT INTO performance_metrics (metric_name, metric_value, timestamp)
SELECT 
    'active_users' as metric_name,
    COUNT(*) as metric_value,
    NOW() as timestamp
FROM users 
WHERE last_activity > DATE_SUB(NOW(), INTERVAL 5 MINUTE);

📋 插入数据最佳实践数据验证：在插入前验证数据的有效性事务使用：对于相关的多表插入使用事务错误处理：处理重复键和约束违反错误批量操作：大量数据使用批量插入提高性能索引考虑：大批量插入时考虑临时禁用索引备份策略：重要数据插入前做好备份监控性能：监控插入操作的性能影响

➕ MySQL 插入数据
https://clxhxhhr.top/posts/2805/
作者
clxstart
发布于
2026-09-19
许可协议
CC BY-NC-SA 4.0
MySQL 排序
🐬 MySQL 教程
评论
0 条
还没有评论，先写一条吧。