CODEWITHSUNDEEP
mysql
Andy
Andy

Reputation: 3021

Find and replace entire MySQL database

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]');

Upvotes: 74

Views: 173260

Answers (12)

Vishnu Sharma
Vishnu Sharma

Reputation: 1383

Update old URL to new URL in WordPress MySQL Query:

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

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

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

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

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

Upvotes: 54

Julien Berrier
Julien Berrier

Reputation: 21

I had the same issue on MySQL. I took the procedure from symcbean and adapted her to my needs.

Mine is only replacing textual values (or any type you put in the SELECT FROM information_schema) so if you have date fields, you will not have an error in execution.

Mind the collate in SET @stmt, it must match you database collation.

I used a template request in a variable with multiple replaces but if you have motivation, you could have done it with one CONCAT().

Anyway, if you have serialized data in your database, don't use this. It will not work unless you replace your string with a string with the same lenght.

Hope it helps someone.

DELIMITER $$

DROP PROCEDURE IF EXISTS replace_all_occurences_in_database$$
CREATE PROCEDURE replace_all_occurences_in_database (find_string varchar(255), replace_string varchar(255))
BEGIN
  DECLARE loop_done integer DEFAULT 0;
  DECLARE current_table varchar(255);
  DECLARE current_column varchar(255);
  DECLARE all_columns CURSOR FOR
  SELECT
    t.table_name,
    c.column_name
  FROM information_schema.tables t,
       information_schema.columns c
  WHERE t.table_schema = DATABASE()
  AND c.table_schema = DATABASE()
  AND t.table_name = c.table_name
  AND c.DATA_TYPE IN('varchar', 'text', 'longtext');

  DECLARE CONTINUE HANDLER FOR NOT FOUND
  SET loop_done = 1;

  OPEN all_columns;

table_loop:
LOOP
  FETCH all_columns INTO current_table, current_column;
  IF (loop_done > 0) THEN
    LEAVE table_loop;
  END IF;
  SET @stmt = 'UPDATE `|table|` SET `|column|` = REPLACE(`|column|`, "|find|", "|replace|") WHERE `|column|` LIKE "%|find|%"' COLLATE `utf8mb4_unicode_ci`;
  SET @stmt = REPLACE(@stmt, '|table|', current_table);
  SET @stmt = REPLACE(@stmt, '|column|', current_column);
  SET @stmt = REPLACE(@stmt, '|find|', find_string);
  SET @stmt = REPLACE(@stmt, '|replace|', replace_string);
  PREPARE s1 FROM @stmt;
  EXECUTE s1;
  DEALLOCATE PREPARE s1;
END LOOP;
END
$$

DELIMITER ;

Upvotes: 2

T.Todua
T.Todua

Reputation: 56557

BE CAREFUL, when replacing with REPLACE command!

why?

because there is a great chance that your database contains serialized data (especially wp_options table), so using just "replace" might break data.

Use recommended serialization: https://puvox.software/tools/wordpress-migrator

Upvotes: 7

symcbean
symcbean

Reputation: 48387

This strongly implies that your data IS NOT NORMALISED to begin with.

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

Upvotes: 6

Sibeesh Venu
Sibeesh Venu

Reputation: 21849

If you are in phpMyAdmin and you have only a minimal change, you can do this in an easy way.

  • Login to your phpMyAdmin
  • Select the database you need to perform the changes

  • Click on the search option

    enter image description here

You can always select either all the tables or any. Remember to give the search keyword, it will be used as wildcard(%).

  • Now click on Go.
  • This will give you all the tables which have the item you have searched for.

enter image description here

  • Now you can open each table one by one and perform the update A sample query generated may look like the following.

    SELECT * FROM sibeecst_passion.wp_ewwwio_images WHERE (CONVERT(id USING utf8) LIKE '%sibee%' OR CONVERT(path USING utf8) LIKE '%sibee%' OR CONVERT(image_md5 USING utf8) LIKE '%sibee%' OR CONVERT(results USING utf8) LIKE '%sibee%' OR CONVERT(gallery USING utf8) LIKE '%sibee%' OR CONVERT(image_size USING utf8) LIKE '%sibee%' OR CONVERT(orig_size USING utf8) LIKE '%sibee%' OR CONVERT(updates USING utf8) LIKE '%sibee%' OR CONVERT(updated USING utf8) LIKE '%sibee%' OR CONVERT(trace USING utf8) LIKE '%sibee%' OR CONVERT(attachment_id USING utf8) LIKE '%sibee%' OR CONVERT(resize USING utf8) LIKE '%sibee%' OR CONVERT(converted USING utf8) LIKE '%sibee%' OR CONVERT(level USING utf8) LIKE '%sibee%' OR CONVERT(pending USING utf8) LIKE '%sibee%' OR CONVERT(backup USING utf8) LIKE '%sibee%')

Upvotes: 1

Dean Rather
Dean Rather

Reputation: 32394

sqldump to a text file, find/replace, re-import the sqldump.

Dump the database to a text file
mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

Restore the database after you have made changes to it.
mysql -u root -p[root_password] [database_name] < dumpfilename.sql

Upvotes: 145

Rudolph
Rudolph

Reputation: 1642

I just wanted to share how I did this find/replace thing with sql database, because I needed to replace links from Chrome's sessionbuddy db file.

  • So I exported sql database file as .txt file by using SQLite Database Browser 2.0 b1
  • Find/replace in notepad++
  • Imported the .txt file back on SQLite Database Browser 2.0 b1

Upvotes: 0

Ville
Ville

Reputation: 4346

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.

Upvotes: 0

user1794295
user1794295

Reputation: 1839

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.

Upvotes: 2

Jaha Rabari
Jaha Rabari

Reputation: 173

Simple Soltion

UPDATE `table_name`
 SET `field_name` = replace(same_field_name, 'unwanted_text', 'wanted_text')

Upvotes: 1

John Parker
John Parker

Reputation: 54445

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

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 839174

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.

Upvotes: 4

Related Questions