MySQL Workbench SQL Script File Character Set Encoding Issue – Fix

character-setMySQLmysql-workbench

Upon running a .sql file for creating a schema in MySQL Workbench 6.1, it prompts a window saying it cannot decide the right character set

enter image description here

What's the proper thing to do?

Best Answer

If it is the "world" database downloaded from here, it is in latin1 encoding. Use that for importing.

UTF-8 (utf8 or utf8mb4 in MySQL) has become the de facto standard nowadays for operating systems and global communications (except in some countries and specific areas of knowledge), and utf8 is now the default encoding in MySQL (since 5.5). See also http://utf8everywhere.org

latin1 was the 1 byte representation of characters for the western languages. MySQL supports several encodings, you can see them by executing:

SHOW CHARACTER SET;

You asked in a comment:

Given a *.sql file, how do I unambiguously find out its encoding?

If you used mysqldump to generate it, it usually presets the encoding used at the beginning like this (check the "set names" execution), so that it is imported correctly, without needing to specify it.

In general, it is impossible to know the encoding used of any arbitrary text file, but you can try to auto-detect it with tools like the standard GNU file tool on Amazon Linux file -i <filename>. There should be tons of tools like that, even more specialised.

Related Question