概述

每次想要实现一个功能时，总是百度Google，挺浪费时间的，于是整理得到此文。持续更新中。

目录

列更新

比如，http更新为https：

UPDATE <table> SET <field> = replace(field, 'http://www.baidu.com', 'https://www.baidu.com');

删除重复数据，仅保留索引(id)最小的一条数据

硬删除：

delete
from role_res
where role_res_id not in (
    select role_res_id
    from (
             select min(role_res_id) as role_res_id
             from role_res
             group by role_id, res_type, res_id, permission
             having count(*) > 1
         ) dt
);

逻辑删除：

update role_res set is_active = 0
where role_res_id not in (
    select role_res_id
    from (
             select min(role_res_id) as role_res_id
             from role_res
             group by role_id, res_type, res_id, permission
             having count(*) > 1
         ) dt
);

添加constraint限制

alter table role_res add constraint uniq_role_res_type_id unique (role_id, res_type, res_id);

参考