mysql - 加载数据到文件默认编码

Question

在尝试在 MYSQL (InnoDB) 中使用 LOAD DATA INFILE 命令时，我在使编码正常工作时遇到了困难。

"something","something else",\N,"ANOTHER","2012-05-05T19:54:03","2012-12-08T16:14:53","SOMETHING","Something","Hello","HIHI","HEY","999.0","0.01","0.25","06/2012",\N,"2012-06-28","2012-06-28","2012-06-28","2009-03-02","2012-06-28",\N,"LOLOL","","LOLNON",\N,

成为

獯浥瑨楮朢Ⱒ獯浥瑨楮朠敬獥       䅎佔䡅刢Ⱒ㈰ㄲⴰ㔭〵吱㤺㔴㨰㌢Ⱒ㈰ㄲⴱ㈭〸吱㘺ㄴ㨵㌢Ⱒ协䵅呈䥎䜢Ⱒ卯浥瑨楮朢Ⱒ䡥汬漢Ⱒ䡉䡉   䡅夢Ⱒ㤹㤮〢Ⱒ〮〱   \N  \N  ㈰ㄲⴰ㘭㈸   ㈰〹ⴰ㌭〲   ㈰ㄲⴰ㘭㈸       䱏䱏䰢Ⱒ    0   0   \N  \N  \N  \N  \N  \N  \N  \N  \N  \N  \N  \N  \N  \N

其他人也有类似的问题，和我一样，通过将字符集设置为“utf8”来解决。 (如 this one 和 this one )在所有问题或答案中都没有明确说明为什么 utf8 不能自动工作。

这看起来很奇怪，因为人们经常提到他们使用 utf8 作为默认编码，而我在 Notepad++ 中使用“utf8 - 默认排序规则”的表默认排序规则对数据进行 UTF-8 格式编码。

我进一步查看，发现我的架构默认字符集是 utf16，默认排序规则为 utf16_general_ci。我相信 MYSQL 使用 LOAD DATA INFILE 命令的架构默认字符集和排序规则。

MYSQL 是否使用 LOAD DATA INFILE 的默认模式字符集，如果是，记录在哪里？如果不是，默认字符集从哪里来？

Answer 1

基本上是的。

The server uses the character set indicated by the character_set_database system variable to interpret the information in the file. SET NAMES and the setting of character_set_client do not affect interpretation of input. If the contents of the input file use a character set that differs from the default, it is usually preferable to specify the character set of the file by using the CHARACTER SET clause. A character set of binary specifies “no conversion.”

这位于 documentation for LOAD DATA INFILE 顶部附近

如果您动态设置该变量，那么您可以声称您没有使用架构默认值，但这会扩展这一点。