我检查了文档,但找不到任何可以解释这一点的东西。
mysql> SELECT * FROM information_schema.`TABLES` WHERE table_schema='myschema' AND TABLE_NAME ='mytable' \G;
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: myschema
TABLE_NAME: mytable
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 262354
AVG_ROW_LENGTH: 114
DATA_LENGTH: 30015488
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 0
DATA_FREE: 4194304
AUTO_INCREMENT: NULL
CREATE_TIME: 2022-05-17 12:00:32
UPDATE_TIME: 2022-05-17 06:00:37
CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_general_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
1 row in set (0,04 sec)
如您所见,看起来该表是在更新后创建的。知道什么会导致这种情况吗?
编辑:这是架构:
show create table myschema.mytable \G;
| mytable | CREATE TABLE `mytable` (
`app_id` char(27) NOT NULL,
`ref_id` int unsigned NOT NULL,
`value` decimal(10,5) unsigned NOT NULL,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`app_id`,`ref_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
你是对的,不应该。不幸的是,我强烈地感觉到有一个错误。
我在 MySQL 8.0.23 的 Windows 版本中创建了一个测试
测试代码
请注意我如何
DO SLEEP(3);
确保我们在操作之间看到不同的时间戳。我运行了多次, UPDATE_TIME 似乎卡在了
2022-05-18 09:28:56
我开始运行这个测试的时间戳上。这是我多次运行测试的会话
While the resulting behavior of my test is slightly different from yours, it is just as bad because the timestamp
2022-05-18 09:28:56
keeps reappearing long after I did the initial test.There has to be some bug regarding the information_schema. If I got this error in Windows, this bug most also exist in other OS versions of MySQL 8.0.23.
You should file a bug report. Chances are, Oracle might say it is a feature and you should code around it. It the very least, information_schema.tables is a little unstable.
There is already reported bug (90219) for that. I've tested on our enviroment like these steps:
1: Create Table As you can see, there is no update date on information field.
2: DML Operation such as insert:
3: Table Alter operation
After alter operation update time still same but creation time renewed. Probably alter commands drop and creates table, hence create date section updated. But MySQL keeps DML and watermark statistics on different place. It's ridiculous but I hope there is a logical explanation behind of it.
Our data architect team getting trouble on their data catalog product because of this update time < create time situation