【MySQL】数据表插入数据

个人主页：Guiat
归属专栏：MySQL

正文

1. 插入数据概述

插入数据是数据库操作中最基本的操作之一，MySQL提供了多种插入数据的方式，以满足不同的使用场景和需求。

1.1 插入数据的重要性

• 是数据库操作的基础
• 决定数据库中存储的内容质量
• 影响后续查询、更新和分析操作
• 是数据初始化和数据迁移的关键步骤

1.2 插入数据的基本原则

• 确保数据符合表的结构和约束
• 遵循数据类型的要求
• 特殊字符应该正确转义
• 大批量数据应考虑性能优化
• 插入操作应该考虑事务管理
• 注意数据格式和编码的一致性

2. 基本插入语句

2.1 INSERT INTO语法

-- 基本语法
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...);-- 实例：插入完整行数据，指定所有列
INSERT INTO customers (customer_id, first_name, last_name, email, phone)
VALUES (1, 'John', 'Doe', 'john.doe@example.com', '555-123-4567');-- 实例：只插入部分列，其他列使用默认值或NULL
INSERT INTO products (product_name, price, category_id)
VALUES ('Smartphone', 699.99, 3);

2.2 插入多行数据

-- 一次性插入多行数据
INSERT INTO employees (first_name, last_name, department, salary)
VALUES ('Alice', 'Johnson', 'HR', 55000),('Bob', 'Smith', 'IT', 65000),('Carol', 'Williams', 'Finance', 60000),('David', 'Brown', 'Marketing', 50000);-- 带条件的多行插入
INSERT INTO sales (product_id, quantity, sale_date, amount)
VALUES(101, 5, '2023-01-15', 250.00),(102, 3, '2023-01-15', 120.00),(103, 8, '2023-01-16', 400.00);

2.3 不指定列名的插入

-- 不指定列名，按表的列顺序插入（不推荐，易出错）
INSERT INTO countries
VALUES (1, 'USA', 'United States', 'North America');-- 当表结构变化时，上面的查询可能会出错，
-- 而下面的查询会更加健壮
INSERT INTO countries (country_id, code, name, continent)
VALUES (1, 'USA', 'United States', 'North America');

2.4 插入NULL和默认值

-- 显式插入NULL值
INSERT INTO orders (order_id, customer_id, total_amount, notes)
VALUES (1001, 5, 125.50, NULL);-- 隐式插入NULL (省略列)
INSERT INTO orders (order_id, customer_id, total_amount)
VALUES (1002, 6, 230.75);-- 使用DEFAULT关键字插入默认值
INSERT INTO articles (title, content, published)
VALUES ('New Article', 'This is the content', DEFAULT);-- 省略有默认值的列
INSERT INTO articles (title, content)
VALUES ('Another Article', 'More content here');

3. 高级插入技术

3.1 使用子查询插入数据

