Set Charset/Collation MySQL PHP?

Question

How to set collation for MySQL Connection?

// Here is default/Expected output
mysqli_get_charset($con);
object(stdClass)[2]
  public 'charset' => string 'utf8mb4' (length=7)
  public 'collation' => string 'utf8mb4_unicode_ci' (length=18)
  public 'dir' => string '' (length=0)
  public 'min_length' => int 1
  public 'max_length' => int 4
  public 'number' => int 224
  public 'state' => int 1
  public 'comment' => string '' (length=0)

mysqli_character_set_name($con);
\tests\db.php:22:string 'utf8mb4' (length=7)

After setting charset using :

mysqli_set_charset($con, 'utf8mb4');

// Output :

mysqli_get_charset($con);
object(stdClass)[2]
  public 'charset' => string 'utf8mb4' (length=7)
  public 'collation' => string 'utf8mb4_general_ci' (length=18)
  public 'dir' => string '' (length=0)
  public 'min_length' => int 1
  public 'max_length' => int 4
  public 'number' => int 45
  public 'state' => int 1
  public 'comment' => string 'UTF-8 Unicode' (length=13)

mysqli_character_set_name($con);
\tests\db.php:22:string 'utf8mb4' (length=7)

Tried using these :

mysqli_set_charset($con, 'utf8mb4');
mysqli_query($con, "SET NAMES 'utf8mb4';");
mysqli_query($con, "SET CHARACTER SET 'utf8mb4';");
mysqli_query($con, "SET COLLATION_CONNECTION = 'utf8mb4_unicode_ci';");

After using mysqli_set_charset($con, 'utf8mb4');

the collation resets to utf8mb4_general_ci.

Any way to prevent this.

How can I solve this ?

Answer 1

I believe you don't care for the client collation.

The only use for the charset-related stuff on the client is the escaping function but it uses only charset, and don't care for the collation, which is used only on the server.

Therefore, if you need to set the specific collation, the correct code would be

mysqli_set_charset($con, 'utf8mb4');
mysqli_query($con, "SET COLLATION_CONNECTION = 'utf8mb4_unicode_ci'");

After this, both proper charset and collation will be set.

You can test it with the following code:

var_dump(mysqli_get_charset($con)->collation,$con->query("select @@collation_connection")->fetch_row()[0]);
mysqli_set_charset($con, 'utf8mb4');
var_dump(mysqli_get_charset($con)->collation,$con->query("select @@collation_connection")->fetch_row()[0]);
mysqli_query($con, "SET COLLATION_CONNECTION = 'utf8mb4_unicode_ci'");
var_dump(mysqli_get_charset($con)->collation,$con->query("select @@collation_connection")->fetch_row()[0]);

For me it outputs

string(17) "latin1_swedish_ci"
string(17) "latin1_swedish_ci"
string(18) "utf8mb4_general_ci"
string(18) "utf8mb4_general_ci"
string(18) "utf8mb4_general_ci"
string(18) "utf8mb4_unicode_ci"

and the last one - the connection collation is what you actually need.