How to Preserve Table Formatting in MySQL with UTF8 Character Encoding?

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

The above is the detailed content of How to Preserve Table Formatting in MySQL with UTF8 Character Encoding?. For more information, please follow other related articles on the PHP Chinese website!