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

数据库如何批量替换任意字段的某字符串

  •  
  •   iblessyou · 2021-01-28 11:20:07 +08:00 · 1625 次点击
    这是一个创建于 1155 天前的主题,其中的信息可能已经有所发展或是发生改变。

    看标题也不难猜了,我这是因为要做数据脱敏,数据库使用的 postgres

    以前给组织 A 做过的项目,现在几十个表,要求将所有表所有字段里出现的“组织 A”都替换成 XXX 。 尝试用了 Navicat 的模式查询,可以查到,但是改着不方便。 这个有简单点的办法吗

    10 条回复    2021-01-28 18:25:02 +08:00
    Te11UA
        1
    Te11UA  
       2021-01-28 11:28:12 +08:00   ❤️ 1
    `UPDATE table SET 组织名字 = replace(组织名字, '组织 A', 'XXXX')`
    这个?
    codingadog
        2
    codingadog  
       2021-01-28 11:31:27 +08:00 via Android
    把表导出成 excel 或 sql
    然后全局搜索替换
    然后重新导入
    kaiki
        3
    kaiki  
       2021-01-28 11:33:10 +08:00
    也可以直接把查询到的需要修改的部分导出,用外部程序批量正则修改再进行更新
    levelworm
        4
    levelworm  
       2021-01-28 11:35:15 +08:00
    应该可以查询到每张表每列的名字吧,然后做个小程序更新一下?
    找了一下 Stackoverflow 有人做了个 Perl 的程序:
    https://stackoverflow.com/questions/29211779/how-can-i-change-all-occurrences-of-a-particular-value-in-any-column-in-postgres
    id4alex
        5
    id4alex  
       2021-01-28 11:38:43 +08:00
    你需要用元数据的列记录 去拼接 SQL concat('REPLACE(',column_name','\'组织 A\',\'XXXX\') as ',column_name)
    类似的操作去生成那个 SELECT 语句.

    select * from INFORMATION_SCHEMA.columns

    你可以先看看这个 INFORMATION_SCHEMA 元数据库
    iblessyou
        6
    iblessyou  
    OP
       2021-01-28 13:26:20 +08:00
    @Te11UA 字段不固定,可能是组织名字段,也可能地址、设备名什么乱七八糟里面会包含
    iblessyou
        7
    iblessyou  
    OP
       2021-01-28 13:27:12 +08:00
    @codingadog 不行的,数据量比较大
    iblessyou
        8
    iblessyou  
    OP
       2021-01-28 13:37:41 +08:00
    @levelworm
    @id4alex
    谢谢,目前看到这个是较可行的解决思路了:
    先获取所有字符串类型的字段和所在表,再批量生成更新语句。
    id4alex
        9
    id4alex  
       2021-01-28 15:50:13 +08:00
    @iblessyou 可以通过 SQL 去生成 SQL 再执行生成的 SQL 。


    这种脱敏的需求可以做到自动化的。
    heysnakelis
        10
    heysnakelis  
       2021-01-28 18:25:02 +08:00
    SELECT
    TABLE_NAME,
    COLUMN_TYPE,
    COLUMN_NAME,
    CONCAT(
    'select `',COLUMN_NAME,'` as data ,"',COLUMN_NAME,'" as column_name ,"',TABLE_NAME,'" as table_name from ',
    TABLE_NAME,
    ' where `',
    COLUMN_NAME,
    '` like "%关键字%" or `',COLUMN_NAME,'` like "%关键字%" ' , "UNION "
    )
    FROM
    information_schema.`COLUMNS`
    WHERE
    TABLE_SCHEMA = 'test'


    参考下,select 改成 update
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   1791 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 16:31 · PVG 00:31 · LAX 09:31 · JFK 12:31
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.