Mysql 表已分区且不应用CURRENT_TIMESTAMP

Mysql Table Partitioned and not not applying CURRENT_TIMESTAMP

提问人:John King 提问时间:11/17/2023 最后编辑:John King 更新时间:11/17/2023 访问量:23

问:

我想知道为什么当我将数据库分区为几个月时,我的默认时间值已停止在我的数据库上工作。MySQL的8.1

这是正常行为吗?

是否有在MySQL中设置此默认值的替代解决方案?

下表如下

CREATE TABLE `datalogvalues1` (
  `dlvID` int NOT NULL AUTO_INCREMENT,
  `dlvVarID` int NOT NULL,
  `dlvValue` decimal(16,3) NOT NULL DEFAULT '0.000',
  `dlvCreated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `dlvModfied` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`dlvID`,`dlvCreated`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (to_days(`dlvCreated`))
(PARTITION `start` VALUES LESS THAN (0) ENGINE = InnoDB,
 PARTITION from202211 VALUES LESS THAN (738855) ENGINE = InnoDB,
 PARTITION from202212 VALUES LESS THAN (738886) ENGINE = InnoDB,
 PARTITION from202301 VALUES LESS THAN (738917) ENGINE = InnoDB,
 PARTITION from202302 VALUES LESS THAN (738945) ENGINE = InnoDB,
 PARTITION from202303 VALUES LESS THAN (738976) ENGINE = InnoDB,
 PARTITION from202304 VALUES LESS THAN (739006) ENGINE = InnoDB,
 PARTITION from202305 VALUES LESS THAN (739037) ENGINE = InnoDB,
 PARTITION from202306 VALUES LESS THAN (739067) ENGINE = InnoDB,
 PARTITION from202307 VALUES LESS THAN (739098) ENGINE = InnoDB,
 PARTITION from202308 VALUES LESS THAN (739129) ENGINE = InnoDB,
 PARTITION from202309 VALUES LESS THAN (739159) ENGINE = InnoDB,
 PARTITION from202310 VALUES LESS THAN (739190) ENGINE = InnoDB,
 PARTITION from202311 VALUES LESS THAN (739220) ENGINE = InnoDB,
 PARTITION future VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;
MySQL 数据库分区

评论

0赞 John King 11/17/2023
我发现这是因为我必须将该字段用作主键。即使它没有错误,它也不会添加生成的日期,因为它可能不是唯一的。
0赞 Barmar 11/17/2023
对我有用:db-fiddle.com/f/xtrAaZANmZQDYULcdDvGV4/0
0赞 Barmar 11/17/2023
为什么要把日期放在主键中?自动递增 ID 值已是唯一的。
1赞 Bill Karwin 11/17/2023
@Barmar - 我假设将日期添加到主键不是为了唯一性,而是为了允许对表进行分区。我看到许多开发人员这样做是为了解决分区列必须是主键和表的任何其他唯一键的一部分的要求。
0赞 Barmar 11/18/2023
@BillKarwin谢谢,忘记了那个限制。

答: 暂无答案