List:General Discussion« Previous MessageNext Message »
From:SGreen Date:July 29 2005 2:40pm
Subject:Re: Help need search database
View as plain text  
Gregory Machin <gregory.machin@stripped> wrote on 07/29/2005 08:00:07 AM:

> Hi 
> Please could advise me.
> 
> I need search all the tables in a database for a single string. I'm
> trying to figure out how, where and what other tables exponent cms
> saves it text pages and references to, so i can finish writing a mass
> page import module....
> 
> 
> In short can mysql do a recursive search,  through all the table's in
> a database ..
> Many Thanks ..
> -- 
> Gregory Machin
> greg@stripped
> gregory.machin@stripped
> www.linuxpro.co.za
> Web Hosting Solutions
> Scalable Linux Solutions 
> www.iberry.info (support and admin)
> www.goeducation (support and admin)
> +27 72 524 8096
> 

Recursive search? No, not a native MySQL function. To do it through MySQL 
you would need to write a script that provides all of the SQL statements 
you would need to search every column of every table that may hold your 
target data (your piece of "special text").

However!! if your tables are MyISAM, you could turn off the server and 
scan the actual data files for your target string. It will not tell you in 
which column your target data is in but it will tell you which tables 
contain that piece of data. That will give you only a few tables to look 
at. With those file names in hand (filename = table name) you can look up 
the definitions of each table (SHOW CREATE TABLE tablename\G) and narrow 
your search to just the columns capable of holding your piece of test 
data.

It's the brute-force approach, I know, but it should be relatively quick 
and will help you to quickly narrow your search. This might also work if 
you have InnoDB tables and are creating a separate tablespace for each 
file. If you are using the default InnoDB behavior of one shared 
tablespace for all files, this technique would not help.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Thread
Help need search databaseGregory Machin29 Jul
  • Re: Help need search databasePat Adams29 Jul
  • Re: Help need search databaseSGreen29 Jul