show strings as UTF/Unicode

safineh posted 1 year ago in General

Hi, query results that contains UTF/Unicode strings shown as ASCII. Is any way to choose correct or default encoding for display.

Note: data encoding is NOT incorrect. I can see my data correctly in app. TIA

1 attachment(s):
  • hsql
kalvaro posted 1 year ago

Even if your data encoding is correct, could you please post a screenshot of the "Create table" tab that displays the column encoding and the result of running this query?

SELECT HEX(name), HEX(owner)
FROM your_table_here

Thank you!

safineh posted 1 year ago

I don't need hex of strings, I need Unicode rather that ASCII. the result that I get in web, and the result I get from HeidiSQL are attached. in web in use <meta charset="UTF-8"> in <head>

and this is my create code:

CREATE TABLE alarm ( id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, time DATETIME NOT NULL, text VARCHAR(128) NULL DEFAULT NULL, PRIMARY KEY (id), ) COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=40 ;

2 attachment(s):
  • wsql
  • hsql
safineh posted 1 year ago

this is what i get from HeidiSQL:

1 attachment(s):
  • hsql
kalvaro posted 1 year ago

Sorry then, I was just trying to help.

ansgar posted 12 months ago

Probably a font issue. Try to use a more modern font, in Tools > Preferences > Data

safineh posted 12 months ago

Sorry then, I was just trying to help. thanks kalvaro

safineh posted 12 months ago

Probably a font issue. Try to use a more modern font, in Tools > Preferences > Data

the web output and the HeidieSQL output are using a same font: Tahoma. ????

Misha v.3 posted 12 months ago

Could you provide results of the following queries: show variables like 'character%'; show variables like 'collation%';

Misha v.3 posted 12 months ago

Err. Cannot update mu post to make the code more readable. Here you are:

show variables like 'character%';
show variables like 'collation%'
safineh posted 12 months ago

Err. Cannot update mu post to make the code more readable. Here you are:

show variables like 'character%';
show variables like 'collation%'
2 attachment(s):
  • character
  • collation
Misha v.3 posted 12 months ago

I have the same encoding settings and do not have any issues. For me it looks like your data in the table is encoded incorrectly (by your application).

DROP TABLE IF EXISTS t;

CREATE TABLE `t` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

-- SHOW CREATE TABLE t;

INSERT INTO t (name) values ('کتگوری'); -- I have no idea what does it mean, I just googled this arabic text
INSERT INTO t (name) values (X'DAA9D8AADAAFD988D8B1DB8C'); -- the same text as above but inserted as hex
INSERT INTO t (name) values (X'C398C2B4C398C2AEC399E280A1'); -- the hex value from your post

SELECT id, name, HEX(name) FROM t;

=>

"1" "کتگوری"    "DAA9D8AADAAFD988D8B1DB8C"
"2" "کتگوری"    "DAA9D8AADAAFD988D8B1DB8C"
"3" "شخه"    "C398C2B4C398C2AEC399E280A1"

Take a note on the codes and then open for example this: http://www.fileformat.info/info/charset/UTF-8/list.htm

Now Ctrl+F for DAA9 and you'll see that this is the proper UTF8 code for the arabic char used. Then Ctrl+F C398 and you'll see that mysql correctly displays a char that is "LATIN CAPITAL LETTER O WITH STROKE"

Are you sure that your text is UTF8 text? For example, C398 could also be a UTF-16BE code for another Unicode (but NOT UTF-8) character: https://unicode-table.com/en/C398/

Please login to leave a reply, or register at first.