1 CREATE DATABASE `test` 2 CHARACTER SET 'utf8' 3 COLLATE 'utf8_general_ci';
创建表
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;
设置表的默认字符编码集：ENGINE=INNODB DEFAULT CHARSET=utf8
如果在创建数据后面不加设置默认表的字符编码集就会出现如下图“CityName”乱码问题
2、解决
通过sql语句主动设置表的字符编码集。
默认编码为utf8:ALTER TABLE 表名 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ansi(即GBK或GB2312)：ALTER DATABASE `db_name` DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
修改列的默认字符编码集：
ALTER TABLE tp_wechatlocation MODIFY CityName VARCHAR(30)CHARACTER SET utf8 COLLATE utf8_general_ci;
3、dos下不支持UTF8的显示
Linux系统下设置字符集：
中文设置：export LANG=zh_CN.GB2312
英文设置：export LANG=zh_CN.UTF-8
4、查看字符编码集
查看当前数据库的相关编码集：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\
查看表：show create table yjdb;