A few words of thanks would be greatly appreciated.

Replace string in MySQL through database


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.


Before proceeding make sure to make a backup of your work!
Please click here to know more about backup and 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.


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 "); 

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
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).