Web lists-archives.com

How to get the MySQL Command-Line Tool to display Unicode properly?




Five months ago I posted the query shown below on StackOverflow.  I got one reply which was not of much help.  So I am trying again, hoping a more MySQL-centric forum might be able to solve my problem.

Roger House


How to get the MySQL Command-Line Tool to display Unicode properly?

I use a Python program to write text containing Unicode characters to a MySQL database. As an example, two of the characters are

|u'\u2640' a symbol for Venus or female u'\u2642' a symbol for Mars or male |

I use utf8mb4 for virtually all character sets involved with MySQL. Here is an excerpt from /etc/mysql/my.cnf

|[client] default-character-set=utf8mb4 [mysql] default-character-set=utf8mb4 [mysqld] default-character-set=utf8mb4 character-set-server =utf8mb4 character_set_system =utf8mb4 |

In addition, all tables are created with these parameters:

|ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

In all respects except one, the treatment of Unicode works just fine. I can write Unicode to database tables, read it, display it, etc., with no problems. The exception is mysql, the MySQL Command-Line Tool. When I execute a SELECT statement to see rows in a table containing the Venus and Mars Unicode characters, here is what I see on the screen:

|| Venus | ♀ | | Mars | ♂ | |

What I should see in the right column are the standard glyphs for Venus and Mars.

Any ideas about how to get the MySQL Command-Line Tool to display Unicode properly?

Edit:

I have done a fair amount of research into the various MySQL system variables, etc., and I now realize that the my.cnf settings shown above have some serious issues. In fact, the server, mysqld, would not launch with the settings shown. To correct things, remove these from [mysqld]:

|default-character-set=utf8mb4 character-set-system=utf8mb4 |

I'm not sure that the [client] option does anything, but it doesn't seem to hurt.

In Python u'\u2640' represents a single Unicode character, namely "♀". This compiles down to three bytes containing the hex value E29980. I am having no problems at all encoding and decoding Unicode. The correct values are being stored in a MySQL table; they are correctly read from the table, and when displayed by a Python program they show up like this:

|♀ Venus ♂ Mars |

The program output can be redirected to a file, processed by a text editor, etc., and in all cases the correct Unicode symbol is displayed.

There is only one place where the correct Unicode symbol is not displayed, and that is when I am using the MySQL Command Line Tool. When I issue a SELECT statement on the table containing the Unicode symbols I get the junk shown above. This is not a Windows specific issue. I have exactly the same problem with the MySQL Command Line Tool when I run it on Windows, Mac OS X, and Ubuntu.