V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
einsdisp
V2EX  ›  程序员

数据库批量插入数据,对于 unique 字段,在遇到重复数据时如何自动重命名?

  •  
  •   einsdisp · 2020-07-27 17:15:35 +08:00 · 1534 次点击
    这是一个创建于 453 天前的主题,其中的信息可能已经有所发展或是发生改变。

    数据库为 PostgresSQL,

    表结构样例:

    create table public.test (
        id serial primary key,
        key text unique not null,
        value int
    );
    

    其中字段 key 具有唯一性约束

    现在,需要插入大量数据,例如:

    insert into public.test (key, value) values
    ('k1', 101),
    ('k2_dup', 102),
    ('k2_dup', 103);
    

    数据中 key 字段存在大量重复,如何在插入时按一定规则自动重命名,就是说,如果要插入某值,但是数据库中该字段该值已经存在,则自动末尾添加 _2,如果仍然重复,则改为 _3,以此类推。

    最好能批量处理这些插入数据,就说说,最好别每一条数据就一个 insert 语句。

    11 条回复    2020-07-28 09:08:56 +08:00
    labulaka521
        1
    labulaka521   2020-07-27 17:39:11 +08:00
    事先处理好数据呗
    allAboutDbmss
        2
    allAboutDbmss   2020-07-27 17:45:59 +08:00
    你应该不是手动输入这些 insert 吧
    我想是有个 csv 或者 script
    psql 有个 copy 可以 bulkload(快速读取)csv 等文件类型

    最好你用 grep, sed, awk 这种命令行工具先处理你的文件或者 script
    我从来没在 psql insert 的时候做很多判断
    einsdisp
        3
    einsdisp   2020-07-27 17:48:10 +08:00
    einsdisp
        4
    einsdisp   2020-07-27 17:49:20 +08:00
    @labulaka521
    @allAboutDbmss
    实现处理好数据的话,从你处理好数据时刻到插入的时刻,中间又有可能有新数据已经插入,导致你“处理好”的数据又重复了
    allAboutDbmss
        5
    allAboutDbmss   2020-07-27 17:57:14 +08:00
    那你需要 insert + select
    我给了一个很小的例子 你另外需要字符串处理的函数

    ```
    psql=# drop table foo;
    DROP TABLE
    psql=# create table foo (id int);
    CREATE TABLE
    psql=# select * from foo;
    id
    ----
    (0 rows)

    psql=# insert into foo (id) values (1);
    INSERT 0 1
    psql=# select * from foo;
    id
    ----
    1
    (1 row)

    psql=# insert into foo (id) select f.id+1 from foo f where f.id=1;
    INSERT 0 1
    psql=# select * from foo;
    id
    ----
    1
    2
    (2 rows)

    psql=# insert into foo (id) select f.id+1 from foo f where f.id=2;
    INSERT 0 1
    psql=# select * from foo;
    id
    ----
    1
    2
    3
    (3 rows)

    ```
    sss15
        6
    sss15   2020-07-27 18:08:38 +08:00
    先 select 再插入,单这样效率很低,插个眼看看其他大佬给的方案
    sfqtsh
        7
    sfqtsh   2020-07-27 18:31:32 +08:00 via Android
    >= 9.5 新增 upsert 特性
    INSERT...ON CONFLICT DO UPDATE...
    zhazi
        8
    zhazi   2020-07-27 19:12:13 +08:00
    insert(key k)
    try{
    insert(k);
    }catch(DuplicateKeyException e){
    insert(key+1);
    }
    MoYi123
        9
    MoYi123   2020-07-27 21:06:29 +08:00
    图一乐。估计性能还是会有问题。为了方便把后缀单独弄了一列。

    create table u_insert
    (
    id serial primary key,
    key text,
    value int,
    suffix int default 0
    );
    create unique index on u_insert (key, suffix);

    begin;
    lock u_insert;
    CREATE unlogged TABLE tmp(id serial,key text,value int) on commit drop;
    insert into tmp(key, value) values ('a', 1),('a', 2),('b', 1);
    insert into u_insert(key, value, suffix)
    select key, value, t.suffix + rank() OVER (PARTITION BY key ORDER BY id DESC) as suffix
    from tmp,
    (select t.key as k, greatest(max(u_insert.suffix), t.suffix) as suffix
    from u_insert right join (select unnest(array ['a','b']) as key, 0 as suffix) as t on u_insert.key = t.key group by t.key, t.suffix) as t
    where t.k = key;
    commit;
    rrfeng
        10
    rrfeng   2020-07-27 21:14:25 +08:00 via Android
    每次 1000 条,出错再 handle 一下不就行了??
    Habyss
        11
    Habyss   2020-07-28 09:08:56 +08:00
    也就是说, 无论数据重复不重复, 这些数据都是要插入表中的. 而且如果 key 重复, 还可以随意改动.
    弱弱的问一句, 那这个 key 存在的意义是什么...

    如果末尾添加 _2 依旧有重复的, 也就是说你这样批量处理过的数据, 还会重复处理?不然为什么会有_2.
    关于   ·   帮助文档   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   1670 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 21ms · UTC 16:53 · PVG 00:53 · LAX 09:53 · JFK 12:53
    ♥ Do have faith in what you're doing.