[乐意黎]MySQL使用事务及 PDO 插入大批量数据

梦里梦外; 2022-04-05 03:12 237阅读 0赞

<?php
    $serverIP = "127.0.0.1";
    $userName= "user";
    $passWord= "pass";
    $dbname = "aerchi";
     
    try {
        $pdo = new PDO("mysql:host=$serverIP;dbname=$dbName", $userName, $passWord);
        // set the PDO error mode to exception
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
        $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
     
        // begin transaction
        $pdo->beginTransaction();
        // SQL 
        for($n=0; $n<($multi_items_size+1); $n++){
            $pdo->exec("INSERT INTO userInfo(name, phone, email) VALUES ('zhangsan', '13808710001', 'zhangsan@test.com')");
    
            //$pdo->exec("INSERT INTO userInfo(name, phone, email) VALUES ('lisi', '13808710002', 'lisi@test.com')");
    
            //$pdo->exec("INSERT INTO userInfo(name, phone, email) VALUES ('wanger', '13808710003', 'wanger@test.com')");
            //...
        }
     
        //commit
        $pdo->commit();
        echo "insert records successful";
        //return true;
    }
    catch(PDOException $e)
    {
        // failure, rollback
        $pdo->rollback();
        echo $sql . "::<br>\r\n" . $e->getMessage();
        //return false;
    }
     
    $pdo = null;
    
    ?>

不开启事务，是每个for循环就是个事务，1w个循环，就要开关1w个事务。当然慢了。声明了事务，就只用一个事务，所以快。

**You can change this value on the server by setting the max\_allowed\_packet’ variable.**

出现上面的错误是因为数据库表的** max\_allowed\_packet**这个配置没配置足够大，因为默认的为4M的，后来我调为100M就没报错了

set global max_allowed_packet = 100*1024*1024*

1.setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
    When you receive error like this:
    General error: 2014 Cannot execute queries while other unbuffered queries are active.
    This means that you need to uncomment the following:
    On line 137 of “lib/Varien/Db/Adapter/Pdo/Mysql.php”, find:
    #$this->_connection->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
    and then uncomment it out, so it should be
    $this->_connection->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
    
    
    2.将查询的结果集fetchAll()以释放PDOStatement:
    PDO::query里的一段话：
    
    If you do not fetch all of the data in a result set before issuing your next call to PDO::query(), your call may fail. Call PDOStatement::closeCursor() to release the database resources associated with the PDOStatement object before issuing your next call to PDO::query().

--------------------

在执行的过程中，报出如下的错误：  
.1. SQLSTATE\[22001\]: String data, right truncated: 1406 Data too long for column 'cover' at row 479601 ...  
原因是：插入字段长度超过设定的长度  
cover 字段是用来存取 封面图片Url的.  
  
 \`cover\` varchar(160) DEFAULT NULL,改成 \`cover\` varchar(180) DEFAULT NULL,也就是把字段长度改成20即可。  
或者是  
原因是：  
在my.ini里找到sql-mode=”STRICT\_TRANS\_TABLES,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION”  
把其中的STRICT\_TRANS\_TABLES,去掉,或者把sqlmode=STRICT\_TRANS\_TABLES,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION  
注释掉，然后重启mysql就ok了

--------------------

2. SQLSTATE\[42000\]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2015-10-15 17:41:44', '', 'http://mp.weixin.qq.com/s?\_\_biz=MzU2ODU0OTk4MA==&mid=' at line 2

解决方法： 出现此种错误，一般是SQL 语句有问题，目前只有先尝试打印错误来看。

$query = "INSERT INTO $useTable(artID, gzh_en, title, cover, summary, oriDate, content, ori_url, read_url)
            SELECT '$artID', '$gzh_en', '$title', '$cover', '$summary', '$lastModified', '$content', '$ori_url', '$read_url'
            FROM dual
            WHERE NOT EXISTS (".$existSqlStr_suffix.")";
    
    $result = $bdd->exec($query);
    if ($result === FALSE) {
        print_r($bdd->errorInfo());
        die("Query: " . $query);
    }

--------------------

乐意黎

2018-12-10