Question

1.数据库字符集

默认情况下，MySQL 使用 cp1252(Latin1) 字符集根据 Swedish/Finnish 规则进行排序。这些 默认值适合美国和西欧大部分国家（server: latin1_swedish_ci, InnoDB）。

当客户端连接 MySQL 服务器时，服务器告诉客户端服务器的默认字符集是什么。客户端切换到该字符集进行连接。

MySQL 对于字符集的指定可以到服务器，数据库，表，列，连接等。缺省字符集为 latin1。

数据库字符集编码的 优先级 （小粒度优先）：表>数据库 database>服务器 server，因此表的缺省字符集同数据库，数据库的缺省字符器同服务器。

1) 查看支持的字符集和校对

字符集是一套符号和编码。校对规则是在字符集内用于比较字符的一套规则。

mysql> SHOW CHARSET;
mysql> SHOW COLLATION;

2) 查看缺省的字符集和校对字符集

说明：

• character_set_server 是最先开始的，在编译时缺省设置，而 linux 缺省也是 latin1，所以通常编译后此值缺省为 latin1
• character_set_database 创建数据库时若未设定，则缺省同 character_set_server;创建表时未設定，则缺省为 character_set_database; 创建字段时则缺省为表的选项

下面命令如果是在刚进入 mysql 客户端，未调用 use database 前查看，则显示的是缺省字符集；否则显示的是当前正在使用数据的字符集。其中命令 SET NAMES 会立即改变 client/connect/result 的值。

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+------------------------------------+
| Variable_name      | Value               |
+--------------------------+------------------------------------+
| character_set_client   | latin1               |
| character_set_connection | latin1               |
| character_set_database  | latin1               |
| character_set_results  | latin1               |
| character_set_server   | latin1               |
| character_set_system   | utf8                |
| character_sets_dir    | /data1/mysql/share/mysql/charsets/ |
+--------------------------+------------------------------------+

处理流程 大概如下，

1、发送请求

客户端(character_set_client）=> 数据库连接(character_set_connection)=>存储(table,column)

2、返回请求

存储(table,column)=>数据库连接(character_set_connection )=>客户端(character_set_results)

• 数据库物理存储的字符集变量 ：character_set_system
• 数据库的字符集变量是 character_set_database;
• 服务器字符集变量是 character_set_server;
• 客户端发送查询使用的字符集：character_set_client
• 服务器接收到查询后，将查询从 character_set_client 系统变量转换到 character_set_connection
• 服务器返回查询结果到客户端使用的字符集: character_set_results
• SET NAMES 显示客户端发送的 SQL 语句中使用什么字符集，影响到连接字符集 client/connection/result 三个变量。SET character_set 则为默认数据库设置连接字符集。

  mysql> SHOW VARIABLES LIKE 'collation_%';
  +----------------------+-------------------+
  | Variable_name    | Value       |
  +----------------------+-------------------+
  | collation_connection | latin1_swedish_ci |
  | collation_database  | latin1_swedish_ci |
  | collation_server   | latin1_swedish_ci |
  +----------------------+-------------------+
  

3、查看当前数据库/表/列的字符集

注：SHOW VARIABLES 看到的字符集就是当前正在使用数据库的字符集，非缺省字符集。

mysql> status;

查看 database 使用的字符集： show create database [db_name];

查看表的字符集： show create table [tbl_name];

查看数据库中每个表的字符集： SHOW FULL COLUMNS FROM [tbl_name];

综合查看： show table status from [db_name];

如果每一列后未说明字符集，则列的字符集和表一样。

4、修改 MySQL 字符集

以下 set 只能影响当前数据库，如果要影响缺省项，可修改 my.ini 或 my.cnf

my.cnf

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8 # 这个值会影响到 server/database/表/列的缺省值
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

常规修改命令 ：

a) 影响 character_set_database

set character_set_server=gbk; //设置默认新建数据库编码为 gbk
alter database [db_name] character set utf8; //更改现有数据库编码
alter table [tbl_name] character set utf8; //更改现有数据库里表的编码

b) 影响连接字符集 client/connection/result

SET NAMES 显示客户端发送的 SQL 语句中使用什么字符集，影响到连接字符集 client/connection/result 三个变量。

SET NAMES UTF8

c) 查询时代码编码（下例是 PHP，utf8 最好不要写成 utf-8）

mysql_query("set names ’utf8’ ");
mysql_query("set character_set_client=utf8");
mysql_query("set character_set_results=utf8");

MySQL 字符集小结:

目前的处理方式是字段如果包含中文，使用 GBK 存储。而内部存储为 UTF-8(charset_set_system)，而客户端取数据时，要将字段转化成 GBK.要想显示正常，

1）连接字符集变量与表的字符集变量一致。

若不一致，像 phpmyadmin 就不能正确识别中文，换了终端环境（如 Linux 登陆 MySQL 客户端，缺省字符集都是 latin1; 而 WINDOW 下是 utf-8），连接字符集可能发生改变，最好在插入和查询时都设置与存储字段一致的编码 SET NAMES UTF8 。

