while循环中的PHP mysqli_multi_query事务

Question

PHP 版本 5.3.3，mysql 5.0.95

需要将数据从现有表迁移到两个相同的表。原始数据在插入到两个新表之前需要解析。 (该代码未显示，因为我希望隔离此问题。)想要使用事务来确保新表是相同的。

task_id 字段在 test_timecard 中是自增的，在 test_timecar_2 中是无符号的 mediumint。

两个表的引擎都是 InnoDB。

单独查询有效:

$timecard_data_results = array();
$fill_old_data_array_def = " SELECT task_id, company_id, employee_id, location, task_name, task_start_time, task_end_time, tccomment FROM timecard WHERE company_id = '" . $company_request . "' AND employee_id = '" . $employee_request . "' AND DATE(task_start_time) < '" . $new_text_format_date . "' AND (DATE(task_end_time) > '2014-12-31' OR DATE(task_end_time) = '2000-01-01') ORDER BY task_start_time";
$timecard_data_results = mysqli_query($conn, $fill_old_data_array_def);

while($timecard_record = mysqli_fetch_assoc($timecard_data_results)) {

    $company_id = $timecard_record['company_id'];
    $employee_id = $timecard_record['employee_id'];
    $location = $timecard_record['location'];
    $task_name = $timecard_record['task_name'];
    $task_start_time = $timecard_record['task_start_time'];
    $task_end_time = $timecard_record['task_end_time'];
    $tccomment = $timecard_record['tccomment'];

    $troubleshoot_def = "INSERT INTO test_timecard (company_id, employee_id, location, task_name, task_start_time, task_end_time, tccomment) VALUES ('" . $company_id . "', '" . $employee_id . "', '" . $location . "', '" . $task_name . "', '" . $task_start_time . "', '" . $task_end_time . "', '" . $tccomment . "')"; 
    $troubleshoot_2_def = "INSERT INTO test_timecard_2 (task_id, company_id, employee_id, location, task_name, task_start_time, task_end_time, tccomment) VALUES (LAST_INSERT_ID(), '" . $company_id . "', '" . $employee_id . "', '" . $location . "', '" . $task_name . "', '" . $task_start_time . "', '" . $task_end_time . "', '" . $tccomment . "')";     
    $troubleshoot = mysqli_query ($conn, $troubleshoot_def);
    $troubleshoot_2 = mysqli_query ($conn, $troubleshoot_2_def);
}

使用 mysqli_multi_query 的事务只向两个表插入一行。没有错误报告。

$timecard_data_results = array();
$fill_old_data_array_def = " SELECT task_id, company_id, employee_id, location, task_name, task_start_time, task_end_time, tccomment FROM timecard WHERE company_id = '" . $company_request . "' AND employee_id = '" . $employee_request . "' AND DATE(task_start_time) < '" . $new_text_format_date . "' AND (DATE(task_end_time) > '2014-12-31' OR DATE(task_end_time) = '2000-01-01') ORDER BY task_start_time";
$timecard_data_results = mysqli_query($conn, $fill_old_data_array_def);

while($timecard_record = mysqli_fetch_assoc($timecard_data_results)) {

    $company_id = $timecard_record['company_id'];
    $employee_id = $timecard_record['employee_id'];
    $location = $timecard_record['location'];
    $task_name = $timecard_record['task_name'];
    $task_start_time = $timecard_record['task_start_time'];
    $task_end_time = $timecard_record['task_end_time'];
    $tccomment = $timecard_record['tccomment'];

    $troubleshoot_def = "START TRANSACTION; INSERT INTO test_timecard (company_id, employee_id, location, task_name, task_start_time, task_end_time, tccomment) VALUES ('" . $company_id . "', '" . $employee_id . "', '" . $location . "', '" . $task_name . "', '" . $task_start_time . "', '" . $task_end_time . "', '" . $tccomment . "'); INSERT INTO test_timecard_2 (task_id, company_id, employee_id, location, task_name, task_start_time, task_end_time, tccomment) VALUES (LAST_INSERT_ID(), '" . $company_id . "', '" . $employee_id . "', '" . $location . "', '" . $task_name . "', '" . $task_start_time . "', '" . $task_end_time . "', '" . $tccomment . "'); COMMIT;"; 
    $troubleshoot = mysqli_multi_query ($conn, $troubleshoot_def);
}    

难倒了。

Answer 1

$troubleshoot_def = "INSERT INTO test_timecard (company_id, employee_id, location, task_name, task_start_time, task_end_time, tccomment) VALUES ('" . $company_id . "', '" . $employee_id . "', '" . $location . "', '" . $task_name . "', '" . $task_start_time . "', '" . $task_end_time . "', '" . $tccomment . "')"; 
    $troubleshoot_2_def = "INSERT INTO test_timecard_2 (task_id, company_id, employee_id, location, task_name, task_start_time, task_end_time, tccomment) VALUES (LAST_INSERT_ID(), '" . $company_id . "', '" . $employee_id . "', '" . $location . "', '" . $task_name . "', '" . $task_start_time . "', '" . $task_end_time . "', '" . $tccomment . "')";     

这里有很多问题。首先是将几乎相同的数据插入到两个不同的表中没有任何意义。事实上，当操作完成时，您会得到三个数据几乎相同的表，即 test_timecard_2、test_timecard 和 timecard

其次，您要插入未转义的数据。由于数据来自您的另一个表，因此 sql 注入(inject)的可能性不大，但查询仍然有可能失败。具体来说，我在谈论这样的代码:

VALUES ('" . $company_id . "', '" . $employee_id . "', '" . $location . "', '" . $task_name . "', '" . $task_start_time . "', '" . $task_end_time . "', '" . $tccomment . "')"; 

第三，您几乎不需要执行 SELECT - LOOP - INSERT，因为 mysql 具有内置的 INSERT SELECT 命令。

INSERT INTO test_timecard (company_id, employee_id, location, task_name, task_start_time, task_end_time, tccomment) 
SELECT * FROM time_card

注意让列正确(以上只是代码两部分的复制粘贴)