Home > Database > Mysql Tutorial > body text

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

DDD
Release: 2024-10-30 17:45:30
Original
920 people have browsed it

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                      | 
+-----------------------------------+
Copy after login

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                      | 
+-----------------------------------+
Copy after login

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
Copy after login

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

[mysql]
default-character-set=utf8
Copy after login

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;
Copy after login

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
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!