Organizational Research By

Surprising Reserch Topic

find and replace in entire mysql database


find and replace in entire mysql database  using -'mysql'

i would like to do a find and replace inside an entire database not just a table.

How can i alter the script below to work?


  update [table_name] set [field_name] =
  replace([field_name],'[string_to_find]','[string_to_replace]');


Do i just use an asterix?


  update * set [field_name] =
  replace([field_name],'[string_to_find]','[string_to_replace]');

    

asked Sep 29, 2015 by girisha
0 votes
6 views



Related Hot Questions

6 Answers

0 votes

Update old URL to new URL in word-press mysql Query:

UPDATE wp_options SET option_value = replace(option_value, 'http://olddomain.com', 'http://newdomain.com') WHERE option_name = 'home' OR option_name = 'siteurl';

UPDATE wp_posts SET guid = replace(guid, 'http://olddomain.com','http://newdomain.com');

UPDATE wp_posts SET post_content = replace(post_content, 'http://olddomain.com', 'http://newdomain.com');

UPDATE wp_postmeta SET meta_value = replace(meta_value, 'http://olddomain.com', 'http://newdomain.com');

answered Sep 29, 2015 by ajit.chavhan
0 votes

Short answer: You can't.

Long answer: You can use the INFORMATION_SCHEMA to get the table definitions and use this to generate the necessary UPDATE statements dynamically. For example you could start with this:

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_schema'

I'd try to avoid doing this though if at all possible.

answered Sep 29, 2015 by bhavin
0 votes

This isn't possible - you need to carry out an UPDATE for each table individually.

WARNING: DUBIOUS, BUT IT'LL WORK (PROBABLY) SOLUTION FOLLOWS

Alternatively, you could dump the database via mysqldump and simply perform the search/replace on the resultant SQL file. (I'd recommend offlining anything that might touch the database whilst this is in progress, as well as using the --add-drop-table and --extended-insert flags.) However, you'd need to be sure that the search/replace text wasn't going to alter anything other than the data itself (i.e.: that the text you were going to swap out might not occur as a part of SQL syntax) and I'd really try doing the re-insert on an empty test database first.)

answered Sep 29, 2015 by tseetha
0 votes

This strongly implies that your data IS NOT NORMALISED to begin with. You really should fix that.

Somteh=thing like this should work (NB you've not mentioned of your using any other languages - so its written as a MySQL stored procedure)

 create procedure replace_all(find varchar(255), 
        replce varchar(255), 
        indb varcv=char(255))
 DECLARE loopdone INTEGER DEFAULT 0;
 DECLARE currtable varchar(100);
 DECLARE alltables CURSOR FOR SELECT t.tablename, c.column_name 
    FROM information_schema.tables t,
    information_schema.columns c
    WHERE t.table_schema=indb
    AND c.table_schema=indb
    AND t.table_name=c.table_name;

 DECLARE CONTINUE HANDLER FOR NOT FOUND
     SET loopdone = 1;

 OPEN alltables;

 tableloop: LOOP
    FETCH alltables INTO currtable, currcol; 
    IF (loopdone>0) THEN LEAVE LOOP;
    END IF;
         SET stmt=CONCAT('UPDATE ', 
                  indb, '.', currtable, ' SET ',
                  currcol, ' = word_sub(\'', find, 
                  '\','\'', replce, '\') WHERE ',
                  currcol, ' LIKE \'%', find, '%\'');
         PREPARE s1 FROM stmt;
         EXECUTE s1;
         DEALLOCATE PREPARE s1;
     END LOOP;
 END //

I'll leave it to you to work out how to declare the word_sub function - I've done enough free programming here.

answered Sep 29, 2015 by param.oncemore
0 votes

MySQL Search & Replace Tool

Very useful web-based tool written in PHP which makes it easy to search and replace text strings in a MySQL database.

answered Sep 29, 2015 by pradip.bhoge
0 votes

Another option (depending on the use case) would be to use DataMystic's TextPipe and DataPipe products. I've used them in the past, and they've worked great in the complex replacement scenarios, and without having to export data out of the database for find-and-replace.

answered Sep 29, 2015 by devkumargupta

...