MySQL Command Line Formatting with UTF8: Preserving Tabular Output

When querying a database containing non-Latin1 characters, using the 'set names utf8' command can disrupt tabular formatting in the command line. This article explores solutions to maintain this formatting while displaying UTF8-encoded data.

Short Answer: Set Default Character Set to UTF8

Run the client with the '--default-character-set=utf8' option:

mysql --default-character-set=utf8

Or add it as a default in '/etc/mysql/my.cnf':

[mysql]
default-character-set=utf8

Extended Details

By setting the default character set to UTF8, you force the following configuration variables to be UTF8:

• character_set_client
• character_set_connection
• character_set_results

Further Considerations

1. Check character set related variables:

show variables like '%char%';

• character_set_database indicates the current schema's character set.
• character_set_server can be modified in '/etc/mysql/my.cnf'.

2. Tables and columns can have their own character sets, which may differ from their parent table or schema. For details, refer to the answer provided here: How do I see what character set a MySQL database / table / column is?
3. 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?
4. If everything is set to UTF8 but strange characters persist, the values may have been written with a different character set. Connect to the database using the correct character set or rewrite the values using the correct encoding.

Note: Full UTF-8 Implementation

MySQL's UTF8 encoding is not a fully compliant UTF-8 implementation. For full UTF-8 support, use the 'utf8mb4' charset. Run the client with '--default-character-set=utf8mb4'.