MySQL warning: Incorrect string value: '\x96

Question

I'm trying to import a CVS file where I get this warning: 1366 Incorrect string value: '\x96 PART...' for column

I read somewhere that this is about the 4-bit utf8 characters. But changing the collation of the table and column into utf8mb4 didn't work either.

Answer 1

The hex 96 is presumably the latin1 encoding for an en-dash (–). But you have specified that the CSV file is utf8-encoded (or utf8mb4), this character is incomprehensible to utf8.

Plan A: Change the file. (This is probably not practical.)

Plan B: Tell MySQL that the file is latin1 (as opposed to utf8). Then MySQL will convert it correctly to the utf8-encoding E28093.

"Collation" has to do with sorting and comparing; "Character set" has to do with 'encoding'.

Add this to the LOAD DATA statement that I assume you are using:

CHARACTER SET latin1

Reference.