SET整理和字符UTF8 - Mysql PDO

Question

I would like to know the following: When you type in mysql the following commands:

SHOW VARIABLES LIKE 'collation%';
SHOW VARIABLES LIKE 'character_set%';

It returns collation and character, so I configured to let them in UTF8 in this situation.

array (size=3)
  0 => 
    array (size=2)
      'Variable_name' => string 'collation_connection' (length=20)
      'Value' => string 'latin1_swedish_ci' (length=17)
  1 => 
    array (size=2)
      'Variable_name' => string 'collation_database' (length=18)
      'Value' => string 'utf8_general_ci' (length=15)
  2 => 
    array (size=2)
      'Variable_name' => string 'collation_server' (length=16)
      'Value' => string 'latin1_swedish_ci' (length=17)
array (size=8)
  0 => 
    array (size=2)
      'Variable_name' => string 'character_set_client' (length=20)
      'Value' => string 'latin1' (length=6)
  1 => 
    array (size=2)
      'Variable_name' => string 'character_set_connection' (length=24)
      'Value' => string 'latin1' (length=6)
  2 => 
    array (size=2)
      'Variable_name' => string 'character_set_database' (length=22)
      'Value' => string 'utf8' (length=4)
  3 => 
    array (size=2)
      'Variable_name' => string 'character_set_filesystem' (length=24)
      'Value' => string 'binary' (length=6)
  4 => 
    array (size=2)
      'Variable_name' => string 'character_set_results' (length=21)
      'Value' => string 'latin1' (length=6)
  5 => 
    array (size=2)
      'Variable_name' => string 'character_set_server' (length=20)
      'Value' => string 'latin1' (length=6)
  6 => 
    array (size=2)
      'Variable_name' => string 'character_set_system' (length=20)
      'Value' => string 'utf8' (length=4)
  7 => 
    array (size=2)
      'Variable_name' => string 'character_sets_dir' (length=18)
      'Value' => string 'c:\wamp\bin\mysql\mysql5.6.12\share\charsets\' (length=45)

How do I use PDO like this:

, \PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"
                        //, \PDO::MYSQL_ATTR_INIT_COMMAND => "SET CHARACTER SET UTF8"
                        //, \PDO::MYSQL_ATTR_INIT_COMMAND => "SET character_set_client=utf8"
                        //, \PDO::MYSQL_ATTR_INIT_COMMAND => "SET character_set_connection = utf8"
                        //, \PDO::MYSQL_ATTR_INIT_COMMAND => "SET character_set_database = utf8"
                        //, \PDO::MYSQL_ATTR_INIT_COMMAND => "SET character_set_results = utf8"
                        //, \PDO::MYSQL_ATTR_INIT_COMMAND => "SET character_set_server = utf8"
                        //, \PDO::MYSQL_ATTR_INIT_COMMAND => "SET character_set_system = utf8"
                        //, \PDO::MYSQL_ATTR_INIT_COMMAND => "SET collation_server = utf8_general_ci"
                        //, \PDO::MYSQL_ATTR_INIT_COMMAND => "SET collation_database = utf8_general_ci"
                        //, \PDO::MYSQL_ATTR_INIT_COMMAND => "SET collation_connection = utf8_general_ci"

With the use of "SET NAMES utf8", only the character_set_server is not assigned UTF-8.

, \PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"


5 => 
    array (size=2)
      'Variable_name' => string 'character_set_server' (length=20)
      'Value' => string 'latin1' (length=6)

When I try to set the collation, he reversed:

, \PDO::MYSQL_ATTR_INIT_COMMAND => "SET collation_server = utf8_general_ci"

array (size=3)
  0 => 
    array (size=2)
      'Variable_name' => string 'collation_connection' (length=20)
      'Value' => string 'latin1_swedish_ci' (length=17)
  1 => 
    array (size=2)
      'Variable_name' => string 'collation_database' (length=18)
      'Value' => string 'utf8_general_ci' (length=15)
  2 => 
    array (size=2)
      'Variable_name' => string 'collation_server' (length=16)
      'Value' => string 'utf8_general_ci' (length=15)

How to set everyone on UTF-8 in the PDO?

Answer 1

In general, you rarely need to set anything other than the 3 VARIABLES that SET NAMES changes. Monkeying with the rest of the settings is asking for trouble.

To work with utf8, you need

• The bytes in the client need to be utf8-encoded.
• SET NAMES utf8 (or equivalent)
• CHARACTER SET utf8 on the relevant columns
• meta tag specifying utf8, if you are using html

If you are dealing with Chinese, you may want utf8mb4 instead if utf8.

If you end up with some form of garbage (question marks, truncation, mojibake, double-encoding), post another question, including SELECT col, HEX(col) ... to help debug it.

I have not mentioned COLLATION because it is only needed for sorting and comparisons. If you are having trouble with such, provide specifics.