从 CSV 或 Excel 文件将数据插入表的简单方法
4.43/5 (4投票s)
MYSQL：与其读取 CSV 文件并逐行插入数据（繁琐），不如使用一个查询加速处理过程
引言
本文简单介绍了如何使用高效的查询将 CSV 或 Excel 文件中的数据直接传输到 MYSQL 数据库。
背景
有时我们希望将已经存在于 .csv（逗号分隔值）或 Excel 文件中的数据插入到 MYSQL 中。逐行读取文件（例如在 PHP 中）并插入到 MYSQL 中是一种效率低下且耗时的糟糕编码方式。MYSQL 有一个内置查询，我将详细介绍它。
Using the Code
让我们先看一下这个查询
LOAD DATA [LOW_PRIORITY] INFILE 'file_name.csv' [REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS [TERMINATED BY '\t']
[OPTIONALLY] ENCLOSED BY '']
]
[LINES TERMINATED BY '\n']
[IGNORE number LINES]
[(col_name,...)]
LOAD DATA INFILE 以非常高的速度从文本/CSV/Excel 文件读取数据。 您可以清楚地看到从查询中，这里指定的文件是一个 .csv 文件。
[REPLACE | IGNORE]
REPLACE 可以处理重复条目。 例如，您将文件 nam.csv 加载到 MYSQL 数据库中，该数据库将 'emp_id' 作为主键。 假设由于错误或在未来的条目文件中，存在相同的条目。 如果您在查询中指定了 REPLACE 标签，则不会有任何重复，因为查询将被替换。
如果您指定 IGNORE，则跳过在唯一键值上复制现有行的输入行。 如果您没有指定任何选项，则在找到重复的键值时会发生错误，并且将忽略文本文件的其余部分。
[FIELDS [TERMINATED BY '\t']
[OPTIONALLY] ENCLOSED BY '']
]
.csv 文件中的字段可以用 ',' 或 '\t'（制表符）或 ';' 等分隔/分隔。 因此，您可以在查询中指定如何分隔字段。 您还可以指定每个字段是否用 ''（单引号）或 ""（双引号）括起来。
[LINES TERMINATED BY '\n']
通常，新行以字符 '\n' 结尾。 尽管（例如，当您将 Excel 文件转换为 .csv 文件时），在文本模式下，您无法在文件中看到 \n。 您可以使用以下方式指定要添加数据的列的顺序
[(col_name,...)]
如果我们使用 PHP 中的一个小例子，可以更好地理解这个查询。 假设您有一个应用程序，允许用户在他们的机器上选择文件（前端），然后您的工作是编写 PHP 代码，该代码将允许您将文件内容传输到 MYSQL 数据库。
Front_end.html
<html>
<head>
<script> // This function will pop a window which will tell the
// user the order of fields and format of .csv file
// you can create pop_up_csv.html file in same directory and
// modify it with required format
function popitup(url)
{
new_wind=window.open(url,'name','height=700,width=1500');
if (window.focus) {new_wind.focus();
return false;
}
}
</script>
</head>
<body>
<!-- Adding searchable query data ( File => Table ) -->
Add data : The file should be a .csv file <a href="pop_up_csv.html"
onclick="return popitup('pop_up_csv.html')"
> Check the format here </a> </br></br>
<!-- On browsing of a file when you will click on button this page
will be directed to add_data_BE.php-->
<form action="add_data_BE.php" method="post" enctype="multipart/form-data">
<label for="file"> </label>
<input type="file" name="file" id="file" />
</br></br> <input type="submit" value="Load data file into database"
name="button1"/>
</form>
</div>
</body>
</html>
add_data_BE.php
<?php
if (isset($_POST['button1'])) // Do THE FOLLOWING WHEN BUTTON IS PRESSED
{
echo "button on is pressed";
if ($_FILES["file"]["error"] > 0)
{
echo "Error: " . $_FILES["file"]["error"] .
"You have not selected a file or some other error <br />";
}
else
{ // Errorless start
$file_name=$_FILES["file"]["name"];echo $file_name;
$file_type=$_FILES["file"]["type"];
if($file_type!='text/csv')
{
echo "Please the input file should be a .csv file";
}
else
{
// only executed if file is .csv
echo "its correct";
// Creating a temporary copy on the server
$location=""; // write the location on
// server where a copy should be created
move_uploaded_file($_FILES["file"]["tmp_name"],
$location . $_FILES["file"]["name"]);
connect_db(); // MYSQL connection settings
// I have provided a sample query :
// Please make changes as per your database table and columns
$q="LOAD DATA
INFILE '$file_name' INTO TABLE log_analyse
FIELDS TERMINATED BY \"\t\"
LINES TERMINATED BY \"\n\"
( Lang,Doc_Type,Title,Authors,No_of_author,
Corp,Aff,Pub,Pub_yr,Pub_name,Vol,Issue,Keywords )";
mysql_query($q) or die(mysql_error());
}
}
}
?>
<?php
function connect_db()
{
// Please make changes : input your username and password
$con = mysql_connect("localhost","username","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
// enter your database name
mysql_select_db("databasename", $con);
}
?>
历史
- 2010 年 7 月 22 日：初始发布