MySQL实际项目中常用的 DDL 模板

create table `hot_campaign` (
    `id` bigint not null auto_increment comment '主键',
    `campaign_name` varchar(64) not null comment '活动名称',
    `site` varchar(32) not null comment '站点',
    `status` tinyint not null default 1 comment '状态：1启用 0停用',
    `created_at` datetime not null default current_timestamp comment '创建时间',
    `created_by` varchar(16) default null comment '创建人工号',
    `updated_at` datetime not null default current_timestamp on update current_timestamp comment '更新时间',
    `updated_by` varchar(16) default null comment '更新人工号',
    primary key (`id`),
    key `idx_site` (`site`),
    key `idx_status` (`status`)
) engine=innodb default charset=utf8mb4 comment='营销活动表';

alter table `hot_campaign`
add column `last_updated_staff` varchar(16) default null comment '最后更新人工号';

alter table `hot_campaign`
drop column `last_updated_staff`;

alter table `hot_campaign`
modify column `campaign_name` varchar(128) not null comment '活动名称';

alter table `hot_campaign`
change column `status` `campaign_status` tinyint not null default 1 comment '活动状态';

alter table `hot_campaign`
alter column `status` set default 1;

alter table `hot_campaign`
add index `idx_name` (`campaign_name`);

alter table `hot_campaign`
add index `idx_site_status` (`site`, `status`);

alter table `hot_campaign`
add unique index `uk_site_name` (`site`, `campaign_name`);

alter table `hot_campaign`
drop index `idx_site_status`;

alter table `coupon`
add constraint `fk_coupon_campaign`
    foreign key (`campaign_id`)
    references `hot_campaign` (`id`)
    on delete cascade
    on update cascade;

alter table `coupon`
drop foreign key `fk_coupon_campaign`;

rename table `hot_campaign` to `hot_campaign_v2`;

alter table `hot_campaign`
convert to character set utf8mb4 collate utf8mb4_unicode_ci;

drop table if exists `hot_campaign`;