将 MySQL 中的 UTF8 和 latin1_bin 字段与 PHP 混合使用
在数据库上，我必须以 UTF8 格式存储名称等，并以 latin1_bin 格式存储哈希值。我调用了SET NAMES utf8，但我注意到当我尝试读取latin1字段时它损坏了它们（我能够很好地编写它们）。这很奇怪，因为如果我理解正确的话，查询只是关于向服务器发送数据，而不是接收数据。
phpMyAdmin 也显示损坏的数据。
关于我可能做错了什么的任何线索吗？
（使用 MAMP 1.9.6）
编辑：这个答案指定这是也是用于将数据发送回客户端的字符集。我很困惑：如果无论如何都会忽略列的字符集，那么指定列的字符集有什么意义？
编辑： 列定义摘录：
`tok` char(64) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`sal` char(16) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_roman_ci ;
查询摘录：
SELECT tok,sal FROM user WHERE id=4 LIMIT 1
。
INSERT INTO user (tok, sal) VALUES (x'1387ea0c22277d3000bd23241c357e3a9ba45a2e28f50581d63a73bf785a7458a95cca4de27d0a86588f5bdfa94415d6a255c2c0379ebc2f00dacba03ae6b866', x'8fca28a592c29f245ff0a3ba5f97420c')
On a database I'll have to store names and such in UTF8, and hashes in latin1_bin. I called SET NAMES utf8, but I noticed that it corrupted the latin1 fields when I tried to read them (I was able to write them just fine). Which is odd, since if I understood correctly that query is only about sending data to the server, not receiving it.
phpMyAdmin displays broken data too.
Any clue about what I might be doing wrong?
(using MAMP 1.9.6)
edit: this answer specifies this is also the charset used to send data back to the client. I'm getting confused: what's the point of specifying the charset of a column if that will be ignored anyway?
edit:
excerpt from the column definition:
`tok` char(64) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`sal` char(16) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_roman_ci ;
excerpt from queries:
SELECT tok,sal FROM user WHERE id=4 LIMIT 1
.
INSERT INTO user (tok, sal) VALUES (x'1387ea0c22277d3000bd23241c357e3a9ba45a2e28f50581d63a73bf785a7458a95cca4de27d0a86588f5bdfa94415d6a255c2c0379ebc2f00dacba03ae6b866', x'8fca28a592c29f245ff0a3ba5f97420c')
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您应该更改表定义以使用 MySql 的
BINARY类型，非常适合此类数据：列定义将变为：
You should change your table definition to use MySql's
BINARYtype, which is the perfect fit for this kind of data:The column definitions would become:
set names ...仅设置用于发送和接收数据的连接字符集（仅限客户端！）。与数据库中字段的编码无关！它仅与您用作数据库客户端的编码有关，例如在 PHP 中。数据库引擎始终会将表中的编码转换为set name ...查询中指定的编码。因此，在表定义中，您只需根据需要为每个字段指定编码，并且不会更改使用
set names ...命令的方式 - 它只是与以前相同。set names ...only sets the character set of the connection (of the client side only!) for both sending and receiving the data. It is not related to the encoding of the fields in the database! It is only related to the encoding which you use as a client to the database, e.g. in PHP. The database engine will always convert the encoding between the encoding in tables to the encoding specified inset names ...query.So in table definition you just specify encoding for each field as you need, and you don't change anything in the way how you used the
set names ...command - it just remains the same as before.保持
SET NAMES开启，并且INSERT查询保持不变，我将SELECT查询更改为：即，我将哈希字段转换为二进制。
虽然这有效，但我会保持开放状态，以防有人提供（也许更正确？）替代方案。
Leaving
SET NAMESon, and theINSERTquery intact, I changed theSELECTquery to:i.e. I casted the hash fields to binary.
While this worked, I'll leave this open in case someone provides a (perhaps more correct?) alternative.