MySQL：在插入后选择失败，找不到刚插入的行

Question

我在我的程序中没有使用事务，所以我认为所有的数据库操作都应该立即提交。

我的php程序查找一个表中的值，如果找到则返回id。如果没有找到，则写入一行新数据。之后它重复执行，应该能够找到我刚刚插入的数据，但实际上没有找到，所以会出现重复错误。

如何强制mysql写入数据，以便在插入后获得有效结果？我尝试过清空表格，但这并没有改变我的结果。

更多细节:

我原以为这是一个普遍的mysql问题，但似乎与使用Cakephp的Model Query有关。

我正在使用控制器将CSV文件解析成多个表格，然后即时绑定模型并使用查询调用将数据插入表格。对于应用程序的其他部分，我使用CakePHP，但解析功能只是一种临时功能，所以我没有花费很多时间来使其优雅。

导入模型没有关联的表格，因此我手动绑定到Tracking模型。

下面是相关查询代码的意思：

    $this->Import->bindModel(array('hasOne' => array('Tracking')));

    $insert =<<<DONE
INSERT INTO tracking (id, created, employee_id, client_id, borrower_name, loan_number,
                  loan_amount, activity_id, program_id, rate_id, lock_period,
                  lo_price, investor_price,  committed_price, purchase_advice_price,
                  lock_type, investor_id, time_taken, notes,audited,audit_notes,
                  import_notes, import_error, uniqueness)
                  VALUES (NULL, '%s', %d,  %d,  '%s', '%s',
                          %.2f, %d, %d,  %d, %d,
                          %.5f, %.5f, %.5f, %.5f,
                          %d, %d, %d, '%s', %d, '%s',
                          '%s', %d, %d)
DONE;
while ($row = fgetscsv($fh))
    # Lots of processing deleted

    if (! $this->_exists($row))
    {
       $sql = vsprint($insert, $row);
       $this->Import->Tracking->query($sql); # This is where I'll get a duplicate error
    }
} # end of function

# I've created a unique index on columns that should be unique and I do the query on    
# them here.
private _exists($mydata)
{
       $find = "SELECT id FROM tracking WHERE created = '%s' AND employee_id = %d" AND
            activity_id = $d AND ...";
       $sql = sprintf($find, $mydata[0], $mydata[1]);
       return $this->Import->Tracking->query($sql);
}

看起来问题似乎与Cakephp及其对模型查询的处理有关。

我可能可以将此代码从Cake中移出并使用一个独立的PHP程序，但既然我已经在使用Cakephp，我想我会把导入模型放进去。

额外的评论：选择是正确的，但我没有逐字记录下来，因为它很长，我用省略号表示了。它可以正常工作，因为我可以将其复制/粘贴到mysql会话中并选择数据。第二次运行它，它将找到数据。

听起来Cakephp可能会将整个事物包装成一个大的事务，直到退出控制器才提交。这是否解释了为什么第二次运行它会发现那些行？

我想我需要弄清楚如何让Cakephp提交这些插入或者也许我不应该使用Model->query来完成这个操作，或者不要在我的项目的这一部分中使用Cakephp。

====

谢谢！

- Panama

---

展示你的代码。如果没有看到它，我们怎么知道呢？ - peterm

我猜你正在通过id进行选择。打印出id并确保你实际上获得了插入的id。 - Hanlet Escaño

我们需要一些代码。除非我们知道您的代码是正确的还是错误的，否则我们就只能胡乱猜测了。 - Scott Helme

重复错误意味着它知道该行已经存在，而不是缓存问题。这意味着您的选择语句无效。 - immulatin

很可能你的SELECT语句有误。你的INSERT和SELECT语句是什么样子的？ - Brendan Long

Answer 1

在CakePHP 2.5.5上，仅供将来参考（即使是对我自己，如果我忘记了解决方案并在将来找到自己的答案...嗨，我自己！）好的，这个问题是由于查询缓存引起的。 解决方法：

$this->Import->Tracking->query($sql, false);

添加false参数将关闭查询缓存。 只需阅读方法http://api.cakephp.org/2.5/source-class-Model.html#3363-3366上的注释。

- JorgeObregon

---

啊哈！我想到了，它在某个地方缓存了。太棒了。还有，“嗨”未来的Jorge。我怀疑未来的Panama也会回到这里！ - Panama

Answer 2

您的$find不是一个有效的字符串：

   $find = "SELECT id FROM tracking WHERE created = "%s" AND employee_id = %d" AND activity_id = $d AND ...";
   //                                               ^ this is terminating the string

你需要用单引号将其包裹起来，以使其成为字面字符串。就像这样：

   $find = 'SELECT id FROM tracking WHERE created = "%s" AND employee_id = %d" AND activity_id = $d AND ...';

或者在字符串中转义引号（虽然在这种情况下不确定为什么要这样做）：

   $find = "SELECT id FROM tracking WHERE created = \"%s\" AND employee_id = \"%d\" AND activity_id = $d AND ...";

- immulatin

---

是的，抱歉，当我从我的程序中拷贝代码时，我将其转录错误了。程序中的选择是正确的。 - Panama

Answer 3

你可能在寻找LAST_INSERT_ID()函数

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

- zod

---

他并不是特别想找到刚插入的行，他只是在另一个插入之前检查重复数据，但是失败了。 - Barmar

Answer 4

如果您的数据表上有一个唯一约束（根据代码中的评论，我可以看出您确实有这样一个约束），那么您可以使用INSERT IGNORE。

INSERT IGNORE INTO tracking (...)
VALUES (...)

这说明在进行INSERT操作之前需要执行SELECT操作。

---

另一个要考虑的选择是更改处理CSV文件的方式，具体如下：

1. 创建一个没有约束条件的暂存表，包含导入数据所需的所有必要列（最好是VARCHAR类型）和自动递增的id字段
2. 使用LOAD DATA INFILE以最有效的方式将CSV文件加载到暂存表中
3. 现在，您可以使用所有SQL工具轻松地在暂存表中清理、规范化、转换、验证参考和实际表中的数据
4. 将数据从暂存表插入到实际表中
5. 清空您的暂存表

您甚至可以将p3-5封装成一个存储过程，进一步简化客户端代码。

- peterm

---

@Panamah 这有帮助吗？ - peterm

Answer 5

我认为我遇到的问题是CakePHP在Query方法中的处理。我重新编写了控制器，使其可以动态地创建与模型的关联，并使用内置的模型保存方法。

这是一个更加优雅的解决方案，即使需要更多的编码。它还让我免于深入研究CakePHP并弄清楚查询方法在做什么的麻烦。

感谢大家提供的想法！

- Panama