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

User · Accepted Answer

A simple solution would be doing something like this   mysqldump -u myuser --no-create-info --extended-insert FALSE databasename   grep -i   lt search string gt

User · Answer

Not an elegant solution  but you could achieve it with a nested looping structure     select tables from database and store in an array     loop through the array foreach table in database          select columns in the table and store in an array         loop through the array     foreach column in table                  select   from table where column   url           You could probably speed this up by checking which columns contain strings while building your column array  and also by combining all the columns per table in one giant  comma-separated WHERE clause

User · Answer

SQLyog is 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   In its Data Search feature one can search for strings just like one uses Google

User · Answer

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    lt  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   gt  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       gt    Hope it helps anyone who s stumbling into this problem in the future

User · Answer

Found a way with two  2  easy codes here  First do a mysqldump   mysqldump -uUSERNAME -p DATABASE NAME  gt  database-dump sql   then grep the sqldump file   grep -i  Search string  database-dump sql   It possible also to find replace and re-import back to the database

User · Answer

I can t remember where I came across this script  but I ve been using it with XCloner to move my WP multisites    lt  php         Setup the associative array for replacing the old string with new string      replace array   array   FIND    gt   REPLACE    FIND    gt   REPLACE          mysql link   mysql connect   localhost    USERNAME    PASSWORD         if     mysql link            die   Could not connect      mysql error                  mysql db   mysql select db   DATABASE    mysql link        if    mysql db             die   Can  t select database      mysql error                    Traverse all tables      tables query    SHOW TABLES        tables result   mysql query   tables query        while   tables rows   mysql fetch row   tables result               foreach   tables rows as  table                     Traverse all columns              columns query    SHOW COLUMNS FROM      table               columns result   mysql query   columns query                while   columns row   mysql fetch assoc   columns result                         column    columns row  Field                     type    columns row  Type                        Process only text-based columns                 if  strpos   type   char        false    strpos   type   text        false                            Process all replacements for the specific column                                         foreach   replace array as  old string   gt   new string                              replace query    UPDATE      table                                  SET       column       REPLACE      column                                          old string               new string                                  mysql query   replace query                                                                               mysql free result   columns result        mysql free result   tables result        mysql close   mysql link         echo  Done       gt

User · Answer

The first 30 seconds of this video shows how to use the global search feature of Phpmyadmin and it works  it will search every table for a string   http   www vodahost com vodatalk phpmyadmin-setup 62422-search-database-phpmyadmin html

User · Answer

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

User · Answer

Scott gives a good example of how to do it  but the question is why would you want to   If you need to do a find-and-replace on a specific string  you could also try doing a mysqldump of your database  do a find-and-replace in an editor  then re-load the database   Maybe if you gave some background on what you are trying to achieve  others might be able to provide better answers

User · Answer

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

User · Answer

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   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

User · Answer

I was looking for the same but couldn t find it  so I make a small script in PHP  you can find it at  http   tequilaphp wordpress com 2010 07 05 searching-strings-in-a-database-and-files   Good luck   I remove some private code  let me know if I didn t break it in the process  D

User · Answer

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

User · Answer

In unix machines  if the database is not too big   mysqldump -u  lt username gt  -p  lt password gt   lt database name gt  --extended FALSE   grep  lt String to search gt    less -S

User · Answer

In phpMyAdmin a  Search  feature is available    Select particular database not table  Click  Search  tab Enter the search term you want Select the tables you want to search in   phpMyAdmin screen shot      The  Search  feature is also available in MySQL Workbench    Database Menu   Search Table Data Select database and tables you want to search  it will search in selected tables only  In search you can use wildChars    MySQL Workbench screen shot

User · Answer

If you can use a bash - here is a script  It needs a user dbread with pass dbread on the database      bin bash IFS     DBUSER dbread DBPASS dbread echo -n  Which database do you want to search in  press 0 to see all databases      read DB echo -n  Which string do you want to search     read SEARCHSTRING for i in  mysql  DB -u DBUSER -p DBPASS -e  show tables    grep -v   mysql  DB -u DBUSER -p DBPASS -e  show tables    head -1    do for k in  mysql  DB -u DBUSER -p DBPASS -e  desc  i    grep -v   mysql  DB -u DBUSER -p DBPASS -e  desc  i    head -1     grep -v int   awk   print  1    do if    mysql  DB -u DBUSER -p DBPASS -e  Select   from  i where  k   SEARCHSTRING     wc -l  -gt 1   then echo   Your searchstring was found in table  i  column  k  fi done done   If anyone wants an explanation  http   infofreund de  p 1670

[mysql] Search for all occurrences of a string in a mysql database

Examples related to mysql