MySQL的ON UPDATE CURRENT_TIMESTAMP在布尔/枚举更改时不更新

huangapple go评论58阅读模式
英文:

MySQL ON UPDATE CURRENT_TIMESTAMP not updating on boolean / enum changes

问题

我有这个表:

CREATE TABLE `users` (
  `id` varchar(36) COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'uuid-will-be-generated',
  `createdAt` timestamp NOT NULL DEFAULT (now()),
  `updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
  `username` varchar(60) COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '',
  `email` varchar(256) COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '',
  `realName` varchar(60) COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '',
  `password` varchar(256) COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'no-password-specified',
  `passwordType` enum('bcrypt') COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'bcrypt',
  `useAsDisplayName` enum('username','email','realName') COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'username',
  `admin` tinyint(1) NOT NULL DEFAULT '0',
  `developer` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `usernameIndex` (`username`),
  UNIQUE KEY `emailIndex` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci

如果我更新任何varchar字段,updatedAt字段会更新。但如果我更新一个enum/boolean字段,它不会更新。我知道如果更新的数据相同,它不会更新,但数据是不同的,它仍然不会更新。使用的是MySQL 8.0.32版本。

有人有什么想法吗?

英文:

I've got this table:

CREATE TABLE `users` (
  `id` varchar(36) COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'uuid-will-be-generated',
  `createdAt` timestamp NOT NULL DEFAULT (now()),
  `updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
  `username` varchar(60) COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '',
  `email` varchar(256) COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '',
  `realName` varchar(60) COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '',
  `password` varchar(256) COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'no-password-specified',
  `passwordType` enum('bcrypt') COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'bcrypt',
  `useAsDisplayName` enum('username','email','realName') COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'username',
  `admin` tinyint(1) NOT NULL DEFAULT '0',
  `developer` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `usernameIndex` (`username`),
  UNIQUE KEY `emailIndex` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci

And if I update any varchar the updatedAt field updates. But if I update an enum/boolean field then it doesn't. I know it won't update if the data in an update is the same, but the data is different and it doesn't update. Using MySQL 8.0.32.

Anybody have any ideas?

答案1

得分: 1

原来是我的应用程序,而不是MySQL出了问题。我应该在更底层检查。

英文:

It turns out it was my app not MySQL. I should have checked at a lower level.

huangapple
  • 本文由 发表于 2023年4月10日 22:02:33
  • 转载请务必保留本文链接:https://go.coder-hub.com/75977789.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定