Charset and collation support #456

Problem

The driver parses SQL as UTF-8, and SQLite stores all text as UTF-8. MySQL character sets aren't currently mapped onto that. Connection charset settings are ignored, and declared charsets are recorded but never enforced. We need to declare and implement sensible support.

Charsets

MySQL operates with two charsets—storage charset and connection charset. In PHP, SQLite uses UTF-8 for both. In the SQLite driver, the connection charset will be UTF-8. For storage, we can also support single-byte charsets by checking their values on write and handling them correctly in special cases (e.g., when their binary values are used).

Let's declare character set support as follows:

1. Unicode charsets: Support all of them.

utf8mb4, utf16, utf16le, utf32 and gb18030 accept every Unicode character. utf8mb3 (utf8) and ucs2 accept only the Basic Multilingual Plane, which excludes most emoji. The check is "anything" or "code point ≤ U+FFFF".

2. Single-byte charsets: Support the most common ones. Start with ascii and latin1.

These are 25 single-byte charsets in total: ascii, latin1, latin2, latin5, latin7, cp1250, cp1251, cp1256, cp1257, cp850, cp852, cp866, koi8r, koi8u, greek, hebrew, tis620, armscii8, geostd8, dec8, hp8, keybcs2, macce, macroman and swe7. Each covers at most 256 characters, so a list of 256 characters per charset is enough. We could generate those lists from the mapping files in MySQL's source, which keeps them exact, including quirks like latin1 really being Windows-1252. That's a few KB of data in total.

We should start with ascii and latin1 and add other ones as needed.

3. Other multi-byte charsets: Reject.

Reject charsets like big5, gbk, gb2312, sjis, cp932, ujis, eucjpms and euckr. Support for some via mbstring may be considered in the future.

4. Binary: Support using SQLite binary types.

Connection

The connection charset is always UTF-8. Connection settings must reflect that as follows:

• Accept utf8mb4 and utf8mb3/utf8 in:
  • SET NAMES (SET NAMES DEFAULT means utf8mb4);
  • SET NAMES accepts an optional COLLATE clause. See the Collations section.
  • SET CHARACTER SET;
  • character_set_client, character_set_connection, and character_set_results variables (character_set_results also accepts NULL and binary = "no conversion");
  • DSN charset option.
• Reject any other charset with a clear error.
• Report the charset in @@character_set_client, @@character_set_connection, @@character_set_results, @@collation_connection, and SHOW VARIABLES.
• Input text must be valid UTF-8. Invalid byte sequences are handled like MySQL handles them for a utf8mb4 client, instead of being passed to SQLite as is.
• WordPress $wpdb->charset is always utf8mb4. A non-UTF-8 DB_CHARSET or DB_COLLATE is ignored and reported (e.g., in Site Health), not treated as a fatal error.

Collations

Collations can be addressed separately. Until then, all text columns keep using COLLATE NOCASE.

The support should be implemented as follows:

• Metadata: Record and report collations like charsets. Resolve them from column, table, and database defaults, validate them against the charset, and let columns inherit them. List all collations of supported charsets in INFORMATION_SCHEMA.COLLATIONS and SHOW COLLATION.
• Connection collation: SET NAMES ... COLLATE ... sets collation_connection (without COLLATE, the default collation of the charset). Validate it against the connection charset, report it in @@collation_connection and SHOW VARIABLES, and apply it to literals and other values that don't come from a column.
• Behavior: Map collations to built-in SQLite collations by their suffix and pad attribute:
  • _bin: RTRIM for PAD SPACE collations (e.g., utf8mb4_bin), and BINARY for NO PAD ones (e.g., utf8mb4_0900_bin). Exact for Unicode charsets.
  • _cs, _as_cs, _ks: BINARY. Equality is close, but ordering follows code points instead of language rules.
  • _ci, _ai_ci, _as_ci: NOCASE. Case folding is ASCII-only, accents are always significant (only right for _as_ci), and so are trailing spaces.
  • Language-specific collations (e.g., utf8mb4_czech_ci, utf8mb4_tr_0900_ai_ci) map by their suffix. Their language rules are not emulated.
• No rejection: Collations that can't be matched exactly are accepted and mapped as above. Unlike charsets, they don't change stored data, only comparisons, ordering, and uniqueness. The common defaults (utf8mb4_unicode_520_ci, utf8mb4_0900_ai_ci) can't be matched exactly either, so rejecting them would break almost every site.
• Reporting: Report tables and columns whose collations are not emulated exactly (e.g., in Site Health).
• Known gaps: SQLite has no Unicode case folding, accent-insensitive, or language-specific collations, and no case-insensitive collation that ignores trailing spaces. Custom collations registered in PHP could close some of these gaps, but other SQLite tools then fail on tables that use them ("no such collation sequence").
• Default: utf8mb4 defaults to utf8mb4_0900_ai_ci, like in MySQL 8. Changing that is a separate decision.

Other expected behavior

• Unsupported charsets (including single-byte ones not added yet) fail with a clear error in CREATE/ALTER TABLE and CREATE/ALTER DATABASE. Unknown names fail with MySQL error 1115.
• No rewriting: Supported charsets are kept as declared and validated, not rewritten (e.g., utf8mb3 is not rewritten to utf8mb4).
• No configuration option: There is no strict or lenient mode. Unsupported charsets always fail, and supported ones are always validated.
• Defaults resolve like in MySQL: column, then table, then database. DEFAULT means the database default, and columns without a charset inherit the table charset.
• utf8 is an alias of utf8mb3, reported as utf8mb3 when emulating MySQL 8.0.30+.
• CHARACTER SET binary turns CHAR, VARCHAR, and TEXT into BINARY, VARBINARY, and BLOB.
• Writes (INSERT, UPDATE, REPLACE) fail with MySQL error 1366 in strict mode when a value doesn't fit, and store what MySQL stores otherwise.
• ALTER TABLE changing a column charset checks existing values. ALTER TABLE ... CHARACTER SET changes the table default.
• Conversions: CONVERT(... USING ...), CAST(... AS CHAR CHARACTER SET ...), and introducers (_latin1'...') apply the target charset (? for characters it can't represent). This also makes wpdb::strip_invalid_text() work.
• Byte-level functions (LENGTH(), HEX(), CAST(... AS BINARY)): decide which return the bytes of a non-UTF-8 declared charset.
• SHOW CREATE TABLE prints column CHARACTER SET and COLLATE when they differ from the table default, so exports keep them.
• INFORMATION_SCHEMA.CHARACTER_SETS and SHOW CHARACTER SET list the supported charsets.
• Existing databases: Already recorded unsupported charsets (e.g., a big5 column) keep working without checks, and are reported (e.g., in Site Health).
• Behavior change: New non-utf8mb4 columns start rejecting characters accepted today (e.g., emoji in utf8). Document it.

To consider: Before releasing, run a plugin test set (e.g., Playground Tester) to measure the impact of the new errors and write checks.