Keep MySQL Command Line Tabular Output Formatting with UTF8

When working with MySQL databases that contain non-English characters, the output formatting of tabular query results can break when switching to UTF8 encoding. This article provides a comprehensive guide to resolving this formatting issue.

Short Answer

Start the MySQL client with the --default-character-set=utf8 option to force UTF8 character encoding for all connections. Alternatively, set this option as a default in the /etc/mysql/my.cnf configuration file.

Troubleshooting

If the short answer does not work, consider:

• Connection Character Sets: Verify that character_set_client, character_set_connection, and character_set_results configuration variables are set to utf8.
• Database Character Set: Ensure that the character set of the database (schema) is set to utf8.
• Table and Column Character Sets: Check if individual tables or columns have specific character sets that differ from their parent.

Additional Considerations

• If weird characters persist, verify that UTF-8 values were not written using a client with a different character set encoding.
• Consider using the utf8mb4 character set for a true UTF-8 implementation.

By following these guidelines, you can maintain the integrity of your command line output formatting while working with non-English characters in MySQL databases.