3

I am trying to set a very simple session connection variable on MySQL, but it doesn't do anything. The queries run below do not cause any errors, but the character set of the MySQL connection won't be changed. If I configure the default values for 'collation_server' and 'character_set_server' in the my.ini file as utf8mb4 instead of latin1, then the character set becomes utf8mb4, but I wonder why I cannot change the connection character set from my PHP script.

$pdo = new \PDO("mysql:host=localhost", "root", "");
$pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

$pdo->query("SET NAMES utf8mb4");

print_r($pdo->query("SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';")->fetchAll(PDO::FETCH_ASSOC));

This query shows that the SET NAMES statement did not have affect.

user2180613
  • 739
  • 6
  • 21
  • Each query will stand on it's own. Take a look at [PHP Manual Multiple Statements](http://php.net/manual/en/mysqli.quickstart.multiple-statement.php) – Sloan Thrasher Apr 15 '18 at 13:27
  • 1
    Try putting `charset=utf8mb4` in the DSN instead. – Funk Forty Niner Apr 15 '18 at 13:39
  • @SloanThrasher This is PDO, not mysqli. – chris85 Apr 15 '18 at 13:40
  • The `GLOBAL VARIABLES` is still set to what it is. – chris85 Apr 15 '18 at 13:40
  • 1
    I've previously written an answer https://stackoverflow.com/a/31899827 that details the charset of a PHP application with a database. There's also the more in depth topic here https://stackoverflow.com/questions/279170/utf-8-all-the-way-through - if you take your time to read them, you'll find your answer – Qirel Apr 15 '18 at 13:43

2 Answers2

1

You are setting the charset for the session (not global). Try

SHOW SESSION VARIABLES WHERE ...

Note that with PDO you can set the character set as part of the DSN:

$pdo = new \PDO("mysql:host=localhost;charset=utf8mb4", "root", "");
Paul Spiegel
  • 30,925
  • 5
  • 44
  • 53
1

You must use SHOW SESSION VARIABLES to see the changes, not SHOW GLOBAL VARIABLES.

SET NAMES doesn't change GLOBAL variables. It only changes the character set variables for your current session.

https://dev.mysql.com/doc/refman/5.7/en/charset-connection.html says:

A SET NAMES 'charset_name' statement is equivalent to these three statements:

SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET character_set_connection = charset_name;

Those statements change session variables only.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Yes that makes sense. I copied the `SHOW VARIABLES` statement from stackoverflow and overlooked the word `GLOBAL`. – user2180613 Apr 15 '18 at 14:01