如何从结果集中获取字符集？

Question

mysqli_result::fetch_field() returns a type property for each column, which is an integer value.

The integer value is the same for VARCHAR and VARBINARY (0xFD) columns and also for CHAR and BINARY (0xFE) columns. Those column types can be detected with MYSQLI_TYPE_STRING and MYSQLI_TYPE_VAR_STRING constants.

To know if a string column is BINARY (they have a collation called binary), or to know if columns need to be converted to another character set, the character set name is really needed.

But... mysqli_result::fetch_field() has a charsetnr property which again returns an integer for the character set. Only this time there seems to be no way of knowing the character set name, let alone the collation?

So how can one get the character set names from mysqli_result ?

Answer 1

TL;DR charsetnr is the ID of the collation as listed by SHOW COLLATION.

I couldn't help noticing that even for numeric columns charsetnr was set to 63. This pointed me towards the manual that says:

To distinguish between binary and nonbinary data for string data types, check whether the charsetnr value is 63. If so, the character set is binary, which indicates binary rather than nonbinary data. This enables you to distinguish BINARY from CHAR, VARBINARY from VARCHAR, and the BLOB types from the TEXT types.

Retrieve more information about the collation and character set via:

SELECT `COLLATION_NAME`
     , `CHARACTER_SET_NAME`
     , `IS_DEFAULT`
     , `IS_COMPILED`
     , `SORTLEN`
FROM `INFORMATION_SCHEMA`.`COLLATIONS`
WHERE `ID` = ?;