How to Preserve Tabular Formatting in MySQL Command Line with UTF8?

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'.

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