出于某种原因，当我对 MySQL 中的特定表进行更新时，响应是:

“匹配的行:1 已更改:0 警告:0”

我一辈子都想不通为什么。我可以从数据库中进行选择:(我已经 X 出敏感数据 - 它只是文本)

mysql> SELECT * FROM outgoings WHERE id=198;
+-----+---------+---------------+------+-----+----------------+-----------------+-----------+------------------------------------+-----------+------------+------------------+---------------------+---------------------+---------------+---------------+-----------+-----------------+---------------------------------+-------------------+---------------------+----------------+
| id  | user_id | outgoing_date | form | bsb | account_number | transfer_number | amount    | recipient                          | client_id | project_id | purpose          | created_at          | updated_at          | received_from | cheque_number | cheque_to | project_id_from | cheque_drawer                   | recipient_purpose | transferrer_purpose | client_id_from |
+-----+---------+---------------+------+-----+----------------+-----------------+-----------+------------------------------------+-----------+------------+------------------+---------------------+---------------------+---------------+---------------+-----------+-----------------+---------------------------------+-------------------+---------------------+----------------+
| 198 |       2 | 2015-03-11    |    3 |     |                |                 | 407481.25 | XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |       162 |        503 | XXXXXXXXXXXXXXXX | 2015-03-13 17:51:36 | 2015-03-13 17:51:36 |               | 161           |           |            NULL | XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |                   |                     |           NULL |
+-----+---------+---------------+------+-----+----------------+-----------------+-----------+------------------------------------+-----------+------------+------------------+---------------------+---------------------+---------------+---------------+-----------+-----------------+---------------------------------+-------------------+---------------------+----------------+
1 row in set (0.00 sec)

如您所见，金额列为 407481.25。然后我运行一个简单的更新查询:

mysql> UPDATE cl_time.outgoings SET amount=407481.24 WHERE outgoings.id=198;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

结果显示没有任何更新，尽管事实上我正在尝试更改某些内容。如果我再次运行选择查询，我会看到相同的结果:

mysql> SELECT * FROM outgoings WHERE id=198;
+-----+---------+---------------+------+-----+----------------+-----------------+-----------+------------------------------------+-----------+------------+------------------+---------------------+---------------------+---------------+---------------+-----------+-----------------+---------------------------------+-------------------+---------------------+----------------+
| id  | user_id | outgoing_date | form | bsb | account_number | transfer_number | amount    | recipient                          | client_id | project_id | purpose          | created_at          | updated_at          | received_from | cheque_number | cheque_to | project_id_from | cheque_drawer                   | recipient_purpose | transferrer_purpose | client_id_from |
+-----+---------+---------------+------+-----+----------------+-----------------+-----------+------------------------------------+-----------+------------+------------------+---------------------+---------------------+---------------+---------------+-----------+-----------------+---------------------------------+-------------------+---------------------+----------------+
| 198 |       2 | 2015-03-11    |    3 |     |                |                 | 407481.25 | XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |       162 |        503 | XXXXXXXXXXXXXXXX | 2015-03-13 17:51:36 | 2015-03-13 17:51:36 |               | 161           |           |            NULL | XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |                   |                     |           NULL |
+-----+---------+---------------+------+-----+----------------+-----------------+-----------+------------------------------------+-----------+------------+------------------+---------------------+---------------------+---------------+---------------+-----------+-----------------+---------------------------------+-------------------+---------------------+----------------+
1 row in set (0.00 sec)

更多上下文，这是表结构:

CREATE TABLE `outgoings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `outgoing_date` date NOT NULL,
  `form` int(11) NOT NULL,
  `bsb` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `account_number` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `transfer_number` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `amount` float(8,2) NOT NULL,
  `recipient` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `client_id` int(11) NOT NULL,
  `project_id` int(11) NOT NULL,
  `purpose` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `received_from` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `cheque_number` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `cheque_to` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `project_id_from` int(11) DEFAULT NULL,
  `cheque_drawer` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `recipient_purpose` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `transferrer_purpose` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `client_id_from` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `outgoings_user_id_index` (`user_id`),
  KEY `outgoings_client_id_index` (`client_id`),
  KEY `outgoings_project_id_index` (`project_id`),
  KEY `outgoings_project_id_from_index` (`project_id_from`),
  KEY `outgoings_client_id_from_index` (`client_id_from`)
) ENGINE=MyISAM AUTO_INCREMENT=228 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

最后，我在 Laravel 中编写的 PHP 应用程序向该表插入和写入了数据。但是，该应用程序从不对此表运行更新查询。所有这一切过去都运行良好，直到我将应用程序迁移到新服务器，但我现在无法运行我的更新。

哦，任意我的mysql版本是:5.5.41，运行在Ubuntu上。

(这些问题似乎问的差不多，但是没有一个答案对我有用，所以我在这里问我所有的数据:Why would rows match but not be changed with a mysql update statement? 和 Update statement in mysql not working, while it has to work)

最佳答案

更新不更改行值的原因是舍入问题。您已将 amount 定义为 float(8,2)。然后，您将值从 407481.25 更改为 407481.24 - 它们之间的差异仅为 0.01，但我们知道计算机存在舍入问题。

由于更新不会写入新值，因此可以 100% 确定 MySQL 由于四舍五入问题而认为这两个数字相同。 如果一条记录没有真正改变，MySQL 不会把它写下来 - 这是一个优化步骤，如果不需要，MySQL 不会让硬盘驱动器工作。

现在，这意味着您需要调整您的 amount 列类型并将其更改为整数，然后在内部将小数点向左移动两位或使用诸如 decimal 具有较大的小数位最大数量(例如:amount DECIMAL(8,4))。

关于mysql - MySQL 更新返回 "Rows matched: 1 Changed: 0 Warnings: 0"，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/31086337/