代码1:for循环批量插入100W数据
<?php set_time_limit(0); $servername = "localhost"; $username = "root"; $password = "123456"; $dbname = "learn"; // 创建连接 $conn = mysqli_connect($servername, $username, $password, $dbname); // 检测连接 if (!$conn) { die("Connection failed: " . mysqli_connect_error()); } $sql = "INSERT INTO shop_goods (title, price) VALUES ('商品1',500)"; for ($i=0; $i <1000000 ; $i++) { mysqli_query($conn, $sql); } //计算执行时长 $runtime=time()-$_SERVER['REQUEST_TIME']; echo $runtime; //1.03分钟 mysqli_close($conn); ?>
完成时间:1.03分钟
代码2:将所有插入合并为1条Mysql语句,由于语句较长,请执行mysql命令:set global max_allowed_packet = 2*1024*1024*10;避免超长Mysql无法执行。
<?php set_time_limit(0); $servername = "localhost"; $username = "root"; $password = "123456"; $dbname = "learn"; // 创建连接 $conn = mysqli_connect($servername, $username, $password, $dbname); // 检测连接 if (!$conn) { die("Connection failed: " . mysqli_connect_error()); } $sql = "INSERT INTO shop_goods (title, price) VALUES "; for ($i=0; $i <1000000 ; $i++) { $sql.="('商品1',500),"; } $sql = substr($sql,0,strlen($sql)-1); mysqli_query($conn, $sql); //计算执行时长 $runtime=time()-$_SERVER['REQUEST_TIME']; echo $runtime; //4秒 mysqli_close($conn); ?>
完成时间:4秒