MySQL php插入花了很长时间

Question

我想在我的表中插入0到10000000 (1千万)的主键。

我的桌子如下：

p_key-->      (INT[10]) AUTO_INCREMENT PRIMARY KEY UNIQUE
hash_value--> (SMALLINT)
req_count--> (SMALLINT)

仅添加100条记录就需要11秒才能达到500条记录，而30秒的超时时间已经被跨越。(wamp服务器localhost php mysql)

for ($i=0;$i<100;$i++){
    $query='INSERT INTO hash_table (hash_value, req_count) VALUES (0,0)';
    $result=$con->query($query);
    if(!$result){
        echo ("Error message:".$con->error." \n");
        exit();
    }
  }

我是不是做错了什么。

Answer 1

您应该在一个SQL查询中插入多个记录。

例如：

$values='';

for ($i=0;$i<100;$i++){
    $values.='(0,0),';
}

$values=substr($values,0,-1);

$query='INSERT INTO hash_table (hash_value, req_count) VALUES '.$values;
$result=$con->query($query);
if(!$result){
    echo ("Error message:".$con->error." \n");
    exit();
}

Answer 2

另一个选项，在一个SQL语句中执行该操作：-

INSERT INTO hash_table (hash_value, req_count)
SELECT 0, 0
FROM
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)units,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)tens,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)hundreds,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)thousands,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)tensthousands,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)hundredthousands,
(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)millions,
(SELECT 0 i UNION SELECT 1)tensmillions
WHERE (units.i +
        tens.i * 10 +
        hundreds.i * 100 +
        thousands.i * 1000 +
        tensthousands.i * 10000 +
        hundredthousands.i * 100000 +
        millions.i * 1000000 +
        tensmillions.i * 10000000) BETWEEN 0 AND 10000000