归档的非法组合PHP MYSQL，latin1_swedish_ci和utf8_general_ci [英] Illegal mix of collations PHP MYSQL, latin1_swedish_ci and utf8_general_ci

问题描述

  SQLSTATE [
]我在应用程序中遇到以下coallation问题： HY000]：一般错误：1267操作'='
  

$的非法混合排序规则（latin1_swedish_ci，IMPLICIT）和（utf8_general_ci，COERCIBLE） b $ b

在stacktrace中我可以看到参数Lamellt \xE4ckning，这意味着Lamelltäckning，我认为我的参数隐式调用latin1_swedish_ci coallation。

我的整个数据库使用此：

  DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci 
  

---

当我从PHP插入字符串时，我只是简单地插入：

  $ name =Lamelltäcke; 
 $ db-> update（insert into ....）; 
  

---

我现在想要使用的数据来自一个CSV文件，我不知道我是否可以解决这只是通过设置coallation我有一些方式，或者如果我需要以某种方式转换字符串

什么是这里的问题吗？

---

当我从PHP插入数据时，接缝是一个问题。我做了这样的pdo连接：

  $ db = new \ PDO（$ dsn，$ config-& $ config-> db_pass，array（\PDO :: MYSQL_ATTR_INIT_COMMAND =>SET NAMES utf8））; 
  

当我定义一个字符串

  $ str =åuuäuuö; 
  

在PHP中插入它没有问题。但是当我收到来自一个post请求的字符串，我可以回应它只是很好

 åuuäuuö
  
 
 
 
在数据库中，现在插入
 ？uu？uu？ 
 
 mb_detect_encoding（$ str）; 
  
提供： UTF-8  
 
 
 
 
 
 
问题是字符串本身的编码。我的数据库使用UTF-8，但编码是ISO-8859-x。为了使一切更糟，我的Java客户端也有另一个编码，这使得这很难调试。它被称为引用字符串。
 
 
 
最终帮助我解决问题的是这段PHP代码，它接受一个String，并将其从所有可能的编码转换为UTF- 8并打印。 查找正确打印字符串的行，并且有字符串的编码。然后，当您获得正确的编码时，使用 
 
 
 
  $ str =带有äåö或其他字符的未知编码字符串; 
 
 foreach（mb_list_encodings（）as $ chr）{
 echo mb_convert_encoding（$ str，'UTF-8'，$ chr）。：。$ chr。\r\\ \\ n; 
} 
  
注意要确保客户端也使用正确的编码。在我的case这是一个Java程序，在正常情况下，这将是你的webapp /浏览器和。
解决方案
 
 
 
尝试将PHP文件转换为UTF8 
 
 
在数据库连接后，使用 SET NAMES'utf8' 
 
 
 
 
 
此外，请检查您的字段字符集是否为UTF8。
 
I get the following coallation problem in my application when I try to select something where two strings are equal: 
SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
In the stacktrace I can see the parameter Lamellt\xE4ckning which means Lamelltäckning and I think my parameter implicitly invokes the latin1_swedish_ci coallation.

my whole database uses this:
DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci




When I insert Strings from PHP I just make a simple insert:
$name = "Lamelltäcke";
$db->update("insert into....");




The data I am now trying to use comes from a CSV file and I do not know if I can solve this just by setting coallation i some way or if I need to convert the String in some way

What is the problem here? And how can I solve it?



It seams to be a problem when I insert data from PHP. I made the pdo connection like this:
$db = new \PDO($dsn, $config->db_user, $config->db_pass, array(\PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
When I define a string like 
$str = "åuuäuuö";
In PHP and insert it there is no problem. But when I receive the string from a post request I can echo it out just fine 
 "åuuäuuö"
In the database however it now gets inserted like 
"?uu?uu?"

mb_detect_encoding($str);
gives: UTF-8



The problem was the encoding of the string itself. My database uses UTF-8 but the encoding was ISO-8859-x. To make everything worse my Java client also had another encoding which made this hard to debug. It is called "Quoted String".

What finally helped me solve the problem was this piece of PHP code which takes a String and converts it from all possible encodings to UTF-8 and prints it. Look for a row where your string is printed correctly and there is your encoding of the string. Then when you obtain the correct encoding, rencode your string using mb_convert_encoding.
$str = "String of unknown encoding with chars like äåö or something else";

foreach(mb_list_encodings() as $chr){
            echo mb_convert_encoding($str, 'UTF-8', $chr)." : ".$chr."\r\n";
        } 
A note is to make sure that the client also uses the right encoding. In my case this was a Java program, in a normal case this would be your webapp/browser and .
解决方案

Try to convert your PHP file to UTF8
Right after database connection, make a query with SET NAMES 'utf8' 


Also, check that your field charset is UTF8.

                        
这篇关于归档的非法组合PHP MYSQL，latin1_swedish_ci和utf8_general_ci的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！