MYSQL特殊字符问题 [英] MYSQL special chars issue

问题描述

我长期以来一直在使用这个问题，我曾多次搜寻互联网的解决方案，尝试过很多次但找不到足够的解决方案。

我真的不知道该怎么做，如果你能帮助我，我会非常感谢。

（对不起，我的英语不好）。

$ b

问题：从我的计算机导入存档时，信息显示在我的数据库中，但有些字符为（'ã'，'ç'，'á '，etc ..）显示为？。

信息

1. 我正在使用MYSql，我的版本和变量状态是：

    
    MySQL版本：5.5.10 
   主机：本地主机
   的用户：根
   端口：3306 
   服务器的默认字符集： UTF8 
    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 
    collat​​ion_connection是：utf8_general_ci 
    collat​​ion_database查看：utf8_general_ci 
    collat​​ion_server的：utf8_general_ci 
    completion_type：NO_CHAIN 
    concurrent_insert：AUTO 
    




2. 正在使用的查询是：

     LOAD DATA LOCAL INFILE'xxxxx / file.txt的
   插入TERMINATED BY TABLE xxxxTable 
   场';'
   '结尾BY 
    LINES'IGNORE 1号线
   （status_ordenar，NUMERO，NEWSTATUS，惹得， data_emissao，INICIO，termino，tempo_indisp 
   ，cli_afet，qtd_cli_afet，cod_encerr，uf_ofensor，localidades，clientes_afetados 
   ，especificacao，equipamentos，area_ofens，descricao_encerr，criticidade，cod_erro 
   ，observacao，id_falha_perc，id_falha_conf， NBA，solucao，falhapercebida，falhaconfirmada 
   ，resp_i，resp_f，resp_ue，pre_handover，falha_identificada，report_netcool，tipo_falha 
   ，num_notificacao，equip_afetados，descricao）
     




3. 关于正在导入的文件：
   我已用开放式办公室打开文件3个字符集：

   
   
   

   UTF8 - 给我用'ç'，'ã'，等等的奇怪的字符...
   
   ISO-8859-1 - OK。
   
   WIN-1252 - OK。
   
   ASCII / US - OK。




4. 已经测试：测试从来就在我的数据库字符集的一些：LATIN1，UTF-8，ASCII，但他们给了我同样的结果（？而不是á，ç等）。




5. 额外：我使用Java与Java JDBC来生成和发送查询。

解决方案

file.txt的保存在ISO 8859或Windows 1252（这两个非常相似），与被解释为UTF -8由MySQL。这些是不兼容的。

我如何分辨？

• ：解释为ISO-8859-1或Windows-1252时，文件会正确显示。


• 请参阅第1点：character_set_database：utf8

解决方案：将文件转换为UTF-8，或告诉MySQL将其解释为ISO-8859-1或Windows-1252。

背景：您提供的字符（ã等）在Windows-1252中是单字节值，这些字节是UTF-8中的非法值，因此产生了'？'（Unicode字符替换字符）。

来自的片段MySQL docs ：

LOAD DATA INFILE语法

由character_set_database指示的集系统变量用于解释文件中的信息。

I´ve been having this problem for a long time, I´ve searched the internet many times for the solution, tried lots of them but not found an adequate solution.

I really don´t know what to do so if you could please help me I´d be very thankful.
(Sorry for my poor english).

Question: How can I solve the charset incompatibility between the input archive and a MYSql table?

Problem: When importing the archive from on my computer the information appears in my database, but some chars as ('ã', 'ç', 'á', etc..) are shown as ?.

Aditional information

1. I'm using MYSql, my version and variable status are:

   MySQL VERSION : 5.5.10  
   HOST : localhost  
   USER : root  
   PORT : 3306  
   SERVER DEFAULT CHARSET : utf8  
   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  
   collation_connection  :  utf8_general_ci  
   collation_database  :  utf8_general_ci  
   collation_server  :  utf8_general_ci  
   completion_type  :  NO_CHAIN  
   concurrent_insert  :  AUTO  
   

2. The query that´s being used is:

   LOAD DATA LOCAL INFILE 'xxxxx/file.txt' 
   INTO TABLE xxxxTable 
   FIELDS TERMINATED BY ';' 
   LINES TERMINATED BY ' ' 
   IGNORE 1 LINES
   ( status_ordenar,numero,newstatus,rede,data_emissao,inicio,termino,tempo_indisp
   , cli_afet,qtd_cli_afet,cod_encerr,uf_ofensor,localidades,clientes_afetados 
   , especificacao,equipamentos,area_ofens,descricao_encerr,criticidade,cod_erro
   , observacao,id_falha_perc,id_falha_conf,nba,solucao,falhapercebida,falhaconfirmada
   , resp_i,resp_f,resp_ue,pre_handover,falha_identificada,report_netcool,tipo_falha
   , num_notificacao,equip_afetados,descricao) 
   

3. About the file being imported: I´ve opened the file with open office whith 3 charsets:

   UTF8 - Gave me strange chars in place of the 'ç', 'ã', etc...
   ISO-8859-1 - OK.
   WIN-1252 - OK.
   ASCII/US - OK.

4. Already tested: I´ve tested some charsets in my database: latin1, utf-8, ascii, but all of them gave me the same result (? instead of 'á', 'ç' etc).

5. Extra: I'm using Java with Java JDBC to generate and send the query.

解决方案

file.txt is saved in ISO-8859-1 or Windows-1252 (these two are very similar), and being interpreted as UTF-8 by MySQL. These are incompatible.

How can I tell?

• See point 3.: the file displays correctly when interpreted as ISO-8859-1 or Windows-1252.
• See point 1.: character_set_database : utf8

Solution: either convert the file to UTF-8, or tell MySQL to interpret it as ISO-8859-1 or Windows-1252.

Background: the characters you provide (ã etc.) are single-byte values in windows-1252, and these bytes are illegal values in UTF-8, thus yielding the '?'s (unicode replacement characters).

Snippet from MySQL docs:

LOAD DATA INFILE Syntax

The character set indicated by the character_set_database system variable is used to interpret the information in the file.

这篇关于MYSQL特殊字符问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！