個人主頁：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;

結語
感謝您的閱讀！期待您的一鍵三連！歡迎指正！