{"id":327,"date":"2017-01-23T03:22:59","date_gmt":"2017-01-22T19:22:59","guid":{"rendered":"http:\/\/vinta.ws\/code\/?p=327"},"modified":"2026-02-18T01:20:36","modified_gmt":"2026-02-17T17:20:36","slug":"convert-utf8-tables-to-utf8mb4-in-mysql","status":"publish","type":"post","link":"https:\/\/vinta.ws\/code\/convert-utf8-tables-to-utf8mb4-in-mysql.html","title":{"rendered":"Convert utf8 tables to utf8mb4 in MySQL"},"content":{"rendered":"<h2>Change Schema<\/h2>\n<pre class=\"line-numbers\"><code class=\"language-sql\"># For each database:\nALTER DATABASE\n    database_name\n    CHARACTER SET = utf8mb4\n    COLLATE = utf8mb4_unicode_ci;\n\n# For each table:\nALTER TABLE\n    table_name\n    CONVERT TO CHARACTER SET utf8mb4\n    COLLATE utf8mb4_unicode_ci;\n\n# For each column:\nALTER TABLE\n    table_name\n    CHANGE column_name column_name\n    VARCHAR(191)\n    CHARACTER SET utf8mb4\n    COLLATE utf8mb4_unicode_ci;\n\nALTER TABLE\n    svcomments_svcomment\n    CHANGE comment comment \n    longtext\n    CHARACTER SET utf8mb4\n    COLLATE utf8_general_ci;\n\nSHOW VARIABLES WHERE Variable_name LIKE 'character%' OR Variable_name LIKE 'collation%';\n\n# Don\u2019t blindly copy-paste this!\n# The exact statement depends on the column type, maximum length, and other properties.\n# The above line is just an example for a <code>VARCHAR<\/code> column.<\/code><\/pre>\n<p>ref:<br \/>\n<a href=\"https:\/\/coderwall.com\/p\/pns4pa\/setting-up-unicode-defaults-for-mariadb-or-mysql\">https:\/\/coderwall.com\/p\/pns4pa\/setting-up-unicode-defaults-for-mariadb-or-mysql<\/a><br \/>\n<a href=\"https:\/\/mathiasbynens.be\/notes\/mysql-utf8mb4\">https:\/\/mathiasbynens.be\/notes\/mysql-utf8mb4<\/a><br \/>\n<a href=\"http:\/\/stackoverflow.com\/questions\/766809\/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci\">http:\/\/stackoverflow.com\/questions\/766809\/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci<\/a><br \/>\n<a href=\"http:\/\/dba.stackexchange.com\/questions\/8239\/how-to-easily-convert-utf8-tables-to-utf8mb4-in-mysql-5-5\">http:\/\/dba.stackexchange.com\/questions\/8239\/how-to-easily-convert-utf8-tables-to-utf8mb4-in-mysql-5-5<\/a><\/p>\n<h2>Application Settings<\/h2>\n<p>Take a Django project as an example.<\/p>\n<p>in settings.py<\/p>\n<pre class=\"line-numbers\"><code class=\"language-py\">DATABASES = {\n    'default': {\n        'ENGINE': 'django.db.backends.mysql',\n        ...\n        'OPTIONS': {\n            'charset': 'utf8mb4',\n        },\n        'TEST': {\n            'CHARSET': 'utf8mb4',\n            'COLLATION': 'utf8mb4_general_ci',\n        },\n    },\n}<\/code><\/pre>\n<p>ref:<br \/>\n<a href=\"https:\/\/tzangms.com\/use-emoji-in-mysql-with-django\/\">https:\/\/tzangms.com\/use-emoji-in-mysql-with-django\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Don\u2019t blindly copy-paste this!<\/p>\n","protected":false},"author":1,"featured_media":328,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22],"tags":[59,23],"class_list":["post-327","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-about-database","tag-database-migrations","tag-mysql"],"_links":{"self":[{"href":"https:\/\/vinta.ws\/code\/wp-json\/wp\/v2\/posts\/327","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/vinta.ws\/code\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vinta.ws\/code\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vinta.ws\/code\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/vinta.ws\/code\/wp-json\/wp\/v2\/comments?post=327"}],"version-history":[{"count":0,"href":"https:\/\/vinta.ws\/code\/wp-json\/wp\/v2\/posts\/327\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/vinta.ws\/code\/wp-json\/wp\/v2\/media\/328"}],"wp:attachment":[{"href":"https:\/\/vinta.ws\/code\/wp-json\/wp\/v2\/media?parent=327"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vinta.ws\/code\/wp-json\/wp\/v2\/categories?post=327"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vinta.ws\/code\/wp-json\/wp\/v2\/tags?post=327"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}