Mysql 添加/去掉 根据时间戳更新

建测试表

DROP TABLE IF EXISTS `bill`;
CREATE TABLE `bill`  (
  `id` bigint(18) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `create_date` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '创建日期',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1334629073619521978 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

显示建表语句的sql

show create table bill;

显示

去掉 根据时间戳更新

alter table bill change create_date create_date datetime(0) NULL DEFAULT NULL COMMENT '创建日期';
show create table bill;

添加 根据时间戳更新

alter table bill change create_date `create_date` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '创建日期';
show create table bill;

安全改字段

create procedure tmp_change_column
(IN exist integer)
Begin
select count(*)  into exist from information_schema.`COLUMNS` where table_name = 'bill' and COLUMN_NAME='create_date' and column_type='datetime';
if exist=1 then
   alter table bill change create_date create_date datetime(0) NULL DEFAULT NULL COMMENT '创建日期';
end if;
End $$
call tmp_change_column(0) $$
drop procedure if exists tmp_change_column $$
delimiter ;