PHP mysql 批量插入数据

php向mysql插入百万数据只需不到一分钟时间，之间还可以优化

<?php

set_time_limit(0);

$dsn = 'mysql:dbname=test;host=localhost;port=3306';

$username = 'root';

$password = '123456';

try {

$pdo = new PDO($dsn, $username, $password); // also allows an extra parameter of configuration

} catch(PDOException $e) {

die('Could not connect to the database:<br/>' . $e);

}

$pdo->beginTransaction();

try{

$c = 0;

$values='';

for($i=0;$i<100000000;$i++){

$str="QWERTYUIOPASDFGHJKLZXCVBNM1234567890qwertyuiopasdfghjklzxcvbnm";

str_shuffle($str);

$name=substr(str_shuffle($str),26,6);

//$content = substr(str_shuffle($str),1,60);

$sex = ['男','女'];

$age = [10,20,30,35,32,12,16,18,19,50,56,59,60,68,97,99,33,75,95,28,22,26,28,30,25];

$prov = ['四川','云南','北京','上海','广东','湖南','浙江'];

$city = ['成都','昆明','北京','上海','广州','湖南','浙江','深圳','杭州','合肥','天津','绵阳'];

$prov_name = $prov[array_rand($prov)];

$city_name = $prov[array_rand($prov)];

$sex_name = $sex[array_rand($sex)];

$age_name = $age[array_rand($age)];

// $res = $pdo->prepare("insert into test (id,title,age,sex,prov,city,content) values (:id,:title,:age,:sex,:prov,:city,:content)");

// $res->bindValue('id',UUID());

// $res->bindValue('title',$name);

// $res->bindValue('age',$age[array_rand($age)]);

// $res->bindValue('sex',"".$sex[array_rand($sex)]."");

// $res->bindValue('prov',"".$prov[array_rand($prov)]."");

// $res->bindValue('city',"".$city[array_rand($city)]."");

// $res->bindValue('content',UUID().UUID());

$values .= ',("'.UUID().'","'.$name.'",'.$age_name.',"'.$sex_name.'","'.$prov_name.'","'.$city_name.'","'.$name.'")';

$values = ltrim($values,',');

if ($c%10000==0){

$pdo->exec('insert into test (uuid,title,age,sex,prov,city,content) value '.$values);

$values='';

}

$c++;

//$res->execute();

if ($i%200000==0){

$pdo->commit();

$pdo->beginTransaction();

}

}

$pdo->commit();

}catch (Exception $e){

$pdo->rollBack();

echo $e->getMessage();

}

function UUID(){

$uuid = '';

if (function_exists('uuid_create') === true){

$uuid = uuid_create(1);

}else{

$data = openssl_random_pseudo_bytes(16);

$data[6] = chr(ord($data[6]) & 0x0f | 0x40);

$data[8] = chr(ord($data[8]) & 0x3f | 0x80);

$uuid = vsprintf('%s%s-%s-%s-%s-%s%s%s', str_split(bin2hex($data), 4));

}

return $uuid;

}