MYSQL: Western European characters get Mangled using LOAD DATA LOCAL INFILE
When I run this on the MySQL command line, it works fine:
INSERT INTO MYTABLE VALUES(NULL,101942,'2015-05-08','sähkötupakalle');
"ä" and "ö" end in a MySQL varchar column just fine.
However, when I put the same data into a file and use
LOAD DATA LOCAL INFILE
then "ä" and "ö" will be garbled and I get the data in a MySQL varchar column that looks like this:
sähkötupakalle
Any ideas on how I can load these symbols correctly using "LOAD DATA LOCAL INFILE" ?? FYI, my table has CHARSET = utf8.
source to share
Obviously the file you are downloading is correctly encoded with utf8? But did you not provide a sentence CHARACTER SET utf8
?
Symptom "Mojibake":
When SELECTing
text, each non-English character is replaced by 2-3 characters, which you might call a marquee or garbage.
How did you get into the mess:
- The client bytes that should be
INSERTed
in the table were encoded as utf8 (good) and - The wrap for the connection was
latin1
(e.g. throughSET NAMES latin1
) and - The table column has been declared
CHARACTER SET latin1
How to fix text and table:
Take a 2 step ALTER
:
ALTER TABLE Tbl MODIFY COLUMN col VARBINARY(...) ...;
ALTER TABLE Tbl MODIFY COLUMN col VARCHAR(...) ... CHARACTER SET utf8 ...;
where the lengths are big enough and the other "..." has something else ( NOT NULL
etc.) already in the column.
This will transform the column definition, leaving only individual bits.
How to fix the code (in general):
- Change customer classified charset on
utf8
- throughSET NAMES utf8
or equivalent.
source to share