在 mysql 数据库中搜索所有出现的字符串

Search for all occurrences of a string in a mysql database

提问by gregh

I'm trying to figure out how to locate all occurrences of a url in a database. I want to search all tables and all fields. But I have no idea where to start or if it's even possible.

我试图弄清楚如何在数据库中找到所有出现的 url。我想搜索所有表格和所有字段。但我不知道从哪里开始，或者是否有可能。

回答by Paolo Bergantino

A simple solution would be doing something like this:

一个简单的解决方案是做这样的事情：

mysqldump -u myuser --no-create-info --extended-insert=FALSE databasename | grep -i "<search string>"

回答by murtaza.webdev

In phpMyAdmina 'Search' feature is available:

在phpMyAdmin 中，可以使用“搜索”功能：

1. Select particular database not table.
2. Click 'Search' tab
3. Enter the search term you want
4. Select the tables you want to search in

1. 选择特定的数据库而不是表。
2. 单击“搜索”选项卡
3. 输入您想要的搜索词
4. 选择要搜索的表

phpMyAdminscreen shot:

phpMyAdmin屏幕截图：

The 'Search' feature is also available in MySQL Workbench:

MySQL Workbench 中也提供了“搜索”功能：

1. Database Menu > Search Table Data
2. Select database and tables you want to search (it will search in selected tables only)
3. In search you can use wildChars.

1. 数据库菜单 > 搜索表数据
2. 选择要搜索的数据库和表（它只会在选定的表中搜索）
3. 在搜索中你可以使用wildChars。

MySQL Workbenchscreen shot:

MySQL 工作台屏幕截图：

回答by A. Googler

Old post I know, but for others that find this via Google like I did, if you have phpmyadmin installed, it has a global search feature.

我知道旧帖子，但对于像我一样通过 Google 找到它的其他人，如果您安装了 phpmyadmin，它具有全局搜索功能。

回答by Gurpreet

Using the MySQL Workbench, you can search for a string from the "Database" -> "Search Table Data" menu option.

使用 MySQL Workbench，您可以从“数据库”->“搜索表数据”菜单选项中搜索字符串。

Specify LIKE %URL_TO_SEARCH% and on the left side select all the tables you want to search through. You can use "Cntrl + A" to select the whole tree on the left, and then deselect the objects you don't care about.

指定 LIKE %URL_TO_SEARCH% 并在左侧选择要搜索的所有表。您可以使用“Cntrl + A”选择左侧的整棵树，然后取消选择您不关心的对象。

回答by MikeW

Brute force method

蛮力法

declare @url varchar(255)

set @url = 'stackoverflow.com'

select 'select * from ' + rtrim(tbl.name) + ' where ' + 
          rtrim(col.name) + ' like %' + rtrim(@url) + '%'
from sysobjects tbl
inner join syscolumns col on tbl.id = col.id 
and col.xtype in (167, 175, 231, 239) -- (n)char and (n)varchar, there may be others to include
and col.length > 30 -- arbitrary min length into which you might store a URL
where tbl.type = 'U'    -- user defined table

This will create a script that you could execute on the database.

这将创建一个可以在数据库上执行的脚本。

select * from table1 where col1 like '%stackoverflow.com%'
select * from table1 where col2 like '%stackoverflow.com%'
select * from table2 where col3 like '%stackoverflow.com%'

etc.

等等。

回答by Prasanth K C

You can do this by using HeidiSQLwithout generating Db dumps

您可以使用HeidiSQL执行此操作，而无需生成 Db 转储

Steps:

步骤：

1) Select the database you need to search in from the left panel of GUI.

1) 从 GUI 的左侧面板中选择您需要搜索的数据库。

2) Export > Export Database as SQL

2)导出 > 将数据库导出为 SQL

3) In Table Tools window select "FIND TEXT" tab.

3) 在表格工具窗口中选择“查找文本”选项卡。

4) Provide your string to search and click "FIND".

4) 提供您要搜索的字符串，然后单击“查找”。

5) It will list all the tables contains our string.

5）它将列出包含我们字符串的所有表。