-- 使用子查询的插入语法
INSERT INTO table_name (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
WHERE condition;-- 实例：从临时用户表插入新客户
INSERT INTO customers (first_name, last_name, email, phone)
SELECT first_name, last_name, email, phone
FROM temp_users
WHERE verified = TRUE;-- 实例：使用计算结果插入数据
INSERT INTO product_stats (product_id, total_sales, average_rating)
SELECT product_id,SUM(quantity * price) AS total_sales,AVG(rating) AS average_rating
FROM sales
JOIN product_reviews USING (product_id)
GROUP BY product_id;

3.2 IGNORE关键字

-- 使用IGNORE关键字忽略错误
INSERT IGNORE INTO users (username, email)
VALUES ('user1', 'user1@example.com'),('user2', 'user2@example.com');-- 如果出现违反唯一约束等错误，操作将继续进行
-- 会忽略引起错误的行，而不是整个语句失败

3.3 ON DUPLICATE KEY UPDATE

-- 遇到重复键时更新数据
INSERT INTO products (product_id, product_name, price, stock)
VALUES (101, 'Tablet Pro', 499.99, 50)
ON DUPLICATE KEY UPDATE price = VALUES(price),stock = stock + VALUES(stock);-- 使用表达式更新
INSERT INTO daily_stats (date, page_views, unique_visitors)
VALUES (CURRENT_DATE, 1500, 750)
ON DUPLICATE KEY UPDATE page_views = page_views + VALUES(page_views),unique_visitors = unique_visitors + VALUES(unique_visitors);

3.4 REPLACE语句

-- 使用REPLACE代替INSERT
REPLACE INTO contacts (contact_id, name, email, phone)
VALUES (25, 'Jane Smith', 'jane.smith@example.com', '555-987-6543');-- REPLACE的工作原理:
-- 1. 如果新行不违反PRIMARY KEY或UNIQUE索引，与INSERT一样插入
-- 2. 如果新行违反约束，先删除旧行，再插入新行

3.5 使用INTO OUTFILE和FROM INFILE

-- 导出数据到文件
SELECT * FROM customers
INTO OUTFILE '/tmp/customers.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';-- 从文件导入数据
LOAD DATA INFILE '/tmp/customers.csv'
INTO TABLE customers
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';-- 使用LOCAL关键字从客户端加载文件
LOAD DATA LOCAL INFILE '/path/on/client/data.csv'
INTO TABLE imported_data
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES; -- 忽略标题行

4. 批量插入优化

4.1 多值插入的性能优势

-- 单条插入（低效）
INSERT INTO log_entries (user_id, action, timestamp)
VALUES (101, 'login', NOW());INSERT INTO log_entries (user_id, action, timestamp)
VALUES (102, 'logout', NOW());-- 多值插入（高效）
INSERT INTO log_entries (user_id, action, timestamp)
VALUES (101, 'login', NOW()),(102, 'logout', NOW()),(103, 'update', NOW()),(104, 'delete', NOW());

多值插入的优势：

• 减少网络往返次数
• 减少事务和锁开销
• 减少日志写入次数
• 提高整体插入性能

4.2 批量插入策略

-- 创建临时表导入数据，然后合并
CREATE TEMPORARY TABLE temp_import (id INT PRIMARY KEY,name VARCHAR(100),value DECIMAL(10,2)
);-- 向临时表导入大量数据
INSERT INTO temp_import VALUES ...;-- 将数据从临时表合并到主表
INSERT INTO main_table (id, name, value)
SELECT id, name, value FROM temp_import
ON DUPLICATE KEY UPDATEname = VALUES(name),value = VALUES(value);

4.3 使用事务提高批量插入性能

-- 开始事务
START TRANSACTION;-- 批量插入多条数据
INSERT INTO orders (customer_id, order_date, total)
VALUES (101, '2023-03-10', 299.99),(102, '2023-03-10', 149.50),-- ... 可以有成百上千条(250, '2023-03-11', 1045.75);-- 提交事务
COMMIT;

4.4 禁用约束和索引以加速大规模导入

-- 临时禁用外键检查
SET FOREIGN_KEY_CHECKS = 0;-- 执行大规模插入
INSERT INTO large_table VALUES ...;-- 重新启用外键检查
SET FOREIGN_KEY_CHECKS = 1;-- 禁用唯一键检查
SET UNIQUE_CHECKS = 0;-- 执行大规模插入
INSERT INTO large_table VALUES ...;-- 重新启用唯一键检查
SET UNIQUE_CHECKS = 1;

4.5 使用LOAD DATA INFILE进行超大数据集导入

-- 使用LOAD DATA INFILE进行高性能导入
LOAD DATA INFILE '/var/lib/mysql-files/huge_dataset.csv'
INTO TABLE massive_data
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(col1, col2, @date_var, col4)
SET created_at = STR_TO_DATE(@date_var, '%Y-%m-%d');

LOAD DATA INFILE优势：

• 直接从服务器文件系统读取，减少网络开销
• 批量处理，减少解析开销
• 支持数据转换和预处理
• 可以是普通INSERT速度的20倍或更高

5. 特殊数据类型的插入

5.1 插入日期和时间

-- 插入显式日期和时间值
INSERT INTO events (event_name, event_date, start_time, end_time)
VALUES ('Conference', '2023-05-15', '09:00:00', '17:00:00');-- 使用函数插入当前日期和时间
INSERT INTO log_entries (action, created_at, updated_at)
VALUES ('system_check', NOW(), NOW());-- 使用不同函数插入日期和时间
INSERT INTO records (created_date,created_time,created_datetime,created_timestamp
)
VALUES (CURRENT_DATE(),CURRENT_TIME(),NOW(),CURRENT_TIMESTAMP()
);-- 插入带有时区的时间
INSERT INTO global_events (event_name, event_time, time_zone)
VALUES ('Global Conference', '2023-05-20 14:30:00', 'UTC');

5.2 插入JSON数据

-- 插入JSON对象
INSERT INTO user_preferences (user_id, preferences)
VALUES (101, '{"theme": "dark", "notifications": true, "sidebar": "left"}');-- 插入JSON数组
INSERT INTO product_tags (product_id, tags)
VALUES (1001, '["electronics", "sale", "featured", "new"]');-- 使用JSON函数构建JSON数据
INSERT INTO document_store (doc_id, content)
VALUES (1, JSON_OBJECT('title', 'Annual Report','year', 2023,'authors', JSON_ARRAY('John Smith', 'Jane Doe'),'metadata', JSON_OBJECT('version', 1.5, 'status', 'draft'))
);

5.3 插入二进制数据（BLOB）

-- 插入十六进制字符串作为二进制数据
INSERT INTO files (file_name, file_data)
VALUES ('logo.png', UNHEX('89504E470D0A1A0A'));-- 使用LOAD_FILE函数（需要MySQL有文件读取权限）
INSERT INTO images (image_name, image_data)
VALUES ('profile.jpg', LOAD_FILE('/var/www/images/profile.jpg'));-- 在编程语言中使用参数化查询插入BLOB数据
-- 以PHP为例：
/*
$stmt = $pdo->prepare("INSERT INTO documents (doc_name, doc_data) VALUES (?, ?)");
$stmt->bindParam(1, $fileName);
$stmt->bindParam(2, $fileData, PDO::PARAM_LOB);
$stmt->execute();
*/

5.4 插入地理空间数据

-- 插入点数据
INSERT INTO locations (name, position)
VALUES ('Store A', ST_GeomFromText('POINT(40.7128 -74.0060)'));-- 插入线数据
INSERT INTO routes (route_name, path)
VALUES ('Route 1', ST_GeomFromText('LINESTRING(0 0, 1 1, 2 2)'));-- 插入多边形数据
INSERT INTO territories (territory_name, boundary)
VALUES ('Territory A', ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))')
);

