MySQL解码Unicode到UTF-8函数

I want to decode Unicode strings to UTF-8 when inserting in a table. Here's what I have:

在插入表时，我想将Unicode字符串解码为UTF-8。这就是我有:

('\u0645\u064e\u062b\u0652\u0646\u064e\u0649 \u00a0\u062c \u0645\u064e\u062b\u064e\u0627\u0646\u064d')

So I want these values to be converted to UTF-8, for example:

所以我想把这些值转换成UTF-8，例如:

INSERT INTO `nouns`(`NOUNID`, `WORDID`, `SINGULAR`, `PLURAL`) VALUES (781, 3188, '\u0646\u064e\u062c\u0652\u0645', ('\u0646\u064e\u062c\u0652\u0645'))

I am migrating my h2 database to MySQL, so I got this when scripting my h2 db:

我正在将h2数据库迁移到MySQL，所以我在编写h2 db脚本时得到了这个:

INSERT INTO PUBLIC.NOUNS(NOUNID, WORDID, SINGULAR, PLURAL) VALUES
  (1, 5, STRINGDECODE('\u0623\u0628\u0651 '), STRINGDECODE
  ('\u0623\u0624\u064f\u0628\u0651')),
  (2, 9, STRINGDECODE('\u0623\u064e\u0628\u0627\u0628'), ''),

basicly thees \u0623\u0632\u0651 are arabic charchters in unicode representation and I want to convert them in real arabic characters, to be stored like that in database. I was trying convert function, but because I am new to mysql I couldn't achive this:

基本上，es \u0623\u0632\u0651是unicode表示法中的阿拉伯文字符，我想把它们转换成真正的阿拉伯文字符，像这样存储在数据库中。我尝试转换函数，但是因为我是mysql的新手，我无法做到这一点:

SELECT CONVERT(_ucs2'\u0623' USING utf8);

1 个解决方案

#1

There is no built-in function to decode unicode escapes in MySQL, but you can create one; see below:

在MySQL中没有内置函数来解码unicode的转义，但是您可以创建一个;见下文:

Note that the backslash is an escape character in MySQL, so you'll need to double them when you write SQL: '\\u0623\\u064e\\u0628\\u0627\\u0628'

注意，反斜杠在MySQL中是转义字符，因此在编写SQL时需要将其加倍:'\ u0623\ u064e\ u0628\ u0628\

DELIMITER //

CREATE FUNCTION STRINGDECODE(str TEXT CHARSET utf8)
RETURNS text CHARSET utf8 DETERMINISTIC
BEGIN
declare pos int;
declare escape char(6) charset utf8;
declare unescape char(3) charset utf8;
set pos = locate('\\u', str);
while pos > 0 do
    set escape = substring(str, pos, 6);
    set unescape = char(conv(substring(escape,3),16,10) using ucs2);
    set str = replace(str, escape, unescape);
    set pos = locate('\\u', str, pos+1);
end while;
return str;
END//

DELIMITER ;

#1