{"id":125,"date":"2016-07-13T13:25:01","date_gmt":"2016-07-13T13:25:01","guid":{"rendered":"http:\/\/chkr.at\/wordpress\/?p=125"},"modified":"2016-07-13T13:25:13","modified_gmt":"2016-07-13T13:25:13","slug":"mysql","status":"publish","type":"post","link":"https:\/\/chkr.at\/wordpress\/?p=125","title":{"rendered":"MySQL and German Umlauts in a Unique Constraint"},"content":{"rendered":"<p>I recently came across some very odd behaviour in MySQL with <a href=\"http:\/\/joycep.myweb.port.ac.uk\/abinitio\/alphabet\/umlautsz.html\">German Umlauts<\/a> (\u00f6 \u00e4 \u00fc \u00df) and Unique Constraints. The problem is even documented as <a href=\"https:\/\/bugs.mysql.com\/bug.php?id=57860\">Bug #57860<\/a> on mysql.com. In short, MySQL (or rather <em>utf8_unicode_ci<\/em>) would suggest that <code>foob\u00e4r<\/code> is the same as <code>foobar<\/code>. So the statement<br \/>\n<code>INSERT INTO test (test) VALUES ('foobar'),('foob\u00e4r'); <\/code><br \/>\nwhere <em>test<\/em> is a column with a unique index\/constraint would fail.<\/p>\n<p>This behaviour might be desired in some languages, but particularly for the german language this behaviour is not optimal. I'm sure if you find this blog post, you came across the same problem so I do not need to come up with another example. <\/p>\n<p>The solution is simple, though you should think twice before you use it:<br \/>\nInstead of <em>utf8_unicode_ci<\/em>, you could use <em>utf8_general_ci<\/em> or even the newer and more appropriate <em>utf8_german2_ci<\/em> (available starting with MySQL 5.6).<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I recently came across some very odd behaviour in MySQL with German Umlauts (\u00f6 \u00e4 \u00fc \u00df) and Unique Constraints. The problem is even documented as Bug #57860 on mysql.com. In short, MySQL (or rather utf8_unicode_ci) would suggest that foob\u00e4r is the same as foobar. So the statement INSERT INTO test (test) VALUES ('foobar'),('foob\u00e4r'); where <a class=\"read-more\" href=\"https:\/\/chkr.at\/wordpress\/?p=125\">...continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[5,10],"tags":[],"_links":{"self":[{"href":"https:\/\/chkr.at\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/125"}],"collection":[{"href":"https:\/\/chkr.at\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/chkr.at\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/chkr.at\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/chkr.at\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=125"}],"version-history":[{"count":4,"href":"https:\/\/chkr.at\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/125\/revisions"}],"predecessor-version":[{"id":129,"href":"https:\/\/chkr.at\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/125\/revisions\/129"}],"wp:attachment":[{"href":"https:\/\/chkr.at\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=125"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/chkr.at\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=125"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/chkr.at\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=125"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}