mysql - select count(*) 错误的结果，缓存还是什么？

Question

我有一个 sql 查询，我计算行数

SELECT count(*) AS `nr` FROM `ordine` WHERE (ditta_id = '3') AND (YEAR(`data`) = '2012') AND (stato = 'approvato' or stato = 'consegnato') LIMIT 1

我更新了一些行，将 stato 更改为“approvato”，有时 count() 给我正确的数字，有时它给我旧的数字

这是我的一段日志:

2012-03-23T10:42:45+01:00 DEBUG (7): Row id = 1809
2012-03-23T10:42:45+01:00 DEBUG (7): SELECT count(*) AS `nr` FROM `ordine` WHERE (ditta_id = '3') AND (YEAR(`data`) = '2012') AND (stato = 'approvato' or stato = 'consegnato') LIMIT 1
2012-03-23T10:42:45+01:00 DEBUG (7): Result = 140
...change stato into 'approvato' thus count must give me 141 next time, requery the row, check changes are saved


2012-03-23T10:44:52+01:00 DEBUG (7): Row id = 1810
2012-03-23T10:44:52+01:00 DEBUG (7): SELECT count(*) AS `nr` FROM `ordine` WHERE (ditta_id = '3') AND (YEAR(`data`) = '2012') AND (stato = 'approvato' or stato = 'consegnato') LIMIT 1
2012-03-23T10:44:52+01:00 DEBUG (7): Result = 140 ?!!! why not 141?

ditta_id, agente_id, stato 是索引

我正在使用 Zend Framework 1.11.11 和 MySQL 5.1.49-3

Answer 1

对于您更新的行，我假设以下情况之一为真...

• ditta_id 不是 '3'
• YEAR(data) 不是 '2012'
• stato 已经是 'approvato'
• stato 已经是 'consegnato'
• stato 中的新值有错字

在更改 stato 字段之前和之后，您能否向我们展示这些字段？