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

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 ?

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` = ?;

You do not need to know character set to see whether the field is binary.

Consider this example:

   if ($result = $mysqli->query($query)) {

        /* Get field information for all columns */
        $finfo = $result->fetch_fields();

        foreach ($finfo as $val) {
            printf("Name:      %s
",   $val->name);
            printf("Table:     %s
",   $val->table);
            printf("Max. Len:  %d
",   $val->max_length);
            printf("Length:    %d
",   $val->length);
            printf("charsetnr: %d
",   $val->charsetnr);
            printf("Flags:     %d
",   $val->flags);
            printf("Type:      %d

", $val->type);
        }
        $result->free();
    }

You have $field->flags property. Check it for bit 128 (0x80). If the bit is set then the field is binary (BINARY, VARBINARY) and has "binary" collation.

I am not sure that you can set "binary" collation on non-binary field.

SELECT  IFNULL(COLLATION_NAME, 'binary')
    FROM  `COLUMNS`
    WHERE  table_schema = 'biglim'
      AND  table_name = 'article'

will find the collation associated with a particular table in a particular database (schema).