{"id":230,"date":"2011-07-12T09:48:18","date_gmt":"2011-07-12T07:48:18","guid":{"rendered":"http:\/\/blog.hermione.de\/?p=230"},"modified":"2011-07-12T10:03:48","modified_gmt":"2011-07-12T08:03:48","slug":"remove-annoying-collation-mysql","status":"publish","type":"post","link":"http:\/\/blog.hermione.de\/?p=230","title":{"rendered":"Remove annoying collation [mysql]"},"content":{"rendered":"<h4>The problem<\/h4>\n<pre>select t.id from tmplay as t, postpl as p where p.zip = t.zip;\r\n\r\nERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) \r\nand (latin1_german1_ci,IMPLICIT) for operation '='\r\n<\/pre>\n<h4>There is the rub<\/h4>\n<pre>\r\nselect \r\n    table_schema, \r\n    table_name, \r\n    column_name, \r\n    character_set_name, \r\n    collation_name \r\nfrom information_schema.columns \r\n   where collation_name = 'latin1_swedish_ci';<\/pre>\n<h4>The quick and dirty solution<\/h4>\n<p>Fuer den Fall, dass man an der collation Sache kein Interesse hat, d.h. es keine Rolle spielt ob das \u00d6 schwedisch ist oder deutsch. In meinem Beispiel ist das der Fall, da es um Postleitzahlen geht, also nur Ziffern im String vorkommen.<\/p>\n<pre>alter table postpl convert to character set latin1 collate 'latin1_german1_ci';<\/pre>\n<h4>Links<\/h4>\n<li>http:\/\/www.interworks.com\/blogs\/bbausili\/2010\/01\/11\/mysql-fixing-illegal-mix-collation<\/li>\n<li>http:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/charset-collate.html<\/li>\n","protected":false},"excerpt":{"rendered":"<p>The problem select t.id from tmplay as t, postpl as p where p.zip = t.zip; ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_german1_ci,IMPLICIT) for operation &#8218;=&#8216; There is the rub select table_schema, table_name, column_name, character_set_name, collation_name from information_schema.columns where collation_name = &#8218;latin1_swedish_ci&#8216;; The quick and dirty solution Fuer den Fall, dass man an [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"_links":{"self":[{"href":"http:\/\/blog.hermione.de\/index.php?rest_route=\/wp\/v2\/posts\/230"}],"collection":[{"href":"http:\/\/blog.hermione.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/blog.hermione.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/blog.hermione.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/blog.hermione.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=230"}],"version-history":[{"count":8,"href":"http:\/\/blog.hermione.de\/index.php?rest_route=\/wp\/v2\/posts\/230\/revisions"}],"predecessor-version":[{"id":237,"href":"http:\/\/blog.hermione.de\/index.php?rest_route=\/wp\/v2\/posts\/230\/revisions\/237"}],"wp:attachment":[{"href":"http:\/\/blog.hermione.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=230"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/blog.hermione.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=230"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/blog.hermione.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=230"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}