{"id":1327,"date":"2018-07-18T13:00:03","date_gmt":"2018-07-18T12:00:03","guid":{"rendered":"http:\/\/chewett.co.uk\/blog\/?p=1327"},"modified":"2018-07-17T22:26:46","modified_gmt":"2018-07-17T21:26:46","slug":"how-to-convert-an-entire-database-to-a-new-character-set-collation","status":"publish","type":"post","link":"https:\/\/chewett.co.uk\/blog\/1327\/how-to-convert-an-entire-database-to-a-new-character-set-collation\/","title":{"rendered":"How to convert an entire database to a new character set collation"},"content":{"rendered":"

\"\"<\/p>\n

This post talks about how you can convert an entire database to a specific collection.<\/p>\n

<\/p>\n

Why you might want to change the collation of all tables<\/h2>\n

One of the most common reasons for changing many tables collations is to support a newly expanded character set. Many databases have been created with a standard ASCII\u00a0character set, latin1. This means that if you want to start supporting Unicode characters you will need to change the collation.<\/p>\n

Changing a single tables character set collation<\/h2>\n

To change a single table to a different you can run the following command<\/p>\n

ALTER TABLE tablename CONVERT TO CHARACTER SET <character set> COLLATE <collaction>;<\/pre>\n
This will update the table to the new character set and collation. In doing so this will apply appropriate conversion of data types from one type to another.<\/p>\n
For example to convert a table to utf8_unicode_ci<\/code> you can run the following command.<\/p>\n
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;<\/pre>\n
Before performing this operation you will want to back up the tables you are modifying. This is because when a conversion between character sets is made there may be some lost data. This especially applies if you are converting from a highly expressive character set to a less expressive one. For example if you are converting from a utf8 character set to a ascii one many of the higher valued characters will be converted or lost.<\/p>\n
This may cause you to lose some of the content stored in the database. It is recommended if converting to a less expressive collection to manually convert it. This will mean you are in control of how the data is converted before applying the database change.<\/p>\n
Changing all tables collation<\/h2>\n
To change all tables to a specific collation you can use the below snippet to create the queries you need.<\/p>\n
SELECT CONCAT('ALTER TABLE `', TABLE_NAME,\r\n'` CONVERT TO CHARACTER SET <character set> COLLATE <collation>;') AS mySQL\r\nFROM INFORMATION_SCHEMA.TABLES\r\nWHERE TABLE_SCHEMA= \"schema name\"\r\nAND TABLE_TYPE=\"BASE TABLE\"\r\nlimit 10000<\/pre>\n
This was taken from an answer on stack overflow<\/a> and creates the statements needed to convert all tables. Once the queries have been created you can decide which tables you want to convert if you don’t want to convert all of them.<\/p>\n
Using these queries you will hopefully be able to convert the table or tables to a new character set and collation.<\/p>\n","protected":false},"excerpt":{"rendered":"
This post talks about how you can convert an entire database to a specific collection.<\/p>\n","protected":false},"author":1,"featured_media":1350,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"How to convert an entire #databases to a new charcter set collection #MySQL","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[5],"tags":[285,60,286],"class_list":["post-1327","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-informational","tag-database","tag-mysql","tag-mysql-database"],"wppr_data":{"cwp_meta_box_check":"No"},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2018\/07\/converting_character_set_database.jpg?fit=800%2C800&ssl=1","jetpack_shortlink":"https:\/\/wp.me\/p2toWX-lp","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":1713,"url":"https:\/\/chewett.co.uk\/blog\/1713\/running-two-mysql-databases-on-windows\/","url_meta":{"origin":1327,"position":0},"title":"Running two MySQL databases on Windows","author":"Chewett","date":"November 10, 2018","format":false,"excerpt":"This post describes the process you need to follow to set up a second MySQL database on Windows. Preparation\u00a0for configuring a secondary database The base of this guide assumes that you have installed MySQL server for windows already and it is running as normally. It is assumed that the location\u2026","rel":"","context":"In "Software"","block_context":{"text":"Software","link":"https:\/\/chewett.co.uk\/blog\/category\/software\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2018\/11\/running_multiple_databases.fw_.png?fit=800%2C800&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2018\/11\/running_multiple_databases.fw_.png?fit=800%2C800&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2018\/11\/running_multiple_databases.fw_.png?fit=800%2C800&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2018\/11\/running_multiple_databases.fw_.png?fit=800%2C800&ssl=1&resize=700%2C400 2x"},"classes":[]},{"id":2783,"url":"https:\/\/chewett.co.uk\/blog\/2783\/how-to-install-a-mysql-server-on-a-raspberry-pi\/","url_meta":{"origin":1327,"position":1},"title":"How to install a MySQL Server on a Raspberry Pi","author":"Chewett","date":"January 30, 2021","format":false,"excerpt":"This blog post explains the steps you need to perform to install a MySQL server on a Raspberry Pi. Ensuring the Raspberry Pi is up to date Before installing any programs it is recommended to make sure your Raspberry Pi is up to date. This reduces the change of a\u2026","rel":"","context":"In "Informational"","block_context":{"text":"Informational","link":"https:\/\/chewett.co.uk\/blog\/category\/informational\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/01\/install_mysql_raspi_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/01\/install_mysql_raspi_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/01\/install_mysql_raspi_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/01\/install_mysql_raspi_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/01\/install_mysql_raspi_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":2207,"url":"https:\/\/chewett.co.uk\/blog\/2207\/distributed-automated-halite-3-bot-tester\/","url_meta":{"origin":1327,"position":2},"title":"Distributed Automated Halite 3 Bot Tester","author":"Chewett","date":"June 1, 2019","format":false,"excerpt":"In this post I talk about the work I did to improve the Halite 3 Bot testing gym to speed up iterations and compare various versions of my bot. Why I spent time Improving the Halite Gym During the competition of Halite 3 one piece of provided code was a\u2026","rel":"","context":"In "Software"","block_context":{"text":"Software","link":"https:\/\/chewett.co.uk\/blog\/category\/software\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2019\/06\/halite_distributed_gym.jpg?fit=654%2C653&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2019\/06\/halite_distributed_gym.jpg?fit=654%2C653&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2019\/06\/halite_distributed_gym.jpg?fit=654%2C653&ssl=1&resize=525%2C300 1.5x"},"classes":[]},{"id":199,"url":"https:\/\/chewett.co.uk\/blog\/199\/bigint-overflow-error-based-sql-injection\/","url_meta":{"origin":1327,"position":3},"title":"BIGINT Overflow Error Based SQL Injection","author":"Chewett","date":"February 14, 2016","format":false,"excerpt":"In MySQL 5.5+ you can abuse a new feature with BIGINT values. This involves a problem called integer rollover and your ability to run arbitrary SQL. The problem of integer rollover Integer rollover happens when a number is too big or small and is made bigger\/smaller. In the case where\u2026","rel":"","context":"In "Informational"","block_context":{"text":"Informational","link":"https:\/\/chewett.co.uk\/blog\/category\/informational\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":569,"url":"https:\/\/chewett.co.uk\/blog\/569\/creating-raspberry-pi-backup-script-scp-bash-windows\/","url_meta":{"origin":1327,"position":4},"title":"Creating a Raspberry Pi backup script with scp and Bash on Windows","author":"Chewett","date":"August 30, 2017","format":false,"excerpt":"This post describes a simple way to keep backups of a Raspberry Pi or any other Linux computer. Why do I need backups of my Raspberry Pi? Some of the nodes in my cluster are going to be compute only, meaning that they will just run what is asked of\u2026","rel":"","context":"In "Informational"","block_context":{"text":"Informational","link":"https:\/\/chewett.co.uk\/blog\/category\/informational\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2017\/08\/backing_up_a_rasperry_pi.jpg?fit=800%2C800&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2017\/08\/backing_up_a_rasperry_pi.jpg?fit=800%2C800&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2017\/08\/backing_up_a_rasperry_pi.jpg?fit=800%2C800&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2017\/08\/backing_up_a_rasperry_pi.jpg?fit=800%2C800&ssl=1&resize=700%2C400 2x"},"classes":[]},{"id":1321,"url":"https:\/\/chewett.co.uk\/blog\/1321\/how-to-install-the-official-mysql-community-server-on-fedora-28\/","url_meta":{"origin":1327,"position":5},"title":"How to Install the official MySQL community server on Fedora 28","author":"Chewett","date":"July 25, 2018","format":false,"excerpt":"Today I talk about how you can install the official\u00a0MySQL community server on Fedora 28. Why you cant just use DNF to install mysql-server In the last couple versions of Fedora the default MySQL\u00a0distribution was changed to mariadb. While this is, for the most part, a drop in replacement this\u2026","rel":"","context":"In "Informational"","block_context":{"text":"Informational","link":"https:\/\/chewett.co.uk\/blog\/category\/informational\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2018\/07\/installing_offical_mysql.jpg?fit=800%2C800&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2018\/07\/installing_offical_mysql.jpg?fit=800%2C800&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2018\/07\/installing_offical_mysql.jpg?fit=800%2C800&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2018\/07\/installing_offical_mysql.jpg?fit=800%2C800&ssl=1&resize=700%2C400 2x"},"classes":[]}],"_links":{"self":[{"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/posts\/1327","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/comments?post=1327"}],"version-history":[{"count":6,"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/posts\/1327\/revisions"}],"predecessor-version":[{"id":1352,"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/posts\/1327\/revisions\/1352"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/media\/1350"}],"wp:attachment":[{"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/media?parent=1327"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/categories?post=1327"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/tags?post=1327"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}