{"id":37,"date":"2021-01-12T09:01:51","date_gmt":"2021-01-12T09:01:51","guid":{"rendered":"https:\/\/veltzer.net:8890\/?p=37"},"modified":"2021-01-12T09:02:32","modified_gmt":"2021-01-12T09:02:32","slug":"altering-the-character-set-of-a-mysql-database","status":"publish","type":"post","link":"https:\/\/veltzer.net:8890\/altering-the-character-set-of-a-mysql-database\/","title":{"rendered":"Altering the character set of a MySQL database"},"content":{"rendered":"\n<p>It happens often that I forget to change the default character set of a database to utf8 and so find out late in the development cycle that many of my fields are based on non utf8 character sets (mostly latin1). Then I go in and modify each field in turn using <code>ALTER TABLE [table] MODIFY [field name] [field type] CHARACTER SET [charset]<\/code>. After some digging I found the <code>ALTER TABLE $TABLE CONVERT TO CHARSET [charset]<\/code> syntax which converted all fields in a table to a certain character set. I looked for a similar syntax to convert the entire database and found <code>ALTER DATABASE<\/code> which, unfortunately, only changes the default character set and collation but does not affect the existing tables, fields or data.<\/p>\n\n\n\n<p>So here is a script that repeats <code>ALTER TABLE \/ CONVERT TO<\/code> on each table in your database:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>#!\/bin\/bash\n \n# parameters...\nUSER='&#91;your db user name]'\nPASS='&#91;your db password]'\nDB='&#91;your db]'\nCHARSET='&#91;character set (utf8?)]'\nCOLLATION='&#91;collation (utf8_unicode_ci?)]'\n \n# here we go...\nQUERY=\"SELECT table_name FROM information_schema.TABLES WHERE table_schema = '$DB';\"\nTABLES=$(mysql -u $USER --password=$PASS $DB --batch --skip-column-names --execute=\"$QUERY\")\nfor TABLE in $TABLES; do\n        echo \"ALTER TABLE $TABLE ......\"\n        mysql -u $USER --password=$PASS $DB -e \"ALTER TABLE $TABLE CONVERT TO CHARSET $CHARSET\"\n        #mysql -u $USER --password=$PASS $DB -e \"ALTER TABLE $TABLE CONVERT TO CHARSET $CHARSET COLLATE $COLLATION\"\ndone<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>It happens often that I forget to change the default character set of a database to utf8 and so find out late in the development cycle that many of my fields are based on non utf8 character sets (mostly latin1). Then I go in and modify each field in turn using ALTER TABLE [table] MODIFY &hellip; <\/p>\n<p class=\"link-more\"><a href=\"https:\/\/veltzer.net:8890\/altering-the-character-set-of-a-mysql-database\/\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;Altering the character set of a MySQL database&#8221;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[10],"tags":[26,4,28,31,23,29,30],"_links":{"self":[{"href":"https:\/\/veltzer.net:8890\/wp-json\/wp\/v2\/posts\/37"}],"collection":[{"href":"https:\/\/veltzer.net:8890\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/veltzer.net:8890\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/veltzer.net:8890\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/veltzer.net:8890\/wp-json\/wp\/v2\/comments?post=37"}],"version-history":[{"count":2,"href":"https:\/\/veltzer.net:8890\/wp-json\/wp\/v2\/posts\/37\/revisions"}],"predecessor-version":[{"id":39,"href":"https:\/\/veltzer.net:8890\/wp-json\/wp\/v2\/posts\/37\/revisions\/39"}],"wp:attachment":[{"href":"https:\/\/veltzer.net:8890\/wp-json\/wp\/v2\/media?parent=37"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/veltzer.net:8890\/wp-json\/wp\/v2\/categories?post=37"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/veltzer.net:8890\/wp-json\/wp\/v2\/tags?post=37"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}