6. 处理错误和数据验证

6.1 错误处理策略

-- 使用IGNORE忽略错误继续执行
INSERT IGNORE INTO users (username, email)
VALUES ('john', 'john@example.com'),('john', 'john_duplicate@example.com'); -- 会忽略这一行的错误-- 使用INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO page_views (page_id, views)
VALUES (101, 1)
ON DUPLICATE KEY UPDATE views = views + 1;-- 使用REPLACE替换现有记录
REPLACE INTO contacts (contact_id, name, email)
VALUES (1, 'John Doe', 'john.updated@example.com');

6.2 使用事务进行错误控制

-- 使用事务确保数据一致性
START TRANSACTION;-- 插入订单
INSERT INTO orders (order_id, customer_id, total_amount)
VALUES (12345, 101, 456.78);-- 插入订单项
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (12345, 201, 2, 150.00),(12345, 202, 1, 156.78);-- 如果一切正常，提交事务
COMMIT;-- 如果有错误，回滚
-- ROLLBACK;

6.3 预验证和数据清洗

-- 在插入前检查和验证数据
SELECT @invalid_emails := COUNT(*) 
FROM temp_import 
WHERE email NOT LIKE '%@%.%';-- 如果有无效数据，可以清理或报错
SELECT IF(@invalid_emails > 0,SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid email addresses detected','Data validation passed'
) AS validation_result;-- 插入前清理数据
INSERT INTO customers (first_name, last_name, email)
SELECT TRIM(first_name),TRIM(last_name),LOWER(TRIM(email))
FROM temp_customers
WHERE email LIKE '%@%.%';

6.4 使用INSERT … SELECT时的数据验证

-- 使用HAVING子句过滤无效数据
INSERT INTO verified_sales (product_id, year, total_sales)
SELECT product_id,YEAR(sale_date) AS year,SUM(amount) AS total_sales
FROM sales
GROUP BY product_id, YEAR(sale_date)
HAVING COUNT(*) >= 5 -- 只导入有至少5笔销售记录的产品
AND total_sales > 1000; -- 只导入销售额超过1000的数据

7. 实际应用示例

7.1 电子商务数据库数据插入

