How to batch insert data in mysql

Mysql batch insert data method: 1. Loop insertion, syntax format "for($i=1;$i<=condition;$i){//insert statement}"; 2. Reduce connection resources , splice a sql; 3. Use stored procedures; 4. Use "MYSQL LOCAL_INFILE".

The operating environment of this tutorial: windows7 system, mysql8.0.22 version, thinkpad t480 computer.

Mysql batch insert data method:

Method 1: Loop insertion

This is also the most common way, if The amount of data is not very large and can be used, but the resources to connect to the database will be consumed every time.

The general thinking is as follows

(I write pseudo code here, the specific writing can be combined with your own business logic or framework syntax)

for($i=1;$i<=100;$i++){
$sql = &#39;insert...............&#39;;
//querysql
}
foreach($arr as $key => $value){
$sql = &#39;insert...............&#39;;
//querysql
}
while($i <= 100){
$sql = &#39;insert...............&#39;;
//querysql
$i++
}

Because it is too common and not difficult at the same time At the same time, it is not what I am mainly writing today, so I won’t say much here

Method 2: Reduce connection resources and splice a sql

The pseudo code is as follows

//这里假设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

It is not a big problem to write 10,000 normal inserts like this, unless the data is very long, it is enough to handle ordinary batch insertion, such as: batch generation of card numbers, batch generation of random codes, etc.

Method 3: Use stored procedures

I happen to use this in my hand to pay the sql. You can combine the specific business logic yourself.

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();

This is just a test code, you can define the specific parameters yourself

I am inserting 80,000 items at a time. Although it is not much, the amount of data in each item is large, and there are many varchar4000 and text field

takes 6.524s

Method 4: Use MYSQL LOCAL_INFILE

I am currently using this, so by the way, I also put the pdo code Post it for your reference

//设置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 &#39;" . $myFile . "&#39; INTO TABLE ";
      $sql .= "tablename (" . implode(&#39;,&#39; ,$arr_keys) . ")";
      $pdo = $this->pdo_local_info ();
      $res = $pdo->exec($sql);
      if (!$res) {
           //TODO 插入失败
      }
      @unlink($my_file);
  }
}

More related free learning recommendations:mysql tutorial(Video)

The above is the detailed content of How to batch insert data in mysql. For more information, please follow other related articles on the PHP Chinese website!