另外为了正确识别中文，只能选择 utf8 或者 gbk，建议 utf8 因为若 phpmyadmin 的中文都用 utf8 来标识。

2）平台 locale 和 termianl 要与表内容的实际字符集一致。

附录：很烦很复杂的 MySQL 字符集

• character_set_server 服务器使用字符集，默认是 latin1
• character_set_database 数据库使用字符集，可 set character_set_database=''
• character_set_system 系统内部元数据使用字符集，默认是 utf8
• character_set_client 客户端指定字符集
• character_set_connection 连接时使用的字符集
• character_set_results 返回结果的字符集

校对字符集的 latin1_swedish_ci 是 latin1 的默认值

• collation_connection latin1_swedish_ci
• collation_database latin1_swedish_ci
• collation_server latin1_swedish_ci

备注 ：

utf8_general_ci 和 utf8_unicode_ci 的差别 ：主要在德语和法语上。对于我们中国人来说，一般使用 general，因为 general 更快。

utf8_bin_ci ：在字符对比时，unicode 和 general 都不是大小写敏感的；如果要求大小写敏感的话，就使用 utf8_bin_ci.

2.数据库表字段内容中文的正常显示

中文要正常显示，需用能够正确显示的 中文编码如 gbk 或者 utf8。

数据库存储中常使用 latin1，如果字段编码也是 latin1，并且字段值出现了中文，那么这在 MySQL 客户端/PHPMYADMIN 都不能正确显示中文。

如果要得到中文结果，需设置 SET NAMES 为支持中文正确显示的编码如 GBK 或者 UTF8 （注意不是 UTF-8）。

注意：如果字段编码是 latin1。

数据库编码转化

情形 1： latin1-->utf-8 原始表和字段编码是 latin1，目标表和字段编码是 utf-8。

解决方案：

• 转化多表：
• 转化单表（某表名 game3） alter table game3 convert to character set utf8
• 转化字段（某字段名 name）：

alter table game3 change name name varchar(255) character set utf8

注意：alter 语句只改变了表或字段的结构，并没有改变已经存储数据的编码。

基本处理流程：

以原数据库表字段编码为 latin1 为例，目标编码 utf8，数据库名 test, 表名 game3。

方法 1：手动和脚本处理（5 步）

1） 确定目标字段的编码：latin1（ISO-8859-1）

2） 导出原 latin1 的数据如 data.sql（仅数据，要保证导出的文件不乱码才是正确的），相当于数据备份。

mysqldump -uroot -p12341234 --default-character-set=latin1 -q --single-transaction -t test game3> data.sql

3） 将字段所在表重建结构或仅对这个字段编码改为 utf8（本例表为 game3,字段为 name）

备注：这个过程可能原字段的索引会超过 1000 字节而失效，可删除过长索引，等数据转化成功后重建新索引。因为 gbk 会导致长度增加 2 倍，utf8 则是 3 倍左右。

全表： alter table game3 convert to character set utf8

字段： alter table game3 change name name varchar(255) character set utf8

4） 将原数据 data.sql 转化为 utf8，并将 set names 的编码从 latin1 改为 utf8。（如果目标字段编码是 gbk，此处文件编码可以是 utf8，但需将 set names 项改为 gbk）。

此步可用 editplus 修改，也可用代码修改。

sed -i s/SET NAMES latin1/SET NAMES utf8/g `grep -rl "SET NAMES latin1" data.sql `
sed -i 's/latin1/utf8/g' data.sql
iconv -f gb18030 -c -t UTF-8 data.sql -o data_convert.sql

5. 导入数据

mysql -uroot -p12341234 -f test < data2-u.sql

方法 2：phpmyadmin 导入导出处理（3 步）

1. 导出格式为文件编码为 latin1 的数据和结构 SQL 文件（导出文件中的中文要保证能正常显示。导出数据是插入操作；导出结构只是创建索引，真正的导出结构是在 show create table ）。此步建议只导出数据，因为导出结构生成索引的过程常会出现索引过长的问题，可在数据导完后，单独创建索引。
2. 修改表或字段结构：包括清空原表，修改表或字段编码。可能需要手动在文件头添加 SET NAMES 语句和编码变量赋值语句。
3. 导入修改过 SET NAMES UTF8（不能是其它编码）和文件编码已经转化成 UTF-8 的 SQL 文件。

这时浏览表内容，如果中文显示正常，则 OK。

字段中文乱码常见情形

字段内容中文的正常显示主要出现在三种情形：

• 情形 1：MySQL 客户端
• 情形 2：PHPMyadmin
• 情形 3：目标 HTML 页

情形 1：MySQL 客户端中文显示乱码。

问题描述 ：unix 平台，locale 和 terminal 都是 GBK 的情况下，进入 mysql cmd，查询可以正常显示中文内容。windows 平台：cmd 下进入 mysql cmd，查询显示中文乱码。