-- 插入类别数据
INSERT INTO categories (category_name, description, parent_id) 
VALUES ('Electronics', 'Electronic devices and gadgets', NULL),('Computers', 'Desktop and laptop computers', 1),('Smartphones', 'Mobile phones and accessories', 1),('Clothing', 'Apparel and fashion items', NULL),('Men\'s Clothing', 'Clothing for men', 4),('Women\'s Clothing', 'Clothing for women', 4);-- 插入产品数据
INSERT INTO products (product_name, category_id, price, stock_quantity, description)
VALUES ('Gaming Laptop Pro', 2, 1299.99, 25, 'High-performance gaming laptop with the latest GPU'),('Smartphone X', 3, 799.99, 50, 'Latest smartphone with advanced camera features'),('Men\'s Casual Shirt', 5, 39.99, 100, 'Comfortable cotton shirt for casual wear'),('Women\'s Summer Dress', 6, 59.99, 75, 'Light and stylish summer dress');-- 插入客户数据
INSERT INTO customers (first_name, last_name, email, phone, address)
VALUES ('John', 'Doe', 'john.doe@example.com', '555-123-4567', '123 Main St, Anytown, USA'),('Jane', 'Smith', 'jane.smith@example.com', '555-987-6543', '456 Oak Ave, Othertown, USA');-- 插入订单数据
INSERT INTO orders (customer_id, order_date, status, shipping_address, total_amount)
VALUES (1, NOW(), 'processing', '123 Main St, Anytown, USA', 1339.98);-- 插入订单项目数据
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (1, 1, 1, 1299.99),(1, 3, 1, 39.99);-- 更新产品库存
UPDATE products 
SET stock_quantity = stock_quantity - 1
WHERE product_id IN (1, 3);

7.2 博客系统数据插入

-- 插入用户数据
INSERT INTO users (username, email, password_hash, display_name, bio, registration_date) 
VALUES ('admin', 'admin@blog.com', SHA2('secure_password', 256), 'Admin User', 'Blog administrator', NOW()),('author1', 'author1@blog.com', SHA2('author_password', 256), 'First Author', 'Professional blogger', NOW());-- 插入分类数据
INSERT INTO categories (category_name, slug, description) 
VALUES ('Technology', 'technology', 'Articles about technology'),('Lifestyle', 'lifestyle', 'Lifestyle and personal development'),('Travel', 'travel', 'Travel guides and experiences');-- 插入文章数据
INSERT INTO posts (title, slug, content, author_id, published_date, status, featured_image)
VALUES ('Getting Started with MySQL', 'getting-started-mysql', 'MySQL is a popular relational database management system...',1, NOW(), 'published','/images/mysql-logo.jpg'),('Top 10 Travel Destinations', 'top-travel-destinations', 'Here are the top destinations you should visit this year...',2, NOW(), 'published','/images/travel.jpg');-- 文章与分类关联
INSERT INTO post_categories (post_id, category_id)
VALUES (1, 1), -- MySQL文章关联到Technology分类(2, 3); -- 旅行文章关联到Travel分类-- 插入标签数据
INSERT INTO tags (tag_name, slug) 
VALUES ('MySQL', 'mysql'),('Database', 'database'),('Travel', 'travel'),('Adventure', 'adventure');-- 文章与标签关联
INSERT INTO post_tags (post_id, tag_id)
VALUES (1, 1), -- MySQL文章关联MySQL标签(1, 2), -- MySQL文章关联Database标签(2, 3), -- 旅行文章关联Travel标签(2, 4); -- 旅行文章关联Adventure标签-- 插入评论
INSERT INTO comments (post_id, author_name, author_email, content, created_at, status)
VALUES (1, 'Reader One', 'reader1@example.com', 'Great introduction to MySQL!', NOW(), 'approved'),(1, 'Reader Two', 'reader2@example.com', 'Very helpful tutorial.', NOW(), 'approved'),(2, 'Traveler', 'traveler@example.com', 'I visited destination #3 last year, it was amazing!', NOW(), 'approved');

7.3 日志系统数据导入

-- 创建临时表存储日志
CREATE TEMPORARY TABLE temp_logs (log_timestamp VARCHAR(25),log_level VARCHAR(10),service VARCHAR(50),message TEXT,ip_address VARCHAR(15)
);-- 从CSV文件导入日志数据
LOAD DATA INFILE '/var/log/app/logs.csv'
INTO TABLE temp_logs
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;-- 清理和标准化数据后插入到主日志表
INSERT INTO system_logs (timestamp, log_level, service, message, ip_address, created_date)
SELECT STR_TO_DATE(log_timestamp, '%Y-%m-%d %H:%i:%s'),UPPER(log_level),TRIM(service),message,ip_address,DATE(STR_TO_DATE(log_timestamp, '%Y-%m-%d %H:%i:%s'))
FROM temp_logs
WHERE log_level IN ('error', 'warning', 'info', 'debug');-- 为日志摘要生成统计数据
INSERT INTO log_summary (log_date, total_errors, total_warnings, total_info)
SELECT DATE(timestamp) AS log_date,SUM(IF(log_level = 'ERROR', 1, 0)) AS total_errors,SUM(IF(log_level = 'WARNING', 1, 0)) AS total_warnings,SUM(IF(log_level = 'INFO', 1, 0)) AS total_info
FROM system_logs
GROUP BY DATE(timestamp)
ON DUPLICATE KEY UPDATEtotal_errors = VALUES(total_errors),total_warnings = VALUES(total_warnings),total_info = VALUES(total_info);

