show strings as UTF/Unicode

6 posts

[expired user #10877] posted 7 years 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):

959 posts

kalvaro posted 7 years 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!

6 posts

[expired user #10877] posted 7 years 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):

6 posts

[expired user #10877] posted 7 years ago

this is what i get from HeidiSQL:

1 attachment(s):

959 posts

kalvaro posted 7 years ago

Sorry then, I was just trying to help.

10152 posts

ansgar posted 7 years ago

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

6 posts

[expired user #10877] posted 7 years ago

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

6 posts

[expired user #10877] posted 7 years 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. ????

94 posts

[expired user #1502] posted 7 years ago

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

94 posts

[expired user #1502] posted 7 years ago

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

show variables like 'character%';
show variables like 'collation%'

6 posts

[expired user #10877] posted 7 years 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):

94 posts

[expired user #1502] posted 7 years 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/