插入值：准备好的语句或多值查询？
在您看来，从性能的角度来看，在表中插入多个值的最佳解决方案是什么？
1 - 使用准备好的语句：
$usersId = Users::getAllId($this->sql);
$prep = $this->sql->prepare('INSERT INTO notification_actualites (idUser,idNews) VALUES(:idU,:idN)');
foreach($usersId as $idU)
{
$prep->execute(array(
':idU' => $idU,
':idN' => $idN
));
}
2 - 或使用多个值查询：
$usersId = Users::getAllId();
$values='';
foreach($usersId as $id)
{
$values.='(\''.$id.'\','.$idActu.'),';
}
$values = substr($values,0,strlen($values)-1);
$this->sql->query('INSERT INTO notification_actualites VALUES'.$values);
安全方面在这里不是问题，在这两种情况下，代码都经过调整以防止 SQL 注入。
一个经过充分论证的答案将不胜感激：）
谢谢
In your opinion and from a performance point of view, what is the best solution to insert multiple values in a table ?
1 - With Prepared statement :
$usersId = Users::getAllId($this->sql);
$prep = $this->sql->prepare('INSERT INTO notification_actualites (idUser,idNews) VALUES(:idU,:idN)');
foreach($usersId as $idU)
{
$prep->execute(array(
':idU' => $idU,
':idN' => $idN
));
}
2 - Or with a multiple values query :
$usersId = Users::getAllId();
$values='';
foreach($usersId as $id)
{
$values.='(\''.$id.'\','.$idActu.'),';
}
$values = substr($values,0,strlen($values)-1);
$this->sql->query('INSERT INTO notification_actualites VALUES'.$values);
The security aspect is not a problem here , in both case, the code is adapted to prevent sql injection.
A well-argued answer would be appreciated :)
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我更喜欢后面的方法。每个数据库请求都必须发送到数据库服务器并接收结果 - 这需要时间，特别是当数据库服务器运行在不同的计算机上时。
time_to_prepare_query + time_sending_query + time_to_executing_query + time_receiving_results + time_processing_results
使用第二种方法时，我不必计算 time_sending_query 和 time_receiving_results n 次（n 是发送的行数到数据库）。
另一方面，您对解决可能出现的单个错误（例如重复键（尽管
ON DUPLICATE KEY可以为您省去一些麻烦）、错误值等）的控制权较小。与往常一样，您方法的选择将取决于您的应用。
I prefer the later method. Every database request has to be sent to the database server and receive the results - that takes time, especially if the database server is running on a different machine.
time_to_prepare_query + time_sending_query + time_to_executing_query + time_receiving_results + time_processing_results
When using the second method, I don't have to count the time_sending_query and time_receiving_results n times (n being the number of rows sent to the db).
On the other hand, you have less control over resolving the possible individual errors that might arise (like duplicate keys (although
ON DUPLICATE KEYcan save you some trouble), bad values etc).The choice of your method will - as always - depend on your application.