6) Select the row with higher relevance %.

6) 选择相关性较高的行%。

7) Click "SEE RESULTS"

7) 点击“查看结果”

回答by jimmy.zoger

I was looking for this myself when we changed domain on our Wordpress website. It can't be done without some programming so this is what I did.

当我们在我们的 Wordpress 网站上更改域时，我自己也在寻找这个。没有一些编程就无法完成，所以这就是我所做的。

<?php  
  header("Content-Type: text/plain");

  $host = "localhost";
  $username = "root";
  $password = "";
  $database = "mydatabase";
  $string_to_replace  = 'old.example.com';
  $new_string = 'new.example.com';

  // Connect to database server
  mysql_connect($host, $username, $password);

  // Select database
  mysql_select_db($database);

  // List all tables in database
  $sql = "SHOW TABLES FROM ".$database;
  $tables_result = mysql_query($sql);

  if (!$tables_result) {
    echo "Database error, could not list tables\nMySQL error: " . mysql_error();
    exit;
  }

  echo "In these fields '$string_to_replace' have been replaced with '$new_string'\n\n";
  while ($table = mysql_fetch_row($tables_result)) {
    echo "Table: {$table[0]}\n";
    $fields_result = mysql_query("SHOW COLUMNS FROM ".$table[0]);
    if (!$fields_result) {
      echo 'Could not run query: ' . mysql_error();
      exit;
    }
    if (mysql_num_rows($fields_result) > 0) {
      while ($field = mysql_fetch_assoc($fields_result)) {
        if (stripos($field['Type'], "VARCHAR") !== false || stripos($field['Type'], "TEXT") !== false) {
          echo "  ".$field['Field']."\n";
          $sql = "UPDATE ".$table[0]." SET ".$field['Field']." = replace(".$field['Field'].", '$string_to_replace', '$new_string')";
          mysql_query($sql);
        }
      }
      echo "\n";
    }
  }

  mysql_free_result($tables_result);  
?>

Hope it helps anyone who's stumbling into this problem in the future :)

希望它可以帮助将来遇到此问题的任何人:)

回答by jsist

SQLyogis GUI based solution to the problem of data search across all columns, tables and databases. One can customize search restricting it to field, table and databases.

SQLyog是基于 GUI 的解决方案，用于解决跨所有列、表和数据库的数据搜索问题。可以自定义搜索，将其限制为字段、表格和数据库。

In its Data Searchfeature one can search for strings just like one uses Google.

在其Data Search功能中，您可以像使用 Google 一样搜索字符串。

回答by Brian

MikeW presented an interesting solution, but as mentioned in comments, it's a SQL Server solution not a MySQL solution. Here is a MySQL solution:

MikeW 提出了一个有趣的解决方案，但正如评论中提到的，它是一个 SQL Server 解决方案，而不是一个 MySQL 解决方案。这是一个 MySQL 解决方案：

use information_schema;
set @q = 'Boston';
set @t = 'my_db';

select CONCAT('use \'',@q,'\';') as q UNION
select CONCAT('select \'', tbl.`TABLE_NAME`,'\' as TableName, \'', col.`COLUMN_NAME`,'\' as Col, `',col.`COLUMN_NAME`,'` as value  from `' , tbl.`TABLE_NAME`,'` where `' ,
        col.`COLUMN_NAME` , '` like \'%' ,@q,  '%\' UNION') AS q
from `tables` tbl
inner join `columns` col on tbl.`TABLE_NAME` = col.`TABLE_NAME`and col.DATA_TYPE='varchar'
where tbl.TABLE_SCHEMA  =  @t  ;

回答by Chetabahana

Found a way with two (2) easy codes here. First do a mysqldump:

找到一种方法，有两个（2）易码在这里。首先做一个mysqldump：

mysqldump -uUSERNAME -p DATABASE_NAME > database-dump.sql

then grepthe sqldump file:

然后grepsqldump 文件：

grep -i "Search string" database-dump.sql

It possiblealso to find/replaceand re-importback to the database.

它可能还可以查找/替换和重新导入到数据库中。