Advertisement
Advertisement


Search for all occurrences of a string in a mysql database


Question

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.

2015/03/20
1
197
3/20/2015 1:58:02 AM

Accepted Answer

A simple solution would be doing something like this:

mysqldump -u myuser --no-create-info --extended-insert=FALSE databasename | grep -i "<search string>"
2019/07/17
223
7/17/2019 4:53:12 PM


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.

2011/09/12

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

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.

enter image description here

2014/06/25

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.

2009/02/18

You can do this by using HeidiSQL without generating Db dumps

Steps:

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

2) Export > Export Database as SQL

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

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

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

6) Select the row with higher relevance %.

7) Click "SEE RESULTS"

enter image description here

2017/02/13

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.

<?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 :)

2012/04/16

Source: https://stackoverflow.com/questions/562457
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]