mysql 批量插入更新-白红宇

mysql 批量插入更新

阅读量：4360 次

发布时间：2019-06-07

本文共 4569 字，大约阅读时间需要 15 分钟。

<?php

ini_set

(

"max_execution_time"

,

"1800"

);

/**

* insert 10000条数据

* T1() 164.98570299149 //只循环 $sth->bindValue();$sth->execute();

* T2() 365.94625711441 //循环 $sth = $dbh->prepare();$sth->bindValue();$sth->execute();

* T3() 1.2607250213623 //拼装批量SQL，直接执行

*

* update 10000条数据

* u1() 162.98394322395 //只循环 $sth->bindValue();$sth->execute();

* u2() 193.30426812172 //循环 $sth = $dbh->prepare();$sth->bindValue();$sth->execute();

*  u3()   0.32934808731079     //拼装批量SQL，直接执行（where条件为主键：0.32934808731079，where条件不为主键：1.28左右）

*

* CREATE TABLE IF NOT EXISTS `new_table` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) NOT NULL COMMENT '姓名',

`age` int(11) NOT NULL COMMENT '年龄',

`addtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '添加时间',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=10001 ;

*

*/

class

db {

function

getDb() {

$dsn

=

'mysql:dbname=test;host=127.0.0.1'

;

$user

=

'root'

;

$pass

=

'123456'

;

$dbh

=

new

PDO(

$dsn

,

$user

,

$pass

);

return

$dbh

;

}

class

test {

function

t1() {

$db

=

new

db ();

$dbh

=

$db

->getDb();

$start

= microtime(true);

$sql

=

"INSERT INTO `new_table` (`name` ,`age`)VALUES (:name, :age)"

;

$sth

=

$dbh

->prepare(

$sql

,

array

(

PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY

));

for

(

$i

= 0;

$i

< 10000;

$i

++) {

$sth

->bindValue(

':name'

,

'zf_'

.

$i

);

$sth

->bindValue(

':age'

,

$i

);

$sth

->execute();

}

$end

= microtime(true);

echo

$end

-

$start

;

}

function

t2() {

$db

=

new

db ();

$dbh

=

$db

->getDb();

$start

= microtime(true);

for

(

$i

= 0;

$i

< 10000;

$i

++) {

$sql

=

"INSERT INTO `new_table` (`name` ,`age`)VALUES (:name, :age)"

;

$sth

=

$dbh

->prepare(

$sql

,

array

(

PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY

));

$sth

->bindValue(

':name'

,

'zf_'

.

$i

);

$sth

->bindValue(

':age'

,

$i

);

$sth

->execute();

}

$end

= microtime(true);

echo

$end

-

$start

;

}

function

t3() {

$db

=

new

db ();

$dbh

=

$db

->getDb();

$start

= microtime(true);

$dbh

->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$dbh

->beginTransaction();

try

for

(

$j

= 0;

$j

< 100;

$j

++) {

$sql

=

"INSERT INTO `new_table` (`name` ,`age`)VALUES "

;

$sql_arr

=

array

();

$sql_other

=

''

;

for

(

$i

= 0;

$i

< 100;

$i

++) {

$sql_arr

[] =

"('zf_"

.

$m

.

"', "

.

$m

.

")"

;

}

$sql_other

= implode(

','

,

$sql_arr

);

$sql

.=

$sql_other

;

$sth

=

$dbh

->prepare(

$sql

);

$sth

->execute();

}

$dbh

->commit();

}

catch

(Exception

$e

) {

$dbh

->rollBack();

echo

$e

->getMessage() .

'<br>'

;

}

$end

= microtime(true);

$use_time

=

$end

-

$start

;

echo

'use time:'

.

$use_time

;

}

function

u1() {

$db

=

new

db ();

$dbh

=

$db

->getDb();

$dbh

->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$start

= microtime(true);

try

$sql

=

"update `new_table` set `name`=:name where id=:id"

;

$sth

=

$dbh

->prepare(

$sql

,

array

(

PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY

));

for

(

$i

= 1;

$i

<= 10000;

$i

++) {

$sth

->bindValue(

':name'

,

'zz_'

.

$i

);

$sth

->bindValue(

':id'

,

$i

);

$sth

->execute();

echo

$i

.

' '

;

}

catch

(Exception

$e

) {

echo

$e

->getMessage();

}

$end

= microtime(true);

$use_time

=

$end

-

$start

;

echo

'use time:'

.

$use_time

;

}

function

u2() {

$db

=

new

db ();

$dbh

=

$db

->getDb();

$dbh

->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$start

= microtime(true);

try

for

(

$i

= 1;

$i

<= 10000;

$i

++) {

$sql

=

"update `new_table` set `name`=:name where `id`=:id"

;

$sth

=

$dbh

->prepare(

$sql

,

array

(

PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY

));

$sth

->bindValue(

':name'

,

'zf_'

.

$i

);

$sth

->bindValue(

':id'

,

$i

);

$sth

->execute();

}

catch

(Exception

$e

) {

echo

$e

->getMessage();

}

$end

= microtime(true);

$use_time

=

$end

-

$start

;

echo

'use time:'

.

$use_time

;

}

function

u3() {

$db

=

new

db ();

$dbh

=

$db

->getDb();

$start

= microtime(true);

$m

= 1;

$dbh

->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$dbh

->beginTransaction();

try

for

(

$j

= 0;

$j

< 100;

$j

++) {

$sql

=

"update `new_table` set name='zf' where "

;

$sql_arr

=

array

();

$sql_other

=

''

;

for

(

$i

= 0;

$i

< 100;

$i

++) {

$sql_arr

[] =

"id="

.

$m

;

$m

++;

}

$sql_other

= implode(

' or '

,

$sql_arr

);

$sql

.=

$sql_other

;

//echo $sql . '<br>';

$sth

=

$dbh

->prepare(

$sql

);

$sth

->execute();

}

$dbh

->commit();

}

catch

(Exception

$e

) {

$dbh

->rollBack();

echo

$e

->getMessage() .

'<br>'

;

}

$end

= microtime(true);

$use_time

=

$end

-

$start

;

echo

'use time:'

.

$use_time

;

}

$T

=

new

test ();

$T

->t1();

//$T->t2();

//$T->t3();

//$T->u1();

//$T->u2();

//$T->u3();

?>

参考：http://bbs.csdn.net/topics/390114674

转载于:https://www.cnblogs.com/faster/p/5431871.html

你可能感兴趣的文章

Python 键盘记录

查看>>

HDU 1381 Crazy Search

Android-Activity的启动模式

查看>>

禅道项目管理系统整合Selenium IDE的思路

查看>>

linux-nohup命令

查看>>

[LeetCode OJ] Roman to Integer

putty network error:software caused connection abort

Leetcode: Binary Tree Maximum Path Sum

查看>>

通过虚拟环境创建并开始一个django

查看>>

关于 input[type="button"] , button

查看>>

Android ViewDragHelper全然解析自己定义ViewGroup神器

查看>>

c++ 基础 const char* 转 char*

查看>>