MySQL setting UTF8 character set

Source: Internet
Author: User
Tags dsn mysql manual

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.