{"id":112,"date":"2013-01-08T13:12:34","date_gmt":"2013-01-08T13:12:34","guid":{"rendered":"http:\/\/www.nicovs.be\/?p=112"},"modified":"2013-01-10T21:28:45","modified_gmt":"2013-01-10T21:28:45","slug":"mysql-replacing-string-in-all-tables-of-a-database","status":"publish","type":"post","link":"https:\/\/www.nicovs.be\/?p=112","title":{"rendered":"Mysql: Replacing string in all tables of a database"},"content":{"rendered":"<p>Yes, of course you can replace a string in all fields of a specific table in a specific databases by using the REPLACE function.<br \/>\nNo, you can not easily do this on an entire database&#8230; you have to script this.<\/p>\n<p>I have found a Brilliant script <a title=\"Brilliansheep.com\" href=\"http:\/\/www.brilliantsheep.com\/replacing-a-string-in-all-tables-of-a-database-in-mysql\/\" target=\"_blank\">@BrillianSheep.com<\/a> which I modified a little bit for readability&#8230;<\/p>\n<p>Copy\/Paste the script in a php file, replace parameters (strings to replace: yourDbUser, dbP4sswd and yourDbName and run it on your server!<\/p>\n<pre class=\"lang:default decode:true\"><!--?php     \/\/ Setup the associative array for replacing the old string with new string     $replace_array = array( '\/var\/www\/vhosts\/' => '\/var\/www\/wwwroot', '\/home\/username\/user1' => '\/home\/user1');\r\n\r\n    $mysql_link = mysql_connect( 'localhost', 'yourDbUser', 'dbP4sswd' );\r\n    if( ! $mysql_link) {\r\n        die( 'Could not connect: ' . mysql_error() );\r\n    }\r\n\r\n    $mysql_db = mysql_select_db( 'yourDbName', $mysql_link );\r\n    if(! $mysql_db ) {\r\n        die( 'Can\\'t select database: ' . mysql_error() );\r\n    }\r\n\r\n    \/\/ Traverse all tables\r\n    $tables_query = 'SHOW TABLES';\r\n    $tables_result = mysql_query( $tables_query );\r\n    while( $tables_rows = mysql_fetch_row( $tables_result ) ) {\r\n        foreach( $tables_rows as $table ) {\r\n\r\n            \/\/ Traverse all columns\r\n            $columns_query = 'SHOW COLUMNS FROM ' . $table;\r\n            $columns_result = mysql_query( $columns_query );\r\n            while( $columns_row = mysql_fetch_assoc( $columns_result ) ) {\r\n\r\n                $column = $columns_row['Field'];\r\n                $type = $columns_row['Type'];\r\n\r\n                \/\/ Process only text-based columns\r\n                if( strpos( $type, 'char' ) !== false || strpos( $type, 'text' ) !== false ) {\r\n                    \/\/ Process all replacements for the specific column\r\n                    foreach( $replace_array as $old_string =&gt; $new_string ) {\r\n                        $replace_query = 'UPDATE ' . $table .\r\n                            ' SET ' .  $column . ' = REPLACE(' . $column .\r\n                            ', \\'' . $old_string . '\\', \\'' . $new_string . '\\')';\r\n                        mysql_query( $replace_query );\r\n                    }\r\n                }\r\n            }\r\n        }\r\n    }\r\n\r\n    mysql_free_result( $columns_result );\r\n    mysql_free_result( $tables_result );\r\n    mysql_close( $mysql_link );\r\n\r\n    echo 'Done!\r\n';\r\n\r\n?&gt;<\/pre>\n<p>--><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Yes, of course you can replace a string in all fields of a specific table in a specific databases by using the REPLACE function. No, you can not easily do this on an entire database&#8230; you have to script this. I have found a Brilliant script @BrillianSheep.com which I modified a little bit for readability&#8230; [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[3],"tags":[],"class_list":["post-112","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.nicovs.be\/index.php?rest_route=\/wp\/v2\/posts\/112","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.nicovs.be\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.nicovs.be\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.nicovs.be\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.nicovs.be\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=112"}],"version-history":[{"count":13,"href":"https:\/\/www.nicovs.be\/index.php?rest_route=\/wp\/v2\/posts\/112\/revisions"}],"predecessor-version":[{"id":120,"href":"https:\/\/www.nicovs.be\/index.php?rest_route=\/wp\/v2\/posts\/112\/revisions\/120"}],"wp:attachment":[{"href":"https:\/\/www.nicovs.be\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=112"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.nicovs.be\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=112"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.nicovs.be\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=112"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}