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 mysql> show variables like ' character% '; +--------------------------+---------------------------- +| variable_name |                    Value |+--------------------------+----------------------------+|character_set_client |latin1 || Character_set_connection |latin1 | | Character_set_database |latin1 | | Character_set_filesystem |binary | | Character_set_results |latin1 | | Character_set_server |latin1 | | Character_set_system |utf8 | | Character_sets_dir | /usr/share/mysql/charsets/|+--------------------------+----------------------------+8 rows in Set (0.00 sec) When the character encoding is latin1, the user inserts a character type (char) record into the data table, which may not display the character type's data properly when displaying this record, 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: 1.server itself set problems, such as still stay in the Latin1 2.table language setting problem (including character and collation) 3. The connection language setting problem for client programs (e.g. PHP) It is highly recommended to use UTF8!!!! UTF8 can be compatible with all characters in the world!!!! First, avoid the creation of database and table in Chinese garbled and view encoding Method 1, the creation of the database: Create the "Test ' CHARACTER SET ' utf8 ' COLLATE ' utf8_general_ci '; 2. Create table ' Database_user ' (' ID ' varchar (+) NOT null default ' ', ' UserID ' varchar (+) NOT null default ', ') E Ngine=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: mysql> show variables like "%char%"; +--------------------------+---------------+ | variable_name | Value | +--------------------------+---------------+ | character_set_client | GBK | | character_set_connection | GBK | | Character_set_database | UTF8 | | Character_set_filesystem | binary | | Character_set_results | GBK | | Character_set_server | UTF8 | | Character_set_system | UTF8 | +--------------------------+-------------+ Note: Previous 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 for the test database: Mysql> show create DB test; +------------+------------------------------------------------------------------------------------------------+ | Database | Create Database | +------------+------------------------------------------------------------------------------------------------+ | Test | CREATE DATABASE ' test '/*!40100 DEFAULT CHARACTER SET GBK */| | +------------+------------------------------------------------------------------------------------------------+

3. View the encoding format for the YJDB database: mysql> show create table yjdb; | Yjdb | CREATE TABLE ' yjdb ' (' sn ' int (5) NOT NULL auto_increment, ' type ' varchar (TEN) NOT NULL, ' BRC ' varchar (6) ' is not null, ' Teller ' Int (6) Not NULL, ' telname ' varchar (TEN) is not NULL, ' date ' int (ten) is not null, ' count ' int (6) is not null, ' back ' int (ten) not NU LL, PRIMARY key (' SN '), unique key ' SN ' (' sn '), unique key ' sn_2 ' (' sn ')) Engine=myisam auto_increment=1826 DEFAULT CHARS ET=GBK row_format=dynamic |

Second, to avoid the import of data in Chinese garbled problem 1: Save the Data encoding format as Utf-8 set the default encoding to Utf8:set names UTF8; set the database db_name default to Utf8:alter CHARACTER SET UTF8 COLLATE utf8_general_ci; Set table Tb_name default encoding for 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 database db_name default encoding gbk:alter database ' db_name ' default CHARACTER set GBK COLLATE gbk_chinese_ci; Set table Tb_name default encoding for 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 working for a long time, there are many old data, can not change the setting of Simplified Chinese, then it is recommended to set the page encoding GBK, GBK and GB2312 is the difference between: GBK can show more characters than GB2312, to show 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 in the Web page code to tell the MySQL connection to use UTF8 or GBK;

Modify database MySQL character encoding to UTF8