在php中从CSV文件中将数据插入mysql

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了在php中从CSV文件中将数据插入mysql，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3777字，纯文字阅读大概需要6分钟。

内容图文

我正在开发应用程序,我需要通过csv文件上传数据,然后将记录保存到mysql表中.我在php中这样做.直到现在我有了这个代码.

 <?php
 //date_default_timezone_set("Asia/Karachi");
//echo "The time is " . date("Y-m-d h:i:sa");
$DateTime=date("Y-m-d h:i:sa");
 $FilePath=$_POST['img'];
 $ID=$_POST['ID'];
 $Query_String2="";
// echo $FilePath;
 $row = 1;
 if(isset($FilePath))
 {
    // echo "ID is =".$ID;
       $Query_String1 = "INSERT into ap_form_$ID VALUES";

        if (($handle = fopen($FilePath, "r")) !== FALSE)
         {
            while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) 
            {
                if($row!=1)
                $Query_String2=$Query_String2.",";
                $Query_String2= $Query_String2."("."NULL".","."'".$DateTime."'".","."NULL".","."'127.0.0.1'".","."1".","."NULL";
                $num = count($data);
                $row++;
                $master = array();
                for ($c=0; $c < $num; $c++) 
                {

                    if($c==0)
                        $Query_String2=$Query_String2.","."'".$data[$c]."'";
                    else
                        $Query_String2=$Query_String2.","."'".$data[$c]."'";
                //$master[$c]= $data[$c];
                 }
                       $Query_String2=$Query_String2.")";


                  }

                  $Final_Query=$Query_String1.$Query_String2;
                  echo "Final Query =".$Final_Query;
                $connection = mysqli_connect("localhost", "root","","form_db");

                $result = mysqli_query($connection,$Final_Query) or mysql_error();

                if($result > 0)
                {
                    echo "
                    <script type=\"text/javascript\">
                     alert('Record has been inserted into the form');
                    </script>
                ";
                }
                        else
                {
                        echo "
                    <script type=\"text/javascript\">
                     alert('I am sorry there is some problem');
                    </script>
                ";

                }
    fclose($handle);

我在这段代码中做的是,我从CSV文件中读取数据,然后创建一个长的查询字符串,如“插入表值(”bla,bla,bal),(“bla,bla,bla)”
这个代码适用于小数据,就像我在csv文件中有数据高达1000但当数据超过1000时,进程卡住了,我无法在mysql中保存数据.在我的应用程序中我需要上传至少50000.
任何想法或解决方案来解决这个问题.

解决方法:

如果您在运行MySQL的同一服务器上有CSV文件,您还可以使用LOAD DATA INFILE将CSV文件直接加载到数据库中,例如

LOAD DATA INFILE 'yourfile.csv' 
 INTO TABLE yourtable
 FIELDS TERMINATED BY ',';

有关详细信息,请参阅MySQL参考手册LOAD DATA INFILE.

编辑：

您可以启用LOAD DATA LOCAL以允许将来自Web服务器的CSV文件加载到数据库中,但需要考虑安全因素.再次,reference manual更多详情：

There are two potential security issues with supporting the LOCAL version of LOAD DATA statements:

• The transfer of the file from the client host to the server host is initiated by the MySQL server. In theory, a patched server could be built that would tell the client program to transfer a file of the server’s choosing rather than the file named by the client in the LOAD DATA statement. Such a server could access any file on the client host to which the client user has read access.
• In a Web environment where the clients are connecting from a Web server, a user could use LOAD DATA LOCAL to read any files that the Web server process has read access to (assuming that a user could run any command against the SQL server). In this environment, the client with respect to the MySQL server actually is the Web server, not the remote program being run by the user who connects to the Web server.

要在服务器端启用此功能,请使用：

SET GLOBAL local_infile=ON;

之后你可以验证这个：

> SHOW GLOBAL VARIABLES like "%local%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.00 sec)

您还需要在php.ini中启用它：

[MySQL]
; Allow accessing, from PHP's perspective, local files with LOAD DATA statements
; http://php.net/mysql.allow_local_infile
mysql.allow_local_infile = On

启用LOAD DATA LOCAL后,您可以将Web服务器中的加载CSV文件直接用于数据库：

LOAD DATA LOCAL INFILE 'yourfile.csv' 
 INTO TABLE yourtable
 FIELDS TERMINATED BY ',';

如果需要在加载数据时修改数据,可以先将其存储在局部变量中,然后在插入数据库时??进行修改,例如：

LOAD DATA LOCAL INFILE 'yourfile.csv' 
 INTO TABLE yourtable
 FIELDS TERMINATED BY ','
 (@c1,@c2)
 SET column1=NULL, column2=SUBSTRING(@c2,2,3), column3='127.0.0.1';

内容总结

以上是互联网集市为您收集整理的在php中从CSV文件中将数据插入mysql全部内容，希望文章能够帮你解决在php中从CSV文件中将数据插入mysql所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端