I’m trying to change the default value of column
updated_at, which is a datetime type. What i want is:
Allow Null = True Default = Null On Update = Current Timestamp
Here is my query attempt:
ALTER TABLE billing_payment_flow MODIFY updated_at datetime(6) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6)
I run the query and then when i check the table, nothing has been changed?
Where did i go wrong in query?
You can try the query below. I expect that the problem occurs due to the size specifier
(6) in your statement, this depends on the MySQL version you’re using. If you want to use
DATETIME(6) you need MySQL 5.7 or later.
ALTER TABLE `billing_payment_flow` MODIFY COLUMN `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP;
This works for me, tested it with the following script in MySQL 8.0:
CREATE TABLE `billing_payment_flow`(`ID` int, `updated_at` DATETIME(6)); INSERT INTO `billing_payment_flow`(`ID`) values(1); ALTER TABLE `billing_payment_flow` MODIFY COLUMN `updated_at` DATETIME(6) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6); UPDATE `billing_payment_flow` SET `ID` = 2 WHERE `ID` = 1; SELECT * FROM `billing_payment_flow`;
ID updated_at 2 2021-09-14 10:55:55.891703