MySQL insert and unicode characters

Question

Here is sample code:

CREATE DATABASE test_unicode DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

USE test_unicode;

CREATE TABLE simple_table (text varchar(100) NOT NULL);

INSERT INTO simple_table VALUES ('l\u2019agave');

SELECT * FROM simple_table;

And here is output:

+-------------+
| text        |
+-------------+
| lu2019agave |
+-------------+
1 row in set (0.00 sec)

Mysql stored my string without - " \ "

I need to get "l\u2019agave" with - " \ "

How I can do it?

I tried a lot of encoding settings to mysql, but it not working for me...

Thanks for your advices!

score 4 · Accepted Answer · answered Jul 08 '13 at 11:06

4

Try

INSERT INTO simple_table VALUES ('l\\u2019agave');

answered Jul 08 '13 at 11:06

bansi

55,591
6
41
52

Thanks. It is working... but I got "l\u2019agave" from postgres (using pg_dump) and I will need to replace all strings in my dump... maybe there is exist other ways? – Tom Jul 08 '13 at 11:09
2

@AlexeyLisikhin you can try to use [NO_BACKSLASH_ESCAPES SQL mode](http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_no_backslash_escapes) `SET SESSION sql_mode=NO_BACKSLASH_ESCAPES` – bansi Jul 08 '13 at 11:26

score 1 · Answer 2 · answered Jul 08 '13 at 11:47

Try this :

INSERT INTO `test_unicode`.`simple_table` (`text`) VALUES ('l\\u2019agave');

This is because MySQL uses C escape syntax in strings (for example, “\n” to represent a newline character), you must double any “\” that you use in LIKE strings. For example, to search for “\n”, specify it as “\n”. To search for “\”, specify it as “\\”; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.

score 0 · Answer 3 · edited May 23 '17 at 12:14

0

for java: org.apache.commons.lang3.StringEscapeUtils.unescapeJava(str); from Convert escaped Unicode character back to actual character

edited May 23 '17 at 12:14

Community

1
1

answered Sep 09 '16 at 05:06

qxo

1,584
15
11

MySQL insert and unicode characters

3 Answers3