How to Display UTF-8 Characters in MySQL Command Line While Preserving Table Formatting?

MySQL Command Line Formatting with UTF-8

MySQL users often encounter formatting issues when querying data containing non-English characters, such as Swedish or Norwegian strings. By default, using the MySQL command line with the character set set to latin1 ensures correct formatting, but switching to utf8 to display special characters properly can disrupt formatting.

Solution

To retain tabular formatting while displaying UTF-8 characters, execute the following command:

mysql --default-character-set=utf8

Alternatively, modify the /etc/mysql/my.cnf file and add:

[mysql]
default-character-set=utf8

Deeper Explanation

The above solutions configure the character_set_client, character_set_connection, and character_set_results config variables to utf8. These settings dictate the character sets used by the client, database connection, and results returned.

The character_set_database variable represents the character set assigned to the current schema or database. The schema's character set is inherited from character_set_server unless explicitly specified during creation.

Additionally, individual tables and columns can have custom character sets independent of their parent table or schema. For detailed information on checking these character sets, refer to the provided resources.

Troubleshoot Incorrect Character Display

Despite setting all character sets to utf8, some characters may still appear distorted. This could happen if Unicode characters were entered through a client using the latin1 connection (e.g., mysql --default-character-set=latin1). To resolve this, connect to the database using the same character set as the input data.

Note on MySQL's UTF-8 Implementation

MySQL's utf8 encoding is an incomplete implementation of UTF-8. For a fully compliant UTF-8 implementation, consider using the utf8mb4 character set:

mysql --default-character-set=utf8mb4

For further exploration, consult the resources provided on the difference between utf8 and utf8mb4.

The above is the detailed content of How to Display UTF-8 Characters in MySQL Command Line While Preserving Table Formatting?. For more information, please follow other related articles on the PHP Chinese website!