Mysql – Importing UTF8 data into MySQL from a file causes truncation

encodingMySQLutf-8

I have a file called person.sql with 1 line:

INSERT INTO Person (sFullname) VALUES ('Thomas Ågren');

If I run the following command from the shell:

mysql --default-character-set=utf8 -uusername -ppassword mydatabase < person.sql

I get truncated results:

mysql> select sfullname from person;
+-----------+
| sfullname |
+-----------+
| Thomas    |
+-----------+

If I run the same exact insert command directly from the MySQL command line:

mysql> INSERT INTO Person (sFullname) VALUES ('Thomas Ågren');

I get the correct results:

mysql> select sfullname from person;
+--------------+
| sfullname    |
+--------------+
| Thomas Ågren |
+--------------+

Any ideas? The default charset on the database and Person table are correctly set to utf8. Here's the schema definition:

mysql> show create table person \G
*************************** 1. row ***************************
       Table: person
Create Table: CREATE TABLE `person` (
  `sFullname` varchar(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Best Answer

It turns out that the text file person.sql was not UTF-8 encoded (although it appeared to be). It was actually latin1 (ISO 8859-1).

To switch encodings, I ran:

iconv -f latin1 -t utf-8 person.sql person-utf8.sql

After that, I was able to successfully import without truncation.