PHP/MySQL 有编码问题 [英] PHP/MySQL with encoding problems

问题描述

我在使用 PHP 时遇到了编码问题.

I am having trouble with PHP regarding encoding.

我有一个 JavaScript/jQuery HTML5 页面使用 $.post 与我的 PHP 脚本交互.但是，PHP 面临着一个奇怪的问题，可能与编码有关.

I have a JavaScript/jQuery HTML5 page interact with my PHP script using $.post. However, PHP is facing a weird problem, probably related to encoding.

当我写

htmlentities("í")

我希望 PHP 输出 í.但是，它会输出 í一开始，我以为我在编码上犯了一些错误，但是

I expect PHP to output í. However, instead it outputs í At the beginning, I thought that I was making some mistake with the encodings, however

htmlentities("í")=="í"?"Good":"Fail";

正在输出失败"，其中

htmlentities("í")=="í"?"Good":"Fail";

但是 htmlentities($search, null, "utf-8") 按预期工作.

我想让 PHP 与 MySQL 服务器通信，但它也有编码问题，即使我使用 utf8_encode.我该怎么办?

I want to have PHP communicate with a MySQL server, but it has encoding problems too, even if I use utf8_encode. What should I do?

在 SQL 命令上，写

On the SQL command, writing

SELECT id,uid,type,value FROM users,profile
WHERE uid=id AND type='name' AND value='XXX';

其中 XXX 不包含 í 字符，按预期工作，但如果有任何 'í' 字符，则不会.

where XXX contains no í chars, works as expected, but it does not if there is any 'í' char.

SET NAMES 'utf8';
SET CHARACTER SET 'utf8';
SELECT id,uid,type,value FROM users,profile
WHERE uid=id AND type='name' AND value='XXX';

不仅对于í字符失败，而且对于没有任何特殊"字符的字符串也会失败.从 SET NAMES 和 SET CHARACTER SET 中删除 ' 字符似乎没有任何改变.

Not only fails for í chars, but it ALSO fails for strings without any 'special' characters. Removing the ' chars from SET NAMES and SET CHARACTER SET doesn't seem to change anything.

我正在使用 PDO 连接到 MySQL 数据库.

I am connecting to the MySQL database using PDO.

编辑 2:我使用的是 XAMPP for Linux 的 MySQL 5.1.30 版.

EDIT 2: I am using MySQL version 5.1.30 of XAMPP for Linux.

编辑 3:从 PhpMyAdmin 输出运行 SHOW VARIABLES LIKE '%character%'

EDIT 3: Running SHOW VARIABLES LIKE '%character%' from PhpMyAdmin outputs

character_set_client    utf8
character_set_connection    utf8
character_set_database  latin1
character_set_filesystem    binary
character_set_results   utf8
character_set_server    latin1
character_set_system    utf8
character_sets_dir  /opt/lampp/share/mysql/charsets/

从我的 PHP 脚本(使用 print_r)输出运行相同的查询:

Running the same query from my PHP script(with print_r) outputs:

Array
(
    [0] => Array
        (
            [Variable_name] => character_set_client
            [0] => character_set_client
            [Value] => latin1
            [1] => latin1
        )

    [1] => Array
        (
            [Variable_name] => character_set_connection
            [0] => character_set_connection
            [Value] => latin1
            [1] => latin1
        )

    [2] => Array
        (
            [Variable_name] => character_set_database
            [0] => character_set_database
            [Value] => latin1
            [1] => latin1
        )

    [3] => Array
        (
            [Variable_name] => character_set_filesystem
            [0] => character_set_filesystem
            [Value] => binary
            [1] => binary
        )

    [4] => Array
        (
            [Variable_name] => character_set_results
            [0] => character_set_results
            [Value] => latin1
            [1] => latin1
        )

    [5] => Array
        (
            [Variable_name] => character_set_server
            [0] => character_set_server
            [Value] => latin1
            [1] => latin1
        )

    [6] => Array
        (
            [Variable_name] => character_set_system
            [0] => character_set_system
            [Value] => utf8
            [1] => utf8
        )

    [7] => Array
        (
            [Variable_name] => character_sets_dir
            [0] => character_sets_dir
            [Value] => /opt/lampp/share/mysql/charsets/
            [1] => /opt/lampp/share/mysql/charsets/
        )

)

运行

SET NAMES 'utf8';
SET CHARACTER SET 'utf8';
SHOW VARIABLES LIKE '%character%'

输出一个空数组.

推荐答案

指定htmlentities<的编码很重要/a> 匹配输入，就像您在最后一个示例中所做的那样，但在前三个示例中省略了.

It's very important to specify the encoding of htmlentities to match that of the input, as you did in your final example but omitted in the first three.

htmlentities($text,ENT_COMPAT,'utf-8');

关于与 MySQL 的通信，您需要确保连接排序规则和字符集与您正在传输的数据匹配.您可以在配置文件中进行设置，也可以在运行时使用以下查询进行设置:

Regarding communications with MySQL, you need to make sure the connection collation and character set matches the data you are transmitting. You can either set this in the configuration file, or at runtime using the following queries:

SET NAMES utf8;
SET CHARACTER SET utf8;

确保表、数据库和服务器字符集也匹配.有一种设置不能在运行时更改，那就是服务器的字符集.需要在配置文件中修改:

Make sure the table, database and server character sets match as well. There is one setting you can't change at run-time, and that's the server's character set. You need to modify it in the configuration file:

[mysqld]
character-set-server = utf8
default-character-set = utf8 
skip-character-set-client-handshake

阅读更多关于 MySQL 中的字符集和排序规则在手册中.

Read more on characters sets and collations in MySQL in the manual.

这篇关于PHP/MySQL 有编码问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！