Replace string in MySQL through database

June 2017




Issue


How do you replace a string with another in all tables in a database?
Replace requires that he gives the name of a table and column, so it's not usable like that.

Example :
Replace 'string1' with 'word1' in database 'test' on the 50 tables that compose a single line.

Solution


Before proceeding make sure to make a backup of your work!
Please click here to know more about backup and restore mysql database:
http://ccm.net/faq/2884-backup-restore-mysql-database

If you use MySQL 5.1, you can you use the information_schema database, which contains among others the list of all tables in your database ...
then you can for example be used to generate your request.


Ex:

SELECT CONCAT ('UPDATE test.', TABLE_NAME, 'SET field1 = REPLACE (field1, "string1", "string1 ");') INFORMATION_SCHEMA.TABLES FROM WHERE TABLE_SCHEMA =' test ';  


- > This query will give you a result that you can just paste in the client:
test.table1 UPDATE SET field1 = REPLACE (field1, "string1", "string1")  
test.table2 UPDATE SET field1 = REPLACE (field1, " string1 "," string1 "); 

Related


Published by aakai1056. Latest update on May 7, 2010 at 09:43 AM by aakai1056.
This document, titled "Replace string in MySQL through database," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).