0
Thanks

A few words of thanks would be greatly appreciated.

Replace string in MySQL through database




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

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

0 Comments