MySQL command line formatting with UTF8
Solution 1:
Short answer
Start the client with option --default-character-set=utf8
:
mysql --default-character-set=utf8
You can set this as a default in the /etc/mysql/my.cnf
file.
[mysql]
default-character-set=utf8
The short answer did not work, read below
The command above forces the character_set_client
, character_set_connection
and character_set_results
config variables to be utf8
.
In order to check the values for all the charset related config variables you can run:
show variables like '%char%';
The character_set_database
gives you the character set of the current database (schema) that you are in. The schema and tables are created by default with the charset specified in the character_set_server
, unless it is specified explicitly in the CREATE
statement.
The character_set_server
can be changed in the my.cnf
file:
[mysqld]
character-set-server = utf8
Additionally, tables and columns can have their own charset which might be different from their parent table or schema. To specifically check the values of each table and column in a database see this answer: How do I see what character set a MySQL database / table / column is?
If you want to change the character set of existing tables and columns, see this answer: How to convert an entire MySQL database characterset and collation to UTF-8?
More info on connection character sets in the mysql docsumentation.
Everything is set to utf8, but I still see weird characters
Even if all the charsets variables, tables and columns are set to utf8
, there might be cases where you see weird characters on your screen. For example, somebody might have written Unicode characters in a utf8
column, through a client with latin1
connection (for example by running mysql --default-character-set=latin1
). In this case you need to connect to the database with the same charset as the values were written. You can also retrieve and rewrite them through the correct encoding.
NOTE: As the comments point out, the mysql utf8
encoding is not a true and full implementation of UTF-8. If a full implementation of UTF-8 is needed, one can use the utf8mb4
charset:
mysql --default-character-set=utf8mb4
More info here: What is the difference between utf8mb4 and utf8 charsets in MySQL?