mysql大批量插入数据四种方法

mysql⼤批量插⼊数据四种⽅法

⽅法⼀：循环插⼊

这个也是最普通的⽅式，如果数据量不是很⼤，可以使⽤，但是每次都要消耗连接数据库的资源。⼤致思维如下

（我这⾥写伪代码，具体编写可以结合⾃⼰的业务逻辑或者框架语法编写）

for($i=1;$i<=100;$i++){ $sql = 'insert...............'; //querysql}

foreach($arr as $key => $value){$sql = 'insert...............'; //querysql}

while($i <= 100){

$sql = 'insert...............'; //querysql $i++}

因为太过普通同时也没什么难度同时也不是我今天主要写的所以这⾥我不多说⽅法⼆：减少连接资源，拼接⼀条sql伪代码如下

//这⾥假设arr的key和数据库字段同步，其实⼤多数框架中在php操作数据库的时候都是这么设计的$arr_keys = array_keys($arr);

$sql = 'INSERT INTO tablename (' . implode(',' ,$arr_keys) . ') values';$arr_values = array_values($arr);

$sql .= \" ('\" . implode(\"','\" ,$arr_values) . \"'),\";$sql = substr($sql ,0 ,-1);

//拼接之后⼤概就是 INSERT INTO tablename ('username','password') values ('xxx','xxx'),('xxx','xxx'),('xxx','xxx'),('xxx','xxx'),('xxx','xxx'),('xxx','xxx').......

//querysql

这样写正常插⼊⼀万条基本问题不⼤，除⾮数据很长，应付普通的批量插⼊够⽤了，⽐如：批量⽣成卡号，批量⽣成随机码等等。。。⽅法三：使⽤存储过程

这个我⼿⾥正好再⽤这个就把sql付出来，具体业务逻辑⼤家⾃⼰组合⼀下就可以。

delimiter $$$

create procedure zqtest()begin

declare i int default 0;set i=0;

start transaction;while i<80000 do //your insert sql set i=i+1;end while;commit;end$$$

delimiter;call zqtest();

这个也只是个测试代码，具体参数⼤家⾃⾏定义

我这⾥是⼀次插⼊8万条，虽然不多但是，每⼀条数据量都很⼤，有很多varchar4000 和text字段耗时 6.524s

⽅法四：使⽤MYSQL LOCAL_INFILE

这个我⽬前正在使⽤，所以顺便把pdo的代码也复上来，以便⼤家参考

//设置pdo开启MYSQL_ATTR_LOCAL_INFILE/*hs96.cn@gmail.com

public function pdo_local_info (){

global $system_dbserver;

$dbname = 'hs96.cn@gmail.com';

$ip = 'hs96.cn@gmail.com'; $user = 'hs96.cn@gmail.com'; $pwd = 'hs96.cn@gmail.com';

$dsn = 'mysql:dbname=' . $dbname . ';host=' . $ip . ';port=3306'; $options = [PDO::MYSQL_ATTR_LOCAL_INFILE => true]; $db = new PDO($dsn ,$user ,$pwd ,$options); return $db; }

//伪代码如下

public function test(){

$arr_keys = array_keys($arr);

$root_dir = $_SERVER[\"DOCUMENT_ROOT\"] . '/';

$my_file = $root_dir . \"hs96.cn@gmail.com/sql_cache/\" . $order['OrderNo'] . '.sql'; $fhandler = fopen($my_file,'a+'); if ($fhandler) {

$sql = implode(\"\\" ,$arr); $i = 1;

while ($i <= 80000) {

$i++;

fwrite($fhandler ,$sql . \"\\r\\n\"); }

$sql = \"LOAD DATA local INFILE '\" . $myFile . \"' INTO TABLE \"; $sql .= \"tablename (\" . implode(',' ,$arr_keys) . \")\"; $pdo = $this->pdo_local_info (); $res = $pdo->exec($sql); if (!$res) {

//TODO 插⼊失败 }

@unlink($my_file); }}

这个同样每⼀条数据量都很⼤，有很多varchar4000 和text字段耗时 2.160s

以上满⾜基本需求，100万数据问题不⼤，要不数据实在太⼤也涉及分库分表了，或者使⽤队列插⼊了。