注册

在 PostgreSQL 和 MySQL 中的所有表中查找特定数据

使用大型数据库时,您可能会发现自己需要在多个表和列中查找特定值。这可能是一项具有挑战性的任务,尤其是当您不知道到底该去哪里查看时。幸运的是,有一些方法可以在 postgresql 和 mysql 中自动执行此搜索。

在 postgresql 中搜索数据

postgresql 允许高级过程语言功能,这在此类场景中非常有用。下面,我们将创建一个 pl/pgsql 块,用于在 postgresql 数据库中的所有表和列中搜索特定值。

分步指南:

  1. 创建 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 $$;
  1. 说明:
  • 声明变量:我们声明变量来保存我们的搜索文本和动态查询。
  • 循环列: 我们循环遍历公共模式中类型为字符变化、文本或 uuid 的每一列。
  • 构造并执行查询: 对于每一列,我们构造一个动态 sql 查询来检查该列是否包含搜索文本。
  • 发出通知: 如果找到搜索文本,则会发出带有该列位置的通知。
  1. 运行区块:

在 postgresql 查询工具(例如 pgadmin、psql)中执行上述块。这将打印出找到指定值的位置。

在 mysql 中搜索数据

mysql 不支持 pl/pgsql 风格的过程语言块。但是,您可以通过手动生成并运行必要的查询或使用 shell 脚本来实现类似的功能。

分步指南:

  1. 生成搜索查询:

以下 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 替换为您数据库的实际名称。

  1. 手动运行生成的查询:

复制上述查询的输出,它看起来像这样:

   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 客户端中手动执行每个查询。

  1. 使用 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 



将 your_username、your_password 和 your_database_name 替换为您的实际 mysql 用户名、密码和数据库名称。该脚本将生成必要的搜索查询,然后执行每个查询并打印结果。

结论

无论您使用 postgresql 还是 mysql,您都可以通过利用 postgresql 中的过程语言块或在 mysql 中生成和执行动态查询,在数据库中的所有表和列中高效搜索特定值。这些方法可以在管理大型数据集时为您节省大量时间和精力。

对于 postgresql,pl/pgsql 块为自动搜索提供了强大的解决方案。对于 mysql,虽然过程语言块不可用,但手动或通过 shell 脚本生成和运行动态查询可以达到相同的结果。

通过利用这些技术,您可以简化数据库管理任务并快速找到所需的数据。

以上就是在 PostgreSQL 和 MySQL 中的所有表中查找特定数据的详细内容,更多请关注CTO智库其它相关文章!