CODEWITHSUNDEEP
mysql
Paul Zhang
Paul Zhang

Reputation: 335

set column default failed in mysql

Here is my table DDL:

-- auto-generated definition
create table treehole
(
    id          bigint auto_increment
        primary key,
    content     text                                not null,
    author_id   bigint                              not null,
    create_time timestamp default CURRENT_TIMESTAMP not null,
    modify_time timestamp                           not null,
    oo_num      int       default 0                 not null,
    xx_num      int       default 0                 not null
);

create index Treehole_author_id_index
    on treehole (author_id);

create index Treehole_create_time_index
    on treehole (create_time desc);

create index Treehole_oo_num_index
    on treehole (oo_num desc);

And I want to set the default of modify_time as current_timestamp:

alter table `treehole`
    alter column `modify_time`
        set default current_timestamp;

And it occurs:

[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'current_timestamp' at line 3

My MySQL version is 8.0.422

Upvotes: 2

Views: 62

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

Your syntax is off, use:

ALTER TABLE treehole
MODIFY COLUMN modify_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

The jist here is that you need MODIFY COLUMN rather than ALTER COLUMN, and also you need to basically restate the entire column definition, rather than just restating the default value.

Upvotes: 2

Related Questions