UTF-8从MySQL损坏到SQLite [英] UTF-8 Corrupted from MySQL to SQLite

问题描述

我要将从MySQL 5编写的PHP Web应用程序移植到SQLite3.两者的文本编码均为UTF-8(适用于所有字段，表和数据库).我无法传输带有特殊字符的地理数据库.

I'm porting a PHP Web application I wrote from MySQL 5 to SQLite 3. The text encoding for both is UTF-8 (for all fields, tables, and databases). I'm having trouble transferring a geo database with special characters.

mb_detect_encoding()将这两个都检测为返回的UTF-8数据.

mb_detect_encoding() detects both as returning UTF-8 data.

例如，

原始输出:

MySQL(正确):伊朗达拉卜
SQLite(不正确):伊朗达拉卜

MySQL (correct): Dārāb, Iran
SQLite (incorrect): DÄrÄb, Iran

JSON编码:

MySQL(正确):伊朗D \ u0101r \ u0101b
SQLite(错误):伊朗D \ u00c4 \ u0081r \ u00c4 \ u0081b

MySQL (correct): D\u0101r\u0101b, Iran
SQLite (incorrect): D\u00c4\u0081r\u00c4\u0081b, Iran

解决问题的方法:

$sqlite_output = utf8_encode($sqlite_output);
$sqlite_output = utf8_decode($sqlite_output);

我想象有一种修复SQLite数据库的方法.预先谢谢你.

I imagine there's a way of repairing the SQLite database. Thank you in advance.

推荐答案

感谢您的建议和评论.不幸的是，无论我选择哪种配置，都不需要.我最终只是简单地启动了两个PDO对象，并使用while循环一次插入了一行. (我使用了mysqldump的--no-data选项来获取结构并手动对其进行修改.)

Well, thanks for the advice and comments. Unfortunately, no matter which configurations I chose, it wouldn't take. I ended up simply initiating two PDO objects and, using a while loop, inserting one row at a time. (I used mysqldump's --no-data option to get the structure and modified that by hand.)

在我的256MB CentOS盒子上插入了大约10,000行，相当于9.4MB的数据，大约花了10分钟. (因此，如果您使用的是共享环境，请注意最长的执行时间.)SQLite数据库现在返回正确的Unicode数据.

It took about 10 minutes to insert ~10,000 rows equal to 9.4MB of data on my 256MB CentOS box. (So if you're on a shared environment, be wary of the maximum execution time.) The SQLite database now returns proper Unicode data.

自我说明:编写变通办法比找到推荐的解决方案容易.

Note to self: It's easier to code a work-around than finding the recommended solution.

这篇关于UTF-8从MySQL损坏到SQLite的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！