Non-standard encoding of Emojis and other 4-byte UTF-8 characters on Android pre-6.0 (default NDK implementation)

Question

It is possible to store and retrieve an emoji character value such as \u1F603 [SMILING FACE (MOUTH OPEN)] in case of the default Android implementation ~~and Windows~~. But SELECT HEX(?) with an emoji text value returns different results from (WebKit) Web SQL, iOS, and an Android database opened with the androidDatabaseImplementation: 2 setting. This is also an issue when querying the HEX value of a stored column with an emoji text value. Note that the iOS version and an Android database opened with the androidDatabaseImplementation: 2 setting are consistent with the sqlite3 CLI tool, at least on my Mac OS system.

I suspect this indicates that the default Android implementation (using Android-sqlite-connector) ~~and Windows versions~~ store emoji characters differently. This would impact cases where the same sqlite(3) database is created on another system or shared between multiple platforms. In addition I wonder if there may be issues with other 4-octet UTF-8 characters?

该提问来源于开源项目：storesafe/cordova-sqlite-storage

Answer 1

This does not seem to be a real issue on Windows. As discussed in #652 the Windows version uses UTF-16le database encoding by default. Upon closer examination the HEX value result does look correct for the UTF-16le encoding. If I would first do PRAGMA encoding="UTF-8" then SELECT HEX returns the same result as on iOS, Android/iOS WebKit Web SQL, and an Android database opened with the androidDatabaseImplementation: 2 setting.