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

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

这是一个创建于 1395 天前的主题，其中的信息可能已经有所发展或是发生改变。

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

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

字段

替换

数据库

脱敏

10 条回复 • 2021-01-28 18:25:02 +08:00

Te11UA

2021-01-28 11:28:12 +08:00

`UPDATE table SET 组织名字 = replace(组织名字, '组织 A', 'XXXX')`
这个？

codingadog

2021-01-28 11:31:27 +08:00 via Android

把表导出成 excel 或 sql
然后全局搜索替换
然后重新导入

kaiki

2021-01-28 11:33:10 +08:00

也可以直接把查询到的需要修改的部分导出，用外部程序批量正则修改再进行更新

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

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

2021-01-28 13:26:20 +08:00

@Te11UA 字段不固定，可能是组织名字段，也可能地址、设备名什么乱七八糟里面会包含

iblessyou

2021-01-28 13:27:12 +08:00

@codingadog 不行的，数据量比较大

iblessyou

2021-01-28 13:37:41 +08:00

@levelworm
@id4alex
谢谢，目前看到这个是较可行的解决思路了：
先获取所有字符串类型的字段和所在表，再批量生成更新语句。

id4alex

2021-01-28 15:50:13 +08:00

@iblessyou 可以通过 SQL 去生成 SQL 再执行生成的 SQL 。

这种脱敏的需求可以做到自动化的。

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