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

<?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

发布了430 篇原创文章 · 获赞 415 · 访问量 925万+
展开阅读全文

使用PDO准备的MySQL语句,从数据数组中将多个值插入多个列

08-14

<div class="post-text" itemprop="text"> <p>I'm new to PHP and MySQL (and programming in general) and I'm trying to create a generic database handler class called <code>Database_Handler</code> that will help me manage basic things like insert, delete, select, update, etc.</p> <p>I'm working on a member function to handle insert, currently. In my insert function, I would like to <a href="http://www.php.net/manual/en/pdo.prepared-statements.php" rel="nofollow noreferrer">build a prepared PDO insert statement</a> and execute it.</p> <p>Assume that somewhere in my application, I have called the insert function as follows:</p> <pre><code>$table = "books"; $cols = array('author', 'title', 'pubdate'); $values = array('Bob Smith', 'Surviving the Zombie Apocalypse', '2010'); $db_handler->insert($table, $cols, $values); </code></pre> <p>How can I use the data from <code>$table</code>, <code>$cols</code> and <code>$values</code> to build a prepared PDO insert statement? Here's my first effort, based on <a href="https://stackoverflow.com/questions/4629022/how-to-insert-an-array-into-a-single-mysql-prepared-statement-w-php-and-pdo">an answer from "How to insert an array into a single MySQL Prepared statement w/ PHP and PDO"</a>.</p> <pre><code>public function insert($table, $cols, $values){ $numvalues = count($values); $placeholder = array(); for($i=0; $i<$numvalues; $i++) $placeholder[$i] = '?'; $sql = 'INSERT INTO '. $table . '(' . implode(",", $cols) . ') '; $sql.= 'VALUES (' . implode("," $placeholder) . ')"'; $stmt = $this->dbh->prepare($sql); $for($i=0; $i<$numvalues; $i++) $stmt->bindParam($i+1, $values[$i]) $stmt->execute(); } </code></pre> <p>I don't think this will work, but maybe it will give you an idea of what I want to do. I'm a little confused because the <a href="http://www.php.net/manual/en/pdo.prepared-statements.php" rel="nofollow noreferrer">example given on the php.net manual</a> is: </p> <pre><code>$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)"); $stmt->bindParam(1, $name); $stmt->bindParam(2, $value); // insert one row $name = 'one'; $value = 1; $stmt->execute(); </code></pre> <p>It seems like they are sending <code>$name</code> as a parameter to the <code>bindParam()</code> funciton, then assigning a value to <code>$name</code> afterwards? What is the value of <code>$name</code> when it's sent to <code>bindParam()</code>? Or does <code>bindParam()</code> just associate a parameter with a variable, without taking that variable's data - allowing <code>execute()</code> to handle that part?</p> </div> 问答

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 编程工作室 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览