解决方案 ：这跟不同平台的 MySQL 客户端设置的缺省连接字符集相关。WINDOWS 平台 MySQL 客户端连接字符集通常是 GBK，而 Linux 平台则常是 latin1。（如果连接 SQL 没有非 ANSI 字符，那么无需改变连接字符集；但结果集含中文者需要相应处理）。

SHOW VARIABLES LIKE 'character%'; 可获取到服务端缺省的连接/系统/存储字符集。

(因为表是 latin1，后 set names latin1，中文显示正常。后改表为 GBK，set 除 system 后变量均为 GBK，中文仍乱码）

情形 2：phpmyadin 中文显示乱码

在 phpMyAdmin 首页右侧中查看服务器字符集如下：

服务器字符集： UTF-8 Unicode (utf8)

在 phpMyAdmin 中创建数据库时指定字符集： 选择整理。

备注 ：若 phpmyadmin 浏览表时字段值不能正常显示页面，则也不能正确导入导出。

情形 3：HTML 页面中文显示乱码

解决页面中文乱码可从下面三个角度综合分析：（三者要保持一致，或最终显示文档要转化成相应编码）

1. 页面文档编码
2. 数据库编码
3. 连接数据库时使用的编码 mysql_query(xxxxxxx) 这里

3.中文查找和全文索引

参考：全文搜索功能

http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/functions.html#fulltext-boolean

中文查找有模糊查找和全文索引两种方式，其中模糊查找是正则表达式的字符匹配（未用到索引）；全文索引 MySQL 只支持英文索引，中文索引需要做一些特殊处理。

模糊查找: 没用到索引 LIKE 使用 %% 分隔。LIKE 是用 Regular Expression 去做查询，所有语言都支持。

示例： name LIKE '%%%s%%'

全文索引 ：MySQL 支持全文索引和搜索功能，但只针对空格、逗号和点断句的语言（如拉丁语言系）。MySQL 中的全文索引类型 FULLTEXT 的索引。 FULLTEXT 索引仅可用于 MyISAM 表；他们可以从 CHAR、VARCHAR 或 TEXT 列中作为 CREATE TABLE 语句的一部分被创建，或是随后使用 ALTER TABLE 或 CREATE INDEX 被添加。对于较大的数据集，将你的资料输入一个没有 FULLTEXT 索引的表中，然后创建索引， 其速度比把资料输入现有 FULLTEXT 索引的速度更为快。

全文搜寻的语法：

MATCH (col1, col2,...)  AGAINST (expr [search_modifier])

三种搜寻方式：

• IN BOOLEAN MODE ：布尔模式~可以使用 stopwords，不自动相关性排序。
• IN NATURAL LANGUAGE MODE：自然语言模式
• IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION / WITH QUERY EXPANSION ：查询扩展检索

  示例：

SELECT *
 FROM article
 WHERE MATCH(title, body)
 AGAINST ('xxx' IN NATURAL LANGUAGE MODE);

注意事项

预设搜寻是不分大小写，若要分大小写，columne 的 character set 要从 utf8 改成 utf8_bin。

预设 MATCH...AGAINST 是以相关性排序，由高到低。

MATCH(title, content) 里的字段必须和 FULLTEXT(title, content) 里的字段一模一样。如果只要单查 title 或 content 一个字段，那得另外再建一个 FULLTEXT(title) 或 FULLTEXT(content)

MySQL 的 FULLTEXT 怎么断字：

字母、数字、底线的组合视为一个字，不会把底线断字。
会被断字的字符：空白、逗号（,）与点（.），但不用这些断字的语言，如中文，就得自行手动断字。

一些词在全文搜索中会被忽略：

• 任何过于短的词都会被忽略。 全文搜索所能找到的词的默认最小长度为 4 个字符。
• 停止字中的词会被忽略。禁用词就是一个像“the” 或“some” 这样过于平常而被认为是不具语义的词。存在一个内置的停止字， 但它可以通过用户自定义列表被改写。请参见 12.7.5 节，微调 MySQL 全文搜索 。

默认的停止字在 12.7.3 节，全文停止字 中被给出。默认的最小单词长度和 停止字可以被改变，如 12.7.5 节，微调 MySQL 全文搜索 中所述。

中文全文索引

步骤 1 配置 my.ini ，在 my.ini 末尾添加如下：

# 修改全文检索的最小许可字符为 2 个字符或汉字（默认是 4，但中文分词时会出现单字 2 字符~GBK，3 字符~UTF-8）。
ft_min_word_len = 2

# 查询变量值：ft_min_word_len
$SHOW VARIABLES LIKE 'ft_min_word_len'

解决方案 1 ：整句的中文分词，并按 urlencode、区位码、base64、拼音等进行编码使之以“字母+数字”的方式存储于数据库中。

解决方案 2: 扩展插件 sphinx

详见 搜索引擎实现方案 章节 SPHINX+MySQL