{"id":2680,"date":"2024-12-31T20:26:01","date_gmt":"2024-12-31T20:26:01","guid":{"rendered":"https:\/\/meetanshi.com\/blog\/solved-error-1273-unknown-collation-utf8mb4_0900_ai_ci-in-mysql\/"},"modified":"2025-06-16T12:19:53","modified_gmt":"2025-06-16T06:49:53","slug":"error-1273-unknown-collation-utf8mb4-0900-ai-ci-in-mysql","status":"publish","type":"post","link":"https:\/\/meetanshi.com\/blog\/error-1273-unknown-collation-utf8mb4-0900-ai-ci-in-mysql\/","title":{"rendered":"Solved: Error 1273 Unknown Collation utf8mb4_0900_ai_ci in MySQL"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Did you just stumble upon the Unknown Collation: utf8mb4_0900_ai_ci&nbsp;error in MySQL? Well, that&#8217;s because of the character set mismatch between the databases. Read this blog post till the end to find the solution.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Recently, I was manually importing a database to a new server using MySQL. Things were going well&#8230; until my screen flashed &#8211; <em><strong>Unknown collation: &#8216;utf8mb4_0900_ai_ci&#8217;<\/strong><\/em> \u26a0<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">I dug up further to find the actual cause and found that the collation of the new database was different from the old one. In this post, I am going to share the solution I used to fix this issue. But let&#8217;s first understand what &#8216;Collation&#8217; mean in MySQL.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">What is Collation?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">\u201cA collation is a set of rules that defines how to compare and sort character strings. Each collation in MySQL belongs to a single character set. Every character set has at least one collation, and most have two or more collations.\u201d according to MySQL.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">SQL servers use collations that define the character sets, along with their sorting rules and case &amp; accent sensitivity properties. Each part of the collation, separated by an underscore _, defines certain properties.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Take the collation \u2014 &#8216;utf8mb4_0900_ai_ci&#8217; for example. Here the first part (utf8mb4) refers to a 4-Byte UTF-8 Unicode Encoding character set. The number 0900 refers to the Unicode Collation Algorithm version. And the last parts define the accent &amp; case insensitivities.&nbsp; <a title=\"SQL query for case insensitive data\" href=\"https:\/\/meetanshi.com\/blog\/use-sql-query-for-case-sensitive-data-in-magento-2\/\" target=\"_blank\" rel=\"noopener\">Learn SQL query for case-insensitive data<\/a>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Enough of the theory part..! Now, let&#8217;s come to a solution to the <em><strong>Unknown collation:<\/strong> <\/em><em><strong>&#8216;utf8mb4_0900_ai_ci&#8217;<\/strong><\/em> error in MySQL.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">The solution to Unknown Collation utf8mb4_0900_ai_ci Error in MySQL<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Simply follow these steps to solve the Unknown Collation utf8mb4_0900_ai_ci Error in MySQL:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Step 1:&nbsp;<\/strong>Open the <em><strong>dump.sql file<\/strong><\/em> in any editor.<\/li>\n\n\n\n<li><strong>Step 2:&nbsp;<\/strong>Find &#8216;utf8mb4_0900_ai_ci&#8217; &amp; replace it with &#8216;utf8mb4_unicode_ci&#8217;.<\/li>\n\n\n\n<li><strong>Step 3:&nbsp;<\/strong>Save the file.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Bingo! The error will be solved.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">I hope the above-provided solution will be helpful to you in solving the &#8216;unknown Collation utf8mb4_0900_ai_ci error in MySQL&#8217;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Comment if you still have any queries or doubts.  <\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/meetanshi.com\/blog\/error-1193-hy000-unknown-system-variable-gtid-purged\/\">ERROR 1193 (HY000): Unknown system variable \u2018GTID_PURGED<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Also, share this solution via social media with your developer friends.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Thanks for reading!  <\/p>\n","protected":false},"excerpt":{"rendered":"<p>Did you just stumble upon the Unknown Collation: utf8mb4_0900_ai_ci&nbsp;error in MySQL? Well, that&#8217;s because of the character set mismatch between the databases. Read this blog&#8230;<\/p>\n","protected":false},"author":13,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[34],"tags":[],"class_list":["post-2680","post","type-post","status-publish","format-standard","hentry","category-magento"],"acf":[],"_links":{"self":[{"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/posts\/2680","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/users\/13"}],"replies":[{"embeddable":true,"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/comments?post=2680"}],"version-history":[{"count":3,"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/posts\/2680\/revisions"}],"predecessor-version":[{"id":16829,"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/posts\/2680\/revisions\/16829"}],"wp:attachment":[{"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/media?parent=2680"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/categories?post=2680"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/tags?post=2680"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}