字符串比较使用PHP mysql_ * SET NAMES UTF 8和Mysql Table使用utf8_unicode_ci [英] String Comparison using PHP mysql_* SET NAMES UTF 8 and Mysql Table With utf8_unicode_ci

问题描述

我有一个包含列 State 的Mysql表格 - 状态来自欧洲 - 表格和列位于 utf8_unicode_ci p>

当我调用数据库时，我使用

  mysql_select_db（$ database_WTF，$ WTF）; 
 mysql_query（'SET NAMES utf8'）; 
 $ query_Recordset1 =SELECT * 
 FROM newmeets 
 WHERE newmeets.`State` is NOT NULL 
 AND newmeets.`State`！=''
 ORDER BY newmeets .`State` ASC; 
 

然后我通过这个简单的循环运行

  mysql_select_db（$ database_WTF，$ WTF）; 
 mysql_query（'SET NAMES utf8'）; 
 $ query_Recordset1 =SELECT * 
 FROM newmeets 
 WHERE newmeets.`State` is NOT NULL 
 AND newmeets.`State`！=''
 ORDER BY newmeets .`State` ASC; 
 
 $ LastState =; 
 do {
 echo 
 var_dump（$ LastState == $ row_Recordset1 ['State']）; 
 echo $ row_Recordset1 ['State']; 
 $ LastState = $ row_Recordset1 ['State']; 
 var_dump（$ LastState == $ row_Recordset1 ['State']）; 
 echo< hr>; 
} while（$ row_Recordset1 = mysql_fetch_assoc（$ Recordset1））; 
  

第一次按预期命中新值时：

布尔值false

BRUSSELS

boolean true

下一次我获得：

布尔值true

BRUSSELS

boolean true

与我预期的一样。

每当通过时产生非拉丁字符，就会出现

：

布尔值false

Baden-Württember

boolean true

当我将它们设置为相等时，记录被拉（并且每个记录已经被相同地输入）失败，这是当我使用相同的编码，我实际上需要它以识别他们是相同的，即使字符输入不同。

无论发生了什么，我尝试了 utf8_encode 和 Normalizer :: normalize 比较的真实结果，所以我可以在控制结构中使用（例如

解决方案

看起来你有数据库中的规范化问题。

这也会确保你不会重复存储相同的状态，而不是一次又一次地存储相同的状态。 - 例如。

或者，你应该查询不同的行，并且将不同的数据转换成不同的行，你不能像以前一样正确对齐。更新它们，以便您至少对同名状态具有相同的二进制字符串数据。例如。如果Mysql能够实际对齐这些状态字符串，但PHP - 由于它是字符串的二进制属性 - 不是。

I have a Mysql table with column State - the states are from across Europe - and the table and columns are in utf8_unicode_ci.

When I call the database I use

mysql_select_db($database_WTF, $WTF);
mysql_query('SET NAMES utf8');
$query_Recordset1 = "SELECT * 
     FROM newmeets
     WHERE newmeets.`State` IS NOT NULL 
     AND newmeets.`State` !=  ''
     ORDER BY newmeets.`State` ASC ";

I then run it though this simple loop

mysql_select_db($database_WTF, $WTF);
mysql_query('SET NAMES utf8');
$query_Recordset1 = "SELECT * 
     FROM newmeets
     WHERE newmeets.`State` IS NOT NULL 
     AND newmeets.`State` !=  ''
     ORDER BY newmeets.`State` ASC ";

$LastState = "";
do {
    echo
    var_dump($LastState == $row_Recordset1['State']);
    echo $row_Recordset1['State'];
    $LastState = $row_Recordset1['State'];
    var_dump($LastState == $row_Recordset1['State']);
    echo "<hr>";
} while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));

The first time when it hits a new value as expected I get:

boolean false
BRUSSELS
boolean true

The next time I get:

boolean true
BRUSSELS
boolean true

Much as I would expect.

The problem comes when it hits non latin characters when each pass produces:

boolean false
Baden-Württember
boolean true

When I set them to be equal they are, and then when the next record is pulled (and each record has been entered identically) it fails, this is when I have used the same encoding, I actually need it to recognise they are the same even if the characters are entered differently.

Regardless of what is happening I have tried utf8_encode and Normalizer::normalize to get a true result from the comparison so I can use this in a control structure (e.g. if you've seen it last time don't print it this time) but it fails.

解决方案

It looks like you have a normalization problem in your database. Instead of storing the same state as string over and over again, put all state names into a table of it's own and reference them.

This will also ensure that you do not - e.g. by accident - put binary different but equally looking data into different rows you're not able to properly align later on as you just did.

Alternatively you should query distinct rows and update them, so that you do at least have the same binary string data for same-named states. E.g. if Mysql is able to actually align these state strings but PHP - due to it's binary nature of strings - is not.

这篇关于字符串比较使用PHP mysql_ * SET NAMES UTF 8和Mysql Table使用utf8_unicode_ci的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！