Excel資料匯入Mysql常見問題總表：如何解決匯入資料時遇到的大批量插入問題？

Excel資料匯入Mysql常見問題彙總：如何解決匯入資料時遇到的大批量插入問題？

匯入Excel資料到MySQL是日常開發中常遇到的任務之一。對於少量資料的導入，可以使用資料庫客戶端工具或命令列進行插入操作。但當面對大批量資料匯入時，簡單的單一插入操作無疑會導致嚴重的效能問題。本文將介紹如何解決這個問題，並給出相應的程式碼範例。

問題描述：
在實際使用過程中，當需要匯入Excel表中的大量資料到MySQL資料庫時，單一條插入的效率太低，導致匯入操作非常緩慢。這樣不僅浪費了大量時間，還可能導致資料庫連線逾時或記憶體溢位等問題。

解決方法：
為了提高匯入的效率，我們可以使用批次插入的方式，將多筆記錄一次插入資料庫中。 MySQL提供了多種方法來實現這一目的，以下將分別介紹三種常用的方法。

1. 使用INSERT INTO…VALUES語句
   透過建構INSERT INTO…VALUES語句來實現批次插入。具體步驟如下：

①讀取Excel表格，將資料儲存在二維陣列中；
②將二維陣列轉換為VALUES子句的字串形式；
③拼接INSERT INTO語句，將VALUES子句插入資料庫。

程式碼範例：

import xlrd
import pymysql

# 连接数据库
conn = pymysql.connect(host='localhost', user='root', password='password', database='database')
cursor = conn.cursor()

# 读取Excel表格数据
data = xlrd.open_workbook('data.xlsx')
table = data.sheet_by_name('Sheet1')
rows = table.nrows

# 构建values子句
values = []
for i in range(1, rows):
    values.append(tuple(table.row_values(i)))

# 批量插入
sql = "INSERT INTO table_name (column1, column2, column3) VALUES (%s, %s, %s)"
cursor.executemany(sql, values)
conn.commit()

# 关闭连接
cursor.close()
conn.close()

2. 使用LOAD DATA INFILE語句
   MySQL提供了LOAD DATA INFILE語句用於從檔案中匯入資料。將資料儲存為CSV文件，然後使用LOAD DATA INFILE語句一次匯入到資料庫中，可以大幅提高導入的效率。

程式碼範例：

LOAD DATA INFILE 'data.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '
'
IGNORE 1 LINES;

3. 使用批次插入工具
   除了手動編寫程式碼實作批次插入外，還可以藉助一些工具來自動化處理。例如，可以使用Python的pandas函式庫，透過呼叫to_sql方法將DataFrame中的資料直接插入到MySQL資料庫中。

程式碼範例：

import pandas as pd
from sqlalchemy import create_engine

# 连接数据库
engine = create_engine('mysql+pymysql://root:password@localhost/database')

# 读取Excel表格数据
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')

# 批量插入
df.to_sql('table_name', engine, if_exists='append', index=False)

# 关闭连接
engine.dispose()

總結：
在匯入Excel資料到MySQL時，使用單一條插入的方式效率低下，無法滿足大批量資料的匯入需求。透過大量插入或借助工具的方式可以顯著提高導入的效率，減少導入時間。具體使用哪種方法取決於個人需求和實際情況。希望本文的介紹和範例能幫助讀者解決匯入資料時遇到的大量插入問題。

以上是Excel資料匯入Mysql常見問題總表：如何解決匯入資料時遇到的大批量插入問題？的詳細內容。更多資訊請關注PHP中文網其他相關文章！