➕ MySQL 插入数据

-- 重复时更新数据 INSERT INTO users (id, username, email, login_count) VALUES (1, 'john_doe', 'john@example.com', 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 = DELETE + INSERT REPLACE INTO users (id, username, email) VALUES (1, 'john_updated', 'john_new@example.com'); -- 等同于 -- DELETE FROM users WHERE id = 1; -- INSERT INTO users (id, username, email) VALUES (1, 'john_updated', 'john_new@example.com');

-- 从另一个表复制数据 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 数据 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;

-- 从 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;

-- 大批量插入优化示例 -- 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;

-- 错误信息 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);

-- 错误信息 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));

-- 错误信息 ERROR 1136 (21S01): Column count doesn't match value count -- 解决方案：确保列数和值数匹配 -- 错误示例 INSERT INTO users (username, email) VALUES ('john'); -- 正确示例 INSERT INTO users (username, email) VALUES ('john', 'john@example.com'); -- 或明确指定列 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);