8. 批量导入数据分析

8.1 性能比较

不同插入方法的性能比较：

8.2 不同数据量的最佳实践

数据量	建议方法	其他优化
少量(<100)	标准INSERT	单事务处理
中量(100-10K)	多行VALUES INSERT	适当批量大小(500-1000行/批)
大量(10K-1M)	INSERT…SELECT 或批处理	禁用索引，使用事务
海量(>1M)	LOAD DATA INFILE	分区表，临时禁用约束

8.3 插入性能优化策略

-- 1. 使用事务包裹多个INSERT
START TRANSACTION;
-- 多个INSERT语句
COMMIT;-- 2. 临时禁用索引
ALTER TABLE large_table DISABLE KEYS;
-- 批量插入数据
ALTER TABLE large_table ENABLE KEYS;-- 3. 调整MySQL配置参数
-- my.cnf或my.ini中:
-- innodb_buffer_pool_size = 1G
-- innodb_flush_log_at_trx_commit = 0
-- bulk_insert_buffer_size = 256M-- 4. 使用INSERT DELAYED（仅MyISAM表）
-- INSERT DELAYED INTO logs (message) VALUES ('Log entry');-- 5. 使用分区表处理大数据集
CREATE TABLE huge_data (id INT, value INT,log_date DATE
)
PARTITION BY RANGE (TO_DAYS(log_date)) (PARTITION p0 VALUES LESS THAN (TO_DAYS('2023-01-01')),PARTITION p1 VALUES LESS THAN (TO_DAYS('2023-02-01')),PARTITION p2 VALUES LESS THAN (TO_DAYS('2023-03-01')),PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

9. 安全考虑

9.1 预防SQL注入

在编程语言中使用参数化查询，而不是直接拼接SQL：

// 错误示例 (PHP)
// $query = "INSERT INTO users (username, password) VALUES ('$username', '$password')";// 正确示例 (PHP with PDO)
// $stmt = $pdo->prepare("INSERT INTO users (username, password) VALUES (?, ?)");
// $stmt->execute([$username, $password]);

9.2 适当的权限控制

-- 创建只有插入权限的用户
CREATE USER 'app_insert'@'localhost' IDENTIFIED BY 'password';
GRANT INSERT ON database_name.table_name TO 'app_insert'@'localhost';
FLUSH PRIVILEGES;

9.3 输入数据验证和清洗

-- 在插入前验证和清理数据
INSERT INTO user_profiles (user_id, display_name, website)
SELECT user_id,-- 移除危险HTML标签REGEXP_REPLACE(display_name, '<[^>]*script[^>]*>', '[removed]'),-- 确保网址格式正确IF(website REGEXP '^https?://.+', website, NULL)
FROM temp_user_data;

10. 高级用例和技巧

10.1 使用导入工具

# 使用mysqldump导出数据
mysqldump -u username -p database_name table_name > table_dump.sql# 使用mysql命令导入数据
mysql -u username -p database_name < table_dump.sql# 使用mysqlimport导入CSV数据
mysqlimport --local --fields-terminated-by=',' \--columns='column1,column2,column3' \--lines-terminated-by='\n' \database_name /path/to/datafile.csv

10.2 使用存储过程自动化数据插入

-- 创建存储过程生成测试数据
DELIMITER //
CREATE PROCEDURE generate_test_data(IN num_rows INT)
BEGINDECLARE i INT DEFAULT 0;START TRANSACTION;WHILE i < num_rows DOINSERT INTO test_data (name,value,created_at) VALUES (CONCAT('Test Name ', i),RAND() * 1000,DATE_ADD(NOW(), INTERVAL RAND() * 365 DAY));SET i = i + 1;END WHILE;COMMIT;
END //
DELIMITER ;-- 调用存储过程
CALL generate_test_data(10000);

10.3 使用触发器验证插入数据

-- 创建触发器验证插入的数据
DELIMITER //
CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN-- 检查薪资范围IF NEW.salary < 0 THENSIGNAL SQLSTATE '45000'SET MESSAGE_TEXT = 'Salary cannot be negative';END IF;-- 确保入职日期不在未来IF NEW.hire_date > CURRENT_DATE THENSIGNAL SQLSTATE '45000'SET MESSAGE_TEXT = 'Hire date cannot be in the future';END IF;

结语
感谢您的阅读！期待您的一键三连！欢迎指正！