Enhancing MySQL Command Line Formatting with UTF8 Character Encoding

While working with Swedish and Norwegian strings stored in a database table, you may encounter issues with table formatting when querying data using different character sets.

Problem Statement

By default, using "set names latin1;" produces distorted output:

 ----------------------------------- 
| name                              |
 ----------------------------------- 
| Kid Interi#####                   | 
| Bwg Homes                         | 
| If Skadef####kring                | 
| Jangaard Export                   | 
| Nordisk Film                      | 
 ----------------------------------- 

Switching to "set names utf8;" displays the characters correctly, but disrupts the tabular formatting:

 ----------------------------------- 
| name                              |
 ----------------------------------- 
| Kid Interiør                     | 
| Bwg Homes                         | 
| If Skadeförsäkring              | 
| Jangaard Export                   | 
| Nordisk Film                      | 
 ----------------------------------- 

Solution

To preserve tabular formatting while using UTF8 character encoding, you can start the MySQL client with the "--default-character-set=utf8" option:

mysql --default-character-set=utf8

You can also configure this as a default setting in the "/etc/mysql/my.cnf" file:

[mysql]
default-character-set=utf8

This sets the character_set_client, character_set_connection, and character_set_results configuration variables to utf8.

If the formatting issue persists, ensure that the database, tables, and columns are also set to utf8 using the following commands:

SHOW VARIABLES LIKE '%CHAR%';
SET character-set-server = utf8;

Additionally, check for any Unicode characters that may have been written with a latin1 connection. To resolve this, connect to the database with the same charset as the values were written, or retrieve and rewrite them using the correct encoding.

Note: MySQL's utf8 encoding is not a complete Unicode implementation. Consider using the utf8mb4 charset for a true UTF-8 implementation:

mysql --default-character-set=utf8mb4