Modify database MySQL character encoding to UTF8

Source: Internet
Author: User

Modify database MySQL character encoding to UTF8

The MySQL database is an open-source database that is widely used. The following is a procedure for modifying the character encoding of a MySQL database. Step 1: View the current character encoding method     

when a character is encoded as Latin1 , the user inserts a record of a character type (char) into the data table,
The character type data may not be displayed properly when this record is displayed, so we modify the MySQL character encoding to UTF8. The operation is as follows: Step 2: Modify the MY.CNF configuration file as follows (/ETC/MY.CNF): [client] #password = Your_passwordport = 3306socket = /var/ Lib/mysql/mysql.sockdefault-character-set=utf8 #设置默认编码为UTF8
=============================================================================================================== ====
MySQL Chinese garbled problem perfect solution

MySQL will appear in Chinese garbled reason is the following points:

1. Theserver itself sets a problem, such as still staying in Latin1

2.table language setting problem (including character and collation)

3. UTF8 is strongly recommended for client-side programming (such as PHP) for connection language settings!!!!

UTF8 can be compatible with all characters in the world!!!!

First, avoid creating database and table in Chinese garbled and view encoding method

1: Create database ' Test '

CHARACTER SET ' UTF8 '

COLLATE ' Utf8_general_ci ';

2. Create table ' Database_user ' (

' ID ' varchar (+) not NULL default ' ',

' UserID ' varchar (+) not NULL default ' ',

) engine=innodb DEFAULT Charset=utf8;

With these 3 settings, there is no problem at all, that is, the same encoding format is used when building the library and building the table.

But if you have built libraries and tables, you can query them in the following ways.

1. View the default encoding format:

Note: Before 2 to determine, you can set the default encoding format using the set names Utf8,set names GBK;

The effect of performing set NAMES UTF8 is equivalent to setting the following:

SET character_set_client= ' UTF8 ';

SET character_set_connection= ' UTF8 ';

SET character_set_results= ' UTF8 ';

2. View the encoding format of the TESTJDBC database:

3. View the encoding format of the Citydatabase library:

Second, to avoid the import of data has the problem of Chinese garbled

1: Save the Data encoding format as Utf-8 set the default encoding to Utf8:set names UTF8;

Setting the database db_name default to UTF8:

       ALTER DATABASE ' db_name '

DEFAULT CHARACTER SET UTF8

COLLATE Utf8_general_ci;

Set table tb_name default encoding is UTF8:

ALTER TABLE ' Tb_name '

DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;

Import: LOAD DATA LOCAL INFILE ' c:\\utf8.txt ' into TABLE yjdb;

2: Save the data encoding format to ANSI (that is, GBK or GB2312) to set the default encoding to GBK:

Set names GBK;

Set the database db_name default encoding to GBK:

ALTER DATABASE ' db_name '

DEFAULT CHARACTER SET GBK

COLLATE Gbk_chinese_ci;

Set table Tb_name default encoding is GBK:

ALTER TABLE ' Tb_name '

DEFAULT CHARACTER SET GBK COLLATE gbk_chinese_ci;

Import: LOAD DATA LOCAL INFILE ' c:\\gbk.txt ' into TABLE yjdb;

Note: 1. UTF8 do not import GBK,GBK do not import UTF8;

The display of UTF8 is not supported under 2.dos;

Third, solve the problem of garbled pages in the Web site encoding is set to Utf-8, which can be compatible with all the characters in the world.

If the site has been in operation for a long time, there are many old data, can not change the Simplified Chinese settings,

It is recommended that you set the page encoding to GBK.

The difference between GBK and GB2312 is that:

GBK can display more characters than GB2312, to display the simplified code of traditional characters, you can only use GBK.

1. Edit/etc/my.cnf, add Default_character_set=utf8 in [MySQL] section;

2. When writing the connection URL, add the useunicode=true&characterencoding=utf-8 parameter; 3. Add a "Set names UTF8" or "Set names GBK" instruction to the page code,

Tell MySQL to use UTF8 or GBK to connect the content;

Modify database MySQL character encoding to UTF8

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.