如何使用 PHP 一次性在 SQL 中运行多个插入查询? [英] How to run multiple insert query in SQL using PHP in one go?

问题描述

我想运行 mysql 查询以从自定义 html 表单插入数据.在这里我必须插入多组数据，一些数据到一个表，一些数据到另一个.目前我正在使用简单的方法使用 jquery ajax 将数据发送到 php 页面，并一个接一个地运行多个 mysqli_query() 函数.但是我想当用户量很大的时候，就会出现速度方面的问题.所以任何人都可以建议我一个更好的方法来做同样的事情.

数据库中有 5 个表，每个表有 7 到 10 列，每次都需要获取不同的数据集.

我想仅在前一个插入查询成功完成时运行每个查询.这就是为什么我每次都检查结果然后运行下一个查询的原因，这让我感受到了大量用户群的速度问题.

解决方案

问题是，我需要将数据插入到第一个表中，如果成功插入，则只对第二个表运行查询.

这意味着您需要一个交易.>

事务是一组查询，它们要么全部执行正常，要么失败——它们都失败了.这是为了确保您的表格中不会出现垃圾数据.

不要

• 不要使用多查询.
• 不要使用 mysql_* 函数.
• 不要使用批量插入.

告诉你这样做的人完全不知道他们在做什么，忽略他们.

做

• 使用PDO
• 使用准备好的语句
• 准备语句一次，多次使用它们

示例代码 - 不要复制粘贴

$dsn = 'mysql:dbname=testdb;host=127.0.0.1;charset=utf8mb4';$user = 'dbuser';$password = 'dbpass';$pdo = new PDO($dsn, $user, $password);$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);$insert['first'] = $pdo->prepare("INSERT INTO table SET col1 = :val, col2 = :val2");$insert['second'] = $pdo->prepare("INSERT INTO another_table SET col1 = :val, col2 = :val2");$pdo->beginTransaction();$insert['first']->bindValue(':val', '你的价值');$insert['first']->bindValue(':val2', 'anothervalue');$insert['first']->execute();$insert['second']->bindValue(':val', '你的价值');$insert['second']->bindValue(':val2', 'anothervalue');$insert['second']->execute();$pdo->commit();

上面的代码只有在两次插入都成功时才会将数据保存在两个表中.

I want to run mysql queries to insert data from a custom html form. Here I have to insert multiple set of data, some data to one table and some to other. Currently I am using the simple approach to send data to php page using jquery ajax and run multiple mysqli_query() functions one after another. But I guess when there will be large number of users, there will be problems related to speed. So can anyone suggest me a better way to do the same.

There are 5 tables in the database and each table has 7 to 10 columns that need to get different set of data, every time.

I want to run each query only if the previous insert query is successfully completed. That's why I am checking for the result every time and then running the next query, which makes me feel the issue of speed on large user base.

解决方案

The problem is, I need to insert data to first table and if its successfully inserted then only run query for the second table.

This means you need a transaction.

A transaction is a set of queries that either all execute ok or if one fails - they all fail. This is to ensure you don't end up with crap data in your tables.

Do not

• Do not use multiquery.
• Do not use mysql_* function(s).
• Do not use bulk inserts.

People telling you to do that just have absolutely no clue what they're doing, ignore them.

Do

• Use PDO
• Use prepared statements
• Prepare the statement(s) ONCE, use them MULTIPLE times

Sample code - do NOT copy paste

$dsn = 'mysql:dbname=testdb;host=127.0.0.1;charset=utf8mb4';
$user = 'dbuser';
$password = 'dbpass';

$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$insert['first'] = $pdo->prepare("INSERT INTO table SET col1 = :val, col2 = :val2");
$insert['second'] = $pdo->prepare("INSERT INTO another_table SET col1 = :val, col2 = :val2"); 

$pdo->beginTransaction();

$insert['first']->bindValue(':val', 'your value');
$insert['first']->bindValue(':val2', 'anothervalue');
$insert['first']->execute();

$insert['second']->bindValue(':val', 'your value');
$insert['second']->bindValue(':val2', 'anothervalue');
$insert['second']->execute();

$pdo->commit();

The code above will save the data in two tables ONLY if both inserts are successful.

这篇关于如何使用 PHP 一次性在 SQL 中运行多个插入查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！