在 PostgreSQL 和 MySQL 中的所有表中查找特定数据-CTO智库

使用大型数据库时，您可能会发现自己需要在多个表和列中查找特定值。这可能是一项具有挑战性的任务，尤其是当您不知道到底该去哪里查看时。幸运的是，有一些方法可以在 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 



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


  
  
  结论


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

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

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

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