特殊字符 mysql_MYSQL特殊字符问题

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

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

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)

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.

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).

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.