MySQL character collation (garbled) Problem Solving

MySQL character collation (garbled) Problem Solving

1. Create a MySQL database

1 CREATE DATABASE `test`2 CHARACTER SET 'utf8'3 COLLATE 'utf8_general_ci';

Create a table

 1 CREATE TABLE tp_wechatlocation 2 ( 3     ID INT NOT NULL AUTO_INCREMENT, 4     OpenID VARCHAR(50), 5     Latitude VARCHAR(10), 6     Longitude VARCHAR(10), 7     CityName VARCHAR(30), 8     CreateTime DATETIME, 9     PRIMARY KEY (ID)10 )ENGINE=INNODB DEFAULT CHARSET=utf8; 

Set the DEFAULT character sequence set for the table: ENGINE = innodb default charset = utf8

If the character sequence set of the default table is not added after data creation, the "CityName" garbled problem will occur.

　　

2. Solution

You can use SQL statements to set the character sequence set of a table.

The DEFAULT encoding is utf8: alter table name default character set utf8 COLLATE utf8_general_ci;

Ansi (GBK or GB2312): alter database 'db _ name' default character set gbk COLLATE gbk_chinese_ci;

Modify the default character sequence set of a column:

Alter table tp_wechatlocation MODIFY CityName VARCHAR (30) character set utf8 COLLATE utf8_general_ci;

3. UTF8 display is not supported in dos

Set character sets in Linux:

Chinese settings: export LANG = zh_CN.GB2312
Export LANG = zh_CN.UTF-8

4. view the character sequence set

View the replica set of the current database: show variables like 'character % ';

Variable_name Value
-----------------------------------------------------------------------
Character_set_client utf8
Character_set_connection utf8
Character_set_database latin1
Character_set_filesystem binary
Character_set_results utf8
Character_set_server utf8
Character_set_system utf8
Character_sets_dir E: \ wamp \ bin \ mysql \ mysql5.5.20 \ share \ charsets \

View table: show create table yjdb;

This article permanently updates the link address: