{"id":153,"date":"2012-01-04T01:56:38","date_gmt":"2012-01-04T06:56:38","guid":{"rendered":"http:\/\/jamesroberts.name\/blog\/?p=153"},"modified":"2012-01-04T02:09:04","modified_gmt":"2012-01-04T07:09:04","slug":"mysql-collation-change-script","status":"publish","type":"post","link":"https:\/\/jamesroberts.name\/blog\/2012\/01\/04\/mysql-collation-change-script\/","title":{"rendered":"php mysql collation change script"},"content":{"rendered":"<p>There are times when we get stuck with bad collations on our mysql tables and we are overwhelmed by having to change them by hand.  Well, I have written a php script which will go through all your tables and fields in a database and update them to the collation which works for you.  In my case for this example, I have a database which contains a mix of collations and changes them all to utf8_unicode_ci.  This example uses the mysqli object in php as the database connection.  <\/p>\n<p>Your will need to update the database connection credentials as they are all bogus in this example.  You will also need to change the &#8220;$execute_sql&#8221; to equal true if you want the script to actually perform the collation change via this script. <\/p>\n<pre lang=\"php\">\r\n<?php\r\n\t\r\n\t$execute_sql = false;\r\n\r\n\t$host = 'mydbhost';\r\n\t$username = 'username';\r\n\t$password = 'passsword';\r\n\t$dbname = 'databasename';\r\n\t\r\n\t$db = new mysqli($host, $username, $password, $dbname);\r\n\r\n\t$mysqldatabase = 'databasename';\r\n\t$collation = 'CHARACTER SET utf8 COLLATE utf8_unicode_ci';\r\n\t\r\n\techo '<div>';\r\n\t$db->query(\"ALTER DATABASE $mysqldatabase $collation\");\r\n\t\r\n\t$result = $db->query(\"SHOW TABLES\");\r\n\t\r\n\t$count = 0;\r\n\twhile($row = $result->fetch_assoc()) {\r\n\t\t$table = $row['Tables_in_'.$mysqldatabase];\r\n\t\tif($execute_sql) $db->query(\"ALTER TABLE $table DEFAULT $collation\");\r\n\t\t$result1 = $db->query(\"SHOW COLUMNS FROM $table\");\r\n\t\t$alter = '';\r\n\t\twhile($row1 = $result1->fetch_assoc()) {\r\n\t\t\tif (preg_match('~char|text|enum|set~', $row1[\"Type\"])) {\r\n\t\t\t\tif(strpos($row1[\"Field\"], 'uuid')){\r\n\t\t\t\t\t\/\/ why does this not work\r\n\t\t\t\t}else{\r\n\t\t\t\t\t$alter .= (strlen($alter)?\", \\n\":\" \") . \"MODIFY `$row1[Field]` $row1[Type] $collation\" . ($row1[\"Null\"] ? \"\" : \" NOT NULL\") . ($row1[\"Default\"] && $row1[\"Default\"] != \"NULL\" ? \" DEFAULT '$row1[Default]'\" : \"\");\r\n\t\t\t\t}\r\n\t\t\t}\r\n\t\t}\r\n\t\tif(strlen($alter)){\r\n\t\t\t$sql = \"ALTER TABLE $table\".$alter.\";\";\r\n\t\t\techo \"<div>$sql\\n\\n<\/div>\";\r\n\t\t\tif($execute_sql) $db->query($sql);\r\n\t\t}\r\n\t\t$count++;\r\n\t}\r\n\techo '<\/div>';\r\n\t\r\n?>\r\n<\/pre>\n<p>The only gotcha that I have run into with my script that I have yet to solve is that when you change the collation of an existing uuid field which has a foreign key, the copy table fails in mysql.  Not that the collation of a uuid field really matters, but you can see a comment in the routine which questions why this does not work.  <\/p>\n<p>Anyway, I hope someone finds this useful =)  Enjoy&#8230;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>There are times when we get stuck with bad collations on our mysql tables and we are overwhelmed by having to change them by hand. Well, I have written a php script which will go through all your tables and fields in a database and update them to the collation which works for you. In [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[33,10],"tags":[38,39,65,62],"_links":{"self":[{"href":"https:\/\/jamesroberts.name\/blog\/wp-json\/wp\/v2\/posts\/153"}],"collection":[{"href":"https:\/\/jamesroberts.name\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jamesroberts.name\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jamesroberts.name\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jamesroberts.name\/blog\/wp-json\/wp\/v2\/comments?post=153"}],"version-history":[{"count":11,"href":"https:\/\/jamesroberts.name\/blog\/wp-json\/wp\/v2\/posts\/153\/revisions"}],"predecessor-version":[{"id":157,"href":"https:\/\/jamesroberts.name\/blog\/wp-json\/wp\/v2\/posts\/153\/revisions\/157"}],"wp:attachment":[{"href":"https:\/\/jamesroberts.name\/blog\/wp-json\/wp\/v2\/media?parent=153"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jamesroberts.name\/blog\/wp-json\/wp\/v2\/categories?post=153"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jamesroberts.name\/blog\/wp-json\/wp\/v2\/tags?post=153"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}