Viewing Unicode characters
Does anyone know how to set the character encoding for viewing the database in HeidiSQL?
The chars work fine on the website and are stored in a utf8mb4 database via the admin, which works great.
But the moments I do use the editor, I would like to see what's in there, instead of a square or, in this case a 4 byte character, a question mark.
The problem is that HeidiSQL probably can't handle 4 byte characters? 2 byte chars, as you see in the example image are displayed well.
Oh well, not really a problem though, since I use a CMS I build myself which displays them perfectly.
Was just wondering if there is just some way to display them correctly in HeidiSQL.
UTF8mb4 contains 4 byte characters, which UTF8 doesn't support. That's why the other UTF8 characters show as they should, but these ones don't.
I use mysql_query('SET NAMES utf8mb4') to get them properly from the database. And as you said, if HeidiSQL calls it with mysql_set_character_set(FHandle, 'utf8'), they will not appear.
Is that something I can change or is that deeply embedded in HeidiSQL software code?
But, try this:
CREATE TABLE IF NOT EXISTS `testing_table` (
`index_id` int(11) NOT NULL DEFAULT '0',
`charlist_id` int(11) NOT NULL,
`cOriginal` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`cReplace` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;
And insert some of the characters in the cReplace column from the following URL: http://www.fileformat.info/info/unicode/block/mathematical_alphanumeric_symbols/list.htm
And in this case pick some of the chars from MATHEMATICAL FRAKTUR or MATHEMATICAL SCRIPT.
insert into utable (cReplace) values (_ucs2'\xD835\xDC00')
But I get chinese chars in the data grid, no matter if I ran "SET NAMES utf8" or "SET NAMES utf8mb4" before.
I'm using it via my CMS with a normal query. That's how I got them into the database. The output is fine too.
It's just only the viewing from within HeidiSQL, the character itself is inserted properly in the database.
See my variables and values:
I used MySQL Front before HeidiSQL, and wasn't satisfied with the utf8 support, it had almost none. Copying tables meant loss of all utf8 characters, even though everything was set to utf8_unicode_ci. That was why I switched to HeidiSQL (and because MySQL Front wasn't updated anymore).
Using HeidiSQL now for 6 months or so and very satisfied, which I expressed with a little donation, since you're doing a great job!
I run about 30 MySQL driven websites, so I'm using HeidiSQL a lot. It's stable, the design is nice, the functions work superb and the utf8 support is great. :D
Switch character set from utf8 to the fixed one called utf8mb4 in MySQL. Attempt to still use utf8 when that newer charset does not exist in the client library. At the same time, update libmysql to v2.0 from https://downloads.mariadb.org/client-native/2.0.0/ . See http://www.heidisql.com/forum.php?t=16425
/* Attempt to create plink.exe process, waiting 4s for response ... */ /* Connected. Thread-ID: 310502 */ /* Can't initialize character set utf8mb4 (path: C:\Program Files (x86)\MySql\share\charsets\) */ /* Characterset: utf8 */
This doesn't look right because there's no folder "C:\Program Files (x86)" on my system, is it hard coded?
You should use the nightly build installer in order to get a newer libmysql which has that utf8mb4 charset. When you have the libmysql from the last official 8.3 installer you will always get that error message. Well, HeidiSQL still uses utf8 in that case, so it's not a real problem.
Might now be one of my own settings.
They display correctly in the webbrowser, so my system supports it. The database standard encoding is utf8 though, the table too. Just the column has utf8mb4, since I just started experimenting with it. But I'm not eager to change the complete database/table encoding (yet) on this domain.
It might work on a clean database, with everything set to mb4.
I'll update you on that tomorrow!
/* Delimiter changed to ; */
/* Connecting to *******.com via MySQL (TCP/IP), username *******, using password: Yes ... */
/* Connected. Thread-ID: 8794864 */
/* Characterset: utf8mb4 */
I'm using MySQL 5.5.37, and it supports the charset since everything is working fine on the frontend.
I did not had the time to test on a fresh domain though and will try ASAP.
By the way, since I updated to r4822 connecting to the server is taking about 10-15 seconds instead of 1 ish second.
ALTER DATABASE `in4_db` COLLATE 'utf8mb4_unicode_ci'; CREATE TABLE `test` ( `test` VARCHAR(50) NULL COLLATE 'utf8mb4_unicode_ci' ) COLLATE='utf8mb4_unicode_ci' ENGINE=MyISAM; INSERT INTO `in4_db`.`test` (`test`) VALUES ('
Hopefully that'll get fixed soon.
If I can do or test anything or supply you with data, let me know.
/* 2014-10-03 14:06:47 [localhost] */ /* Connecting to 127.0.0.1 via MariaDB (TCP/IP), username root, using password: No ... */ /* 2014-10-03 14:06:59 [localhost] */ /* Connected. Thread-ID: 103 */
Will try a newer libmysql from the current MariaDB GA release.
Update libmysql again to those from the MariaDB 10.0.14 package. Probably fixes a connection latency, described in http://www.heidisql.com/forum.php?t=16425
And please try again with my exported characters:
CREATE TABLE `utable` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `val` VARCHAR(20) NOT NULL COLLATE 'utf8mb4_unicode_ci', PRIMARY KEY (`id`) ) COLLATE='utf8mb4_unicode_ci'; INSERT INTO utable (id, val) VALUES (1, UNHEX('F09D9484')); INSERT INTO utable (id, val) VALUES (2, UNHEX('F09D9485')); INSERT INTO utable (id, val) VALUES (3, UNHEX('F09D9487')); INSERT INTO utable (id, val) VALUES (4, UNHEX('F09D9488')); INSERT INTO utable (id, val) VALUES (5, UNHEX('F09D9489')); INSERT INTO utable (id, val) VALUES (6, UNHEX('F09D948A')); INSERT INTO utable (id, val) VALUES (7, UNHEX('F09D948D')); INSERT INTO utable (id, val) VALUES (8, UNHEX('F09D948E')); INSERT INTO utable (id, val) VALUES (9, UNHEX('F09D948F')); INSERT INTO utable (id, val) VALUES (10, UNHEX('F09D9490'));
And try another font in Preferences > Data appearance, like kalvaro said. Try "Calibri" or the one kalvaro meant.
These hex values should work here, they should work for you too.
Did not do anything for the squares though.
Like I said before, changing the font is not gooing to be helping. As long as you use true type fonts, they just work. I'm using Tahoma, which is a truetype font.
But, to prove that, here's a screenshot with Calibri:
/* Delimiter changed to ; */ /* Connecting to 10.55.99.29 via MySQL (TCP/IP), username root, using password: Yes ... */ /* Connected. Thread-ID: 102 */ /* Can't initialize character set utf8mb4 (path: C:\Program Files (x86)\MySql\share\charsets\) */ /* Characterset: latin1 */
Now I have rolled back to use the stable version 220.127.116.1194.
/* Delimiter changed to ; */
/* Connecting to 10.55.99.29 via MySQL (TCP/IP), username root, using password: Yes ... */
/* Connected. Thread-ID: 165 */
/* Connecting to 127.0.0.1 via MariaDB (TCP/IP), username root, using password: No ... */ /* Connected. Thread-ID: 284 */ /* Characterset: utf8mb4 */ SHOW STATUS;
What OS is it on your side?
MySQL 4.0.23? That's really, *really* old. And utf8mb4 is supported from MySQL 5.5.3 upwards: http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html
Would you mind adding an option to let the user choose the default character_set to fallback?
Please login to leave a reply, or register at first.