做mysqli-> set_charset()的永久方法? [英] a permanent way of doing mysqli->set_charset()?
问题描述
在将所有可以找到的字符集的配置文件和运行时选项设置为utf-8之后，用php建立的新mysqli连接仍然将其字符集设置为latin1，这实际上意味着我每次必须调用$mysqli->set_charset('utf8')连接.
after setting all config file and runtime options for charset that i can find to utf-8, new mysqli connections made with php still has its charset set to latin1, which effectively means that i have to call $mysqli->set_charset('utf8') each time i connect.
$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
if ($mysqli->connect_error)
err_handle("mysql connect error({$mysqli->connect_errno}).");
if (!$mysqli->set_charset("utf8"))
err_handle("db error({$mysqli->errno}).");
我想知道是否有永久的方法?
i wonder if there is a permanent way of doing this?
a "show variables like 'character_set%'" query on the mysql server before calling $mysqli->set_charset('utf8') shows:
(this part was ambiguous in previous revs)
character_set_client latin1
character_set_connection latin1
character_set_database utf8
character_set_filesystem binary
character_set_results latin1
character_set_server utf8
character_set_system utf8
客户端，连接和结果字符集只能在运行时通过$mysqli->set_charset('utf8')更改为utf8.之后，它显示:
the client, connection and results charset can only be changed to utf8 with $mysqli->set_charset('utf8') at runtime. after that it shows:
character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_filesystem binary
character_set_results utf8
character_set_server utf8
character_set_system utf8
我有
default_charset = "utf-8"
在php.ini和
[client]
default-character-set=utf8
...
[mysqld]
## This option is deprecated in favor of --character-set-server.
#default-character-set=utf8
在my.cnf中设置.
set in my.cnf.
我的表的默认字符集也是utf8.
the default charset for my tables is also utf8.
好像"[client]"选项仅影响cmd的"mysql"工具，与php无关.
seems like the "[client]" options only affect the cmd "mysql" tool and have nothing to do with php.
无论我做什么，$mysqli->character_set_name()的返回值始终为 latin1 ，直到调用$mysqli->set_charset('utf8')为止.
the return value of $mysqli->character_set_name() is always latin1 no matter what i do, until $mysqli->set_charset('utf8') is called.
我猜"latin1"是mysql的东西，因为我无法回忆起系统上默认为"latin1"的任何其他内容.
i guess "latin1" is a mysql thing, since i cant recall anything else that defaults to "latin1" on my system.
^ 更新:根据mysql手册 9.1.5 和 5.1.3 ，character_set_client应该由客户端提供.我想php在连接时不提供它，而mysql使用后备字符集 latin1 .
^update: according to mysql manual 9.1.4, 9.1.5 and 5.1.3, character_set_client should be provided by the client. i guess php doesn't provide it upon connection and mysql uses the fall-back charset latin1.
我正在使用mysql 5.1在debian wheezy上运行php 5.3.
i'm running php 5.3 on debian wheezy with mysql 5.1.
有什么建议吗?
已更新评论信息:
我忘记提及skip-character-set-client-handshake指令以及为什么我不愿意使用它.
i forgot to mention the skip-character-set-client-handshake directive and why i was reluctant to use it.
乍一看，我认为忽略握手可能会导致客户端使用 latin1 而服务器使用 utf8 的情况.服务器如何在不知道当前使用的字符集的情况下将字符串从字符集character_set_client转换为character_set_server?
upon first sight i thought ignoring the handshake might result in the situation that the client talks latin1 while the server talks utf8. how does the server convert the string from charset character_set_client to character_set_server without knowing the charset currently in use?
如果我错了，请纠正我，PLZ. 我将在今天晚些时候尝试使用此设置，以查看其是否有效.
correct me if i'm wrong, plz. i will experiment with this setting later today to see if it works.
更新为 workaroud :
确保所有内容都可以在utf-8(或任何更喜欢的字符集)下使用.然后将skip-character-set-client-handshake行添加到my.cnf.
make sure everything works under utf-8 (or any preferable charset). then add the skip-character-set-client-handshake line to my.cnf.
到目前为止，这对我有用.我尝试了一些全角utf-8字符. insert和select都成功并在浏览器中正确显示.
this works for me so far. i experimented with some double-width utf-8 characters. both insert and select succeeded and displayed properly in the browser.
尚不清楚什么跳过握手方式.并且mysql服务器现在变得无法使用除utf-8以外的任何字符集，whick使此解决方法变得非常不切实际，因为我根本无法将此设置应用于我的网站所运行的所有服务器.
what skipping the handshake means is still unclear. and the mysql server now becomes uncapable of using any charset except utf-8, whick makes this workaround quite impractical since i simply cant apply this setting to all the servers that my website runs on.
所以我没有采用这种解决方法.进一步的评论和答案非常感激.
so i'm not adopting this workaround. further comments and answers are much appreciated.
推荐答案
您已正确诊断出基本问题:虽然可以在客户端计算机的my.cnf或.my.cnf中更改默认的MySQL客户端字符集，但这些文件不是由PHP使用.
You have diagnosed the basic problem correctly: While you can change the default MySQL client charset in the client machine's my.cnf or .my.cnf, these files are not used by PHP.
如果您考虑PHP的MySQLi/MySQL扩展的工作方式，这将是有道理的-它们与mysql客户端程序无关，并且不会为配置文件抓取您的文件系统，因为它们使用libmysql直接.
If you think about how PHP's MySQLi/MySQL extensions work, this will make sense -- they have nothing to do with the mysql client program and aren't going to crawl your filesystem for config files, because they use libmysql directly.
要更改libmysql的实际默认字符集，只需重建libmysql.这可能不是您喜欢的答案(因为您使用的是预编译的MySQL二进制文件)，但这是实际的答案.默认值是在编译时设置的，然后可以在运行时覆盖.
To change libmysql's actual default charset, you'll just need to rebuild libmysql. That may not be an answer you like (since you're using precompiled MySQL binaries), but it is the actual answer. The defaults are set at compile time, and then can be overridden at runtime.
如果您不想这样做并调用set_charset()惹恼了您，我的建议是简单地扩展MySQLi类并使用该类代替mysqli.即:
If you don't want to do this and calling set_charset() annoys you, my suggestion would be to simply extend the MySQLi class and use that class in place of mysqli. i.e.:
class MyDB extends mysqli {
// (You could set defaults for the params here if you want
// i.e. $host = 'myserver', $dbname = 'myappsdb' etc.)
public function __construct($host = NULL, $username = NULL, $dbname = NULL, $port = NULL, $socket = NULL) {
parent::__construct($host, $username, $dbname, $port, $socket);
$this->set_charset("utf8");
}
}
通常，在应用程序中，无论如何，您都将具有某种数据库抽象层，因此您可以使该层使用MyDB而不是mysqli，或者使该层 be MyDB并添加或覆盖您想要的任何方法(我已经通过简单的无ORM应用程序完成了此操作).
Typically in an application you'll have some kind of database abstraction layer anyway, so you can either have this layer use MyDB instead of mysqli, or you can have this layer be MyDB and add or override any methods you want (I've done this with simple ORM-less apps).
始终保持某种数据库抽象层是一个好习惯，即使它只是从class MyDB extends mysqli {}开始，因为这样您就不必搜索/替换整个代码库即可进行小的更改.
It's a good practice to always have some kind of database abstraction layer, even if it starts as just class MyDB extends mysqli {} because then you'll never have to search/replace your entire codebase to make small changes.
RE:正如您所解释的，您的变通办法实际上是将整个数据库服务器硬编码为UTF-8，而不管客户端有什么要求.服务器不具有多个数据库，每个数据库都有自己的字符集，而是仅与UTF-8一起使用，并且如果客户端与另一个字符集连接，则可能会静默处理数据.从根本上讲这是错误的，因为您已经有效地将应用程序配置的一个方面(数据库字符集)从应用程序/客户端计算机移到了它并不真正属于的数据库服务器.
RE: your workaround, as you explain, this essentially hardcodes your entire db server to UTF-8 regardless of what clients request. Instead of having multiple databases, each with its own charset, the server only works with UTF-8 and may silently mangle data if clients connect with another charset. This is fundamentally wrong because you've effectively moved one aspect of your application's configuration (database charset) from the app/client machine to the database server where it doesn't really belong.
如果您考虑应用程序堆栈的层，
If you think about the application stack's layers,
[server] <=> [network] <=> [client libmysql] <=> [PHP binary] <=> [app]
然后，您将了解类似应用程序特定配置的正确"位置在应用程序本身中，而不是堆栈中的其他位置.您可能不喜欢必须在PHP中指定数据库的字符集，但是考虑一下，它实际上就是它的归属地，因为它也是您要指定要连接的数据库本身的地方-这是一个连接参数，不是服务器配置问题.在其他任何地方对字符集进行硬编码会使您的应用程序不可移植.
then you'll understand that the "correct" place for an app-specific configuration like this is in the app itself, not elsewhere in the stack. You may not like having to specify your database's charset in PHP, but if you think about it, that's really where it belongs, because it's also where you're specifying the database itself that you want to connect to -- it's a connection parameter, not a server configuration issue. Hardcoding the charset anywhere else makes your application non-portable.
这篇关于做mysqli-> set_charset()的永久方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
