問題描述

我正在將一些 20000 行的數據從 CSV 文件導入 Mysql.

I am importing some data of 20000 rows from a CSV file into Mysql.

CSV 中的列與 MySQL 表的列的順序不同.如何自動分配Mysql表列對應的列?

Columns in the CSV are in a different order than MySQL table's columns. How to automatically assign columns corresponding to Mysql table columns?

當我執行

LOAD DATA INFILE'abc.csv' INTO TABLE abc

此查詢將所有數據添加到第一列.

this query adds all data to the first column.

請建議將數據導入 Mysql 的自動語法.

Please suggest auto syntax for importing data to Mysql.

推薦答案

您可以使用 LOAD DATA INFILE 命令將 csv 文件導入表中.

You can use LOAD DATA INFILE command to import csv file into table.

檢查這個鏈接MySQL - LOAD DATA INFILE.

Check this link MySQL - LOAD DATA INFILE.

LOAD DATA LOCAL INFILE 'abc.csv' INTO TABLE abc
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(col1, col2, col3, col4, col5...);

對于 MySQL 8.0 用戶:

使用 LOCAL 關鍵字會帶來安全風險，從 MySQL 8.0 開始，LOCAL 功能默認設置為 False.您可能會看到錯誤:

Using the LOCAL keyword hold security risks and as of MySQL 8.0 the LOCAL capability is set to False by default. You might see the error:

ERROR 1148: 此 MySQL 版本不允許使用的命令

ERROR 1148: The used command is not allowed with this MySQL version

您可以按照中的說明覆蓋它文檔.請注意，這種覆蓋并不能解決安全問題，而只是承認您知道并愿意承擔風險.

You can overwrite it by following the instructions in the docs. Beware that such overwrite does not solve the security issue but rather just an acknowledge that you are aware and willing to take the risk.

這篇關于MYSQL 使用 LOAD DATA INFILE 從 csv 導入數據的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！