MySQL setting UTF8 character set

MySQL character set meaning see MySQL manual section 10.1 character Set support

MySQL Character set conversion process please look at the Bird Brother blog This article

0. Initial state

Debian uses apt to install MySQL at the beginning of this

You can see the default

The sever character set is Latin1

The DB character set is Latin1

The MySQL CLI's client and connect character set are UTF8

(the default character set for the PDO connected client and connect is Latin1)

1. Server CharSet

The server's character set settings can be modified through the configuration file

Vim/etc/mysql/my.cnf

In the [Mysqld] section, add

Character_set_server = UTF8

Restart MySQL

You can see that the default character set for the server becomes UTF8

2. DB CharSet

After setting the server character set, the default character set of the database becomes the same as the server UTF8

In addition, in the case of modifying the server character set, you can also set the database character sets by specifying the character set when you create the database

For example, the default character set for both server and database is Latin1

Create a database test

Create DATABASE test character set UTF8;

At this point the default server and DB character sets are still latin1

However, the default character set for the database test is now modified to UTF8

3.table CharSet

Sets the character set of the database, and the character set of the table uses the character set of the database if it is not set

In addition, you can specify a table's character set when you create a table

CREATE TABLE ' user ' (
' id ' int (ten) unsigned NOT NULL auto_increment,
' name ' varchar (+) ' NOT null DEFAULT ' ',
' Email ' varchar ' NOT null default ',
' age ' tinyint (3) unsigned NOT NULL default ' 0 ',
PRIMARY KEY (' id ')
) E Ngine=innodb DEFAULT Charset=utf8;

4. Column CharSet

Sets the character set of the table, and the character set of the table column uses the table's character set by default

In addition, you can specify the character set of a column when you create a table

CREATE TABLE ' user2 ' (
' id ' int (ten) unsigned not NULL auto_increment,
' name ' varchar CHARACTER SET latin1 not Null default ',
' email ' varchar (+) CHARACTER SET latin1 NOT null default ' ',
' age ' tinyint (3) unsigned NOT NULL Default ' 0 ',
PRIMARY KEY (' id ')
) engine=innodb default Charset=utf8;

Visible, table character set is UTF8, column character set is Latin1 5.conect charset

The client and connected character sets need to be set on the client, otherwise the default is Latin1

$dsn = ' mysql:host=127.0.0.1;dbname=test ';
$user = ' root ';
$password = ' root ';
$pdo = new PDO ($DSN, $user, $password);
$name = $_get[' name '];
$email = $_get[' email '];
$age = $_get[' age '];
$sql = ' INSERT INTO user (Name,email,age) VALUES (: Name,:email,:age) ';
$stmt = $pdo->prepare ($sql);
$stmt->bindparam (': Name ', $name, PDO::P aram_str);
$stmt->bindparam (': Email ', $email, PDO::P aram_str);
$stmt->bindparam (': Age ', $age, PDO::P aram_int);
$stmt->execute ();

By accessing

http://www.test.com/?name= Hello &[email protected]&age=20

Execute the above code, when the character set of the MySQL CLI is UTF8, the name shows garbled,

After the client connects to MySQL, it executes the

Set names UTF8;

Represented in the PHP code as

5.1

$pdo = new PDO ($DSN, $user, $password);
$pdo->exec (' Set names UTF8 ');

Access URL again, visible connection character set to UTF8

In addition, you can set character sets when you connect via PDO

5.2

$dsn = ' Mysql:host=127.0.0.1;dbname=test;charset=utf8 ';
$user = ' root ';
$password = ' root ';
$pdo = new PDO ($DSN, $user, $password);

And so

5.3

$attr = Array (Pdo::mysql_attr_init_command = ' SET NAMES UTF8 ');
$pdo = new PDO ($DSN, $user, $password, $attr);

Resources

MSYQL Manual http://dev.mysql.com/doc/refman/5.5/en/charset.html

Laruence Blog http://www.laruence.com/2008/01/05/12.html

StackOverflow Http://stackoverflow.com/questions/4361459/php-pdo-charset-set-names

Http://stackoverflow.com/questions/18496557/pdo-utf-8-encoding-issue

MySQL setting UTF8 character set