Find and replace entire mysql database

Question

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

User · Accepted Answer

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   gt  dumpfilename sql  Restore the database after you have made changes to it  mysql -u root -p root password   database name   lt  dumpfilename sql

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 posts SET post excerpt   replace post excerpt   http   olddomain com    http   newdomain com     UPDATE wp postmeta SET meta value   replace meta value   http   olddomain com    http   newdomain com

User · Answer

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

User · Answer

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

User · Answer

Simple Soltion  UPDATE  table name   SET  field name    replace same field name   unwanted text    wanted text

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] Find and replace entire mysql database

Examples related to mysql