使用大型数据库时，您可能会发现自己需要在多个表和列中查找特定值。这可能是一项具有挑战性的任务，尤其是当您不知道到底该去哪里查看时。幸运的是，有一些方法可以在 postgresql 和 mysql 中自动执行此搜索。
在 postgresql 中搜索数据
postgresql 允许高级过程语言功能，这在此类场景中非常有用。下面，我们将创建一个 pl/pgsql 块，用于在 postgresql 数据库中的所有表和列中搜索特定值。
分步指南：
- 创建 pl/pgsql 块：
以下 pl/pgsql 块将在公共模式内所有类型为字符变化、文本或 uuid 的列中搜索值“dcea8891-b4e1-45f8-8cb9-c8a164cb98ff”。
do $$
declare
rec record;
search_text text := 'dcea8891-b4e1-45f8-8cb9-c8a164cb98ff';
query text;
begin
for rec in
select table_schema, table_name, column_name
from information_schema.columns
where table_schema = 'public' and data_type in ('character varying', 'text', 'uuid')
loop
query := 'select ''' || rec.table_schema || '.' || rec.table_name || '.' || rec.column_name || ''' as location, ' ||
rec.column_name || '
from ' || rec.table_schema || '.' || rec.table_name || '
where ' || rec.column_name || '::text = $1';
execute query using search_text into rec;
if rec is not null then
raise notice 'found in %', rec.location;
end if;
end loop;
end $$;
- 说明：
- 声明变量：我们声明变量来保存我们的搜索文本和动态查询。
- 循环列： 我们循环遍历公共模式中类型为字符变化、文本或 uuid 的每一列。
- 构造并执行查询： 对于每一列，我们构造一个动态 sql 查询来检查该列是否包含搜索文本。
- 发出通知： 如果找到搜索文本，则会发出带有该列位置的通知。
- 运行区块：
在 postgresql 查询工具（例如 pgadmin、psql）中执行上述块。这将打印出找到指定值的位置。
在 mysql 中搜索数据
mysql 不支持 pl/pgsql 风格的过程语言块。但是，您可以通过手动生成并运行必要的查询或使用 shell 脚本来实现类似的功能。
分步指南：
- 生成搜索查询：
以下 sql 查询将生成一个查询列表，用于在指定数据库中的所有类型为 varchar、text 或 char 的列中搜索值“dcea8891-b4e1-45f8-8cb9-c8a164cb98ff”。
select
concat('select ''', table_schema, '.', table_name, '.', column_name,
''' as location, ', column_name,
' from ', table_schema, '.', table_name,
' where ', column_name, ' = ''', 'dcea8891-b4e1-45f8-8cb9-c8a164cb98ff', ''';') as search_query
from
information_schema.columns
where
table_schema = 'your_database_name'
and data_type in ('varchar', 'text', 'char');
将 your_database_name 替换为您数据库的实际名称。
- 手动运行生成的查询：
复制上述查询的输出，它看起来像这样：
select 'your_database_name.table1.column1' as location, column1 from your_database_name.table1 where column1 = 'dcea8891-b4e1-45f8-8cb9-c8a164cb98ff'; select 'your_database_name.table2.column2' as location, column2 from your_database_name.table2 where column2 = 'dcea8891-b4e1-45f8-8cb9-c8a164cb98ff';
在 mysql 客户端中手动执行每个查询。
- 使用 shell 脚本实现自动化：
如果您有权访问类 unix shell，则可以使用 shell 脚本自动化该过程：
#!/bin/bash
SEARCH_TEXT='dcea8891-b4e1-45f8-8cb9-c8a164cb98ff'
DATABASE='your_database_name'
USERNAME='your_username'
PASSWORD='your_password'
# Generate the search queries
QUERIES=$(mysql -u $USERNAME -p$PASSWORD -D $DATABASE -N -e "
SELECT CONCAT('SELECT ''', table_schema, '.', table_name, '.', column_name,
''' AS location, ', column_name,
' FROM ', table_schema, '.', table_name,
' WHERE ', column_name, ' = ''', '$SEARCH_TEXT', ''';')
FROM information_schema.columns
WHERE table_schema = '$DATABASE' AND data_type IN ('varchar', 'text', 'char');")
# Execute each query
while read -r QUERY; do
mysql -u $USERNAME -p$PASSWORD -D $DATABASE -e "$QUERY"
done <<< "$QUERIES"
将 your_username、your_password 和 your_database_name 替换为您的实际 mysql 用户名、密码和数据库名称。该脚本将生成必要的搜索查询，然后执行每个查询并打印结果。
结论
无论您使用 postgresql 还是 mysql，您都可以通过利用 postgresql 中的过程语言块或在 mysql 中生成和执行动态查询，在数据库中的所有表和列中高效搜索特定值。这些方法可以在管理大型数据集时为您节省大量时间和精力。
对于 postgresql，pl/pgsql 块为自动搜索提供了强大的解决方案。对于 mysql，虽然过程语言块不可用，但手动或通过 shell 脚本生成和运行动态查询可以达到相同的结果。
通过利用这些技术，您可以简化数据库管理任务并快速找到所需的数据。
