Replace string in MySQL through database

December 2016


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

Related :

This document entitled « Replace string in MySQL through database » from CCM ( is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.