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("\t" ,$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 万数据问题不大，要不数据实在太大也涉及分库分表了，或者使用队列插入了。

大家可能会说，代码粘贴上运行不了啊？
我的伪代码 只讲思维 有了自己的思维才能一通百通