mysqk怎样实现gbk_bin编码又不区分大小写？

comey 2011-10-30 01:04:42

名字字段可能是中英文混合的
为了按中文排序名字字段采用gbk_bin编码，可是搜索时又需要按不区分大小写，怎么实现才能达到效率比较高？

...全文

184 17 打赏收藏转发到动态举报

写回复

用AI写文章

17 条回复

切换为时间正序

请发表友善的回复…

发表回复

ACMAIN_CHM 2011-10-31

打赏
举报

[Quote]引用 4 楼 wwwwb 的回复:
select * from tt where upper(f1)='XX' or lower(f1)='xx'
or
WHERE a.`subject` COLLATE gb2312_chinese_ci='xx'

这两个效率哪个好？[/Quote]

这个直接的字符集转换明显会比UPPER函数要快。但这个建议还是直接把你的字段设置为gb2312_chinese_ci,这样可以在查询中直接利用索引。

ACMAIN_CHM 2011-10-31

打赏
举报

[Quote]直接gb2312_chinese_ci，中文不能按拼音排序[/Quote]
下面例子，并没有汉字排序的顺序啊。当然GB2312本身并不是严格按拼音排序的。理论上说是按照GB2312的编码顺序来排序，而这个编码顺序的前面常用汉字除多音字外是按拼音排的。

mysql> show full columns from x;

+-------+-------------+-------------------+------+

| Field | Type        | Collation         | Null |

+-------+-------------+-------------------+------+

| id    | int(11)     | NULL              | NO   |

| c1    | varchar(30) | gb2312_chinese_ci | YES  |

| c2    | varchar(30) | gb2312_bin        | YES  |

+-------+-------------+-------------------+------+

3 rows in set (0.02 sec)



mysql>

mysql> select * from x;

+----+--------+--------+

| id | c1     | c2     |

+----+--------+--------+

|  1 | A1000  | A1000  |

|  2 | a2000  | a2000  |

|  3 | A3000  | A3000  |

|  4 | 中000  | 中000  |

|  5 | 不000  | 不000  |

|  6 | A音000 | A音000 |

+----+--------+--------+

6 rows in set (0.03 sec)



mysql> select * from x order by c1;

+----+--------+--------+

| id | c1     | c2     |

+----+--------+--------+

|  1 | A1000  | A1000  |

|  2 | a2000  | a2000  |

|  3 | A3000  | A3000  |

|  6 | A音000 | A音000 |

|  5 | 不000  | 不000  |

|  4 | 中000  | 中000  |

+----+--------+--------+

6 rows in set (0.00 sec)



mysql> select * from x order by c2;

+----+--------+--------+

| id | c1     | c2     |

+----+--------+--------+

|  1 | A1000  | A1000  |

|  3 | A3000  | A3000  |

|  6 | A音000 | A音000 |

|  2 | a2000  | a2000  |

|  5 | 不000  | 不000  |

|  4 | 中000  | 中000  |

+----+--------+--------+

6 rows in set (0.00 sec)



mysql>

jinguanding 2011-10-31

打赏
举报

你为了那个小功能的话，会导致无法使用索引的，建议使用：gb2312_chinese_ci校对规则进行匹配比较

但是可以考虑对输出记录集时，进行校对规则转换，也即SELECT CONVERT(...) FROM

#****************************************#
MySQL技术及运维自动化网：www.mysqlops.com

新浪微博账号：http://weibo.com/mysqlops
#****************************************#

comey 2011-10-31

打赏
举报

原来　
SELECT * FROM gpy_manufacturer ORDER BY CONVERT(name USING gbk)
可以对gb2312_chinese_ci中文进行排序，不用gbk_bin了

问题圆满解决，结帖

comey 2011-10-31

打赏
举报

[Quote=引用 7 楼 wwwwb 的回复:]
估计这个吧，WHERE a.`subject` COLLATE gb2312_chinese_ci='xx'
[/Quote]

哇，回复真迅速
谢谢啊！！！！！！！！！

wwwwb 2011-10-31

打赏
举报

估计这个吧，WHERE a.`subject` COLLATE gb2312_chinese_ci='xx'

comey 2011-10-31

打赏
举报

[Quote=引用 4 楼 wwwwb 的回复:]
select * from tt where upper(f1)='XX' or lower(f1)='xx'
or
WHERE a.`subject` COLLATE gb2312_chinese_ci='xx'
[/Quote]

这两个效率哪个好？

comey 2011-10-31

打赏
举报

[Quote=引用 1 楼 acmain_chm 的回复:]
为什么不直接使用 gb2312_chinese_ci ?
[/Quote]

直接gb2312_chinese_ci，中文不能按拼音排序

wwwwb 2011-10-31

打赏
举报

or
WHERE a.`subject` COLLATE gb2312_chinese_ci='xx'

wwwwb 2011-10-31

打赏
举报

select * from tt where upper(f1)='XX' or lower(f1)='xx'

ACMAIN_CHM 2011-10-31

打赏
举报

中文需要单独另外安装插件
[Quote]12.7. 全文搜索功能
12.7.1. 布尔全文搜索
12.7.2. 全文搜索带查询扩展
12.7.3. 全文停止字
12.7.4. 全文限定条件
12.7.5. 微调MySQL全文搜索
MATCH (col1,col2,...) AGAINST (expr [IN BOOLEAN MODE | WITH QUERY EXPANSION])
MySQL支持全文索引和搜索功能。MySQL中的全文索引类型FULLTEXT的索引。 FULLTEXT 索引仅可用于 MyISAM 表；他们可以从CHAR、 VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或 CREATE INDEX被添加。对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。

关于全文搜索的限制列表，请参见 12.7.4节，“全文限定条件”.

全文搜索同MATCH()函数一起执行。

mysql> CREATE TABLE articles (
-> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> title VARCHAR(200),
-> body TEXT,
-> FULLTEXT (title,body)
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO articles (title,body) VALUES
-> ('MySQL Tutorial','DBMS stands for DataBase ...'),
-> ('How To Use MySQL Well','After you went through a ...'),
-> ('Optimizing MySQL','In this tutorial we will show ...'),
-> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
-> ('MySQL vs. YourSQL','In the following database comparison ...'),
-> ('MySQL Security','When configured properly, MySQL ...');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM articles
-> WHERE MATCH (title,body) AGAINST ('database');
+----+-------------------+------------------------------------------+
| id | title | body |
+----+-------------------+------------------------------------------+
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)
MATCH()函数对于一个字符串执行资料库内的自然语言搜索。一个资料库就是1套1个或2个包含在FULLTEXT内的列。搜索字符串作为对AGAINST()的参数而被给定。对于表中的每一行, MATCH() 返回一个相关值，即, 搜索字符串和 MATCH()表中指定列中该行文字之间的一个相似性度量。

在默认状态下, 搜索的执行方式为不区分大小写方式。然而,你可以通过对编入索引的列使用二进制排序方式执行区分大小写的全文搜索。例如，可以向一个使用latin1字符集的列给定latin1_bin 的排序方式，对于全文搜索区分大小写。

如上述所举例子，当MATCH()被用在一个 WHERE 语句中时，相关值是非负浮点数。零相关的意思是没有相似性。相关性的计算是基于该行中单词的数目, 该行中独特子的数目，资料库中单词的总数，以及包含特殊词的文件(行)数目。

对于自然语言全文搜索，要求MATCH() 函数中命名的列和你的表中一些FULLTEXT索引中包含的列相同。对于前述问讯，注意，MATCH()函数(题目及全文)中所命名的列和文章表的FULLTEXT索引中的列相同。若要分别搜索题目和全文，应该对每个列创建FULLTEXT索引。

或者也可以运行布尔搜索或使用查询扩展进行搜索。关于这些搜索类型的说明见12.7.1节，“布尔全文搜索”和12.7.2节，“全文搜索带查询扩展”。

上面的例子基本上展示了怎样使用返回行的相关性顺序渐弱的MATCH()函数。而下面的例子则展示了怎样明确地检索相关值。返回行的顺序是不定的，原因是 SELECT 语句不包含 WHERE或ORDER BY 子句：

mysql> SELECT id, MATCH (title,body) AGAINST ('Tutorial')
-> FROM articles;
+----+-----------------------------------------+
| id | MATCH (title,body) AGAINST ('Tutorial') |
+----+-----------------------------------------+
| 1 | 0.65545833110809 |
| 2 | 0 |
| 3 | 0.66266459226608 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
+----+-----------------------------------------+
6 rows in set (0.00 sec)
下面的例子则更加复杂。询问返回相关值，同时对行按照相关性渐弱的顺序进行排序。为实现这个结果，你应该两次指定 MATCH()：一次在 SELECT 列表中而另一次在 WHERE子句中。这不会引起额外的内务操作，原因是MySQL 优化程序注意到两个MATCH()调用是相同的，从而只会激活一次全文搜索代码。

mysql> SELECT id, body, MATCH (title,body) AGAINST
-> ('Security implications of running MySQL as root') AS score
-> FROM articles WHERE MATCH (title,body) AGAINST
-> ('Security implications of running MySQL as root');
+----+-------------------------------------+-----------------+
| id | body | score |
+----+-------------------------------------+-----------------+
| 4 | 1. Never run mysqld as root. 2. ... | 1.5219271183014 |
| 6 | When configured properly, MySQL ... | 1.3114095926285 |
+----+-------------------------------------+-----------------+
2 rows in set (0.00 sec)
表中有2行(0.00 秒)

MySQL FULLTEXT 执行将任何单字字符原形 (字母、数字和下划线部分)的序列视为一个单词。这个序列或许也包含单引号 ('),但在一行中不会超过一个。这意味着 aaa'bbb 会被视为一个单词，而 aaa''bbb则被视为2个单词。位于单词之前或其后
[/Quote]

ACMAIN_CHM 2011-10-31

打赏
举报

[Quote]还有点问题，为了搜出尽量多的记录，我用的是 name like "%key%"
看到一些文档说这个不能使用索引？

对，这种形式%key% 不能使用索引[/Quote]无法使用常规索引。这是由索引的性质决定的。
如果这种需求，则应该使用全文索引。

comey 2011-10-31

打赏
举报

[Quote=引用 10 楼 acmain_chm 的回复:]
引用直接gb2312_chinese_ci，中文不能按拼音排序
下面例子，并没有汉字排序的顺序啊。当然GB2312本身并不是严格按拼音排序的。理论上说是按照GB2312的编码顺序来排序，而这个编码顺序的前面常用汉字除多音字外是按拼音排的。

SQL code
mysql> show full columns from x;
+-------+-------------+-----……
[/Quote]

gb2312_chinese_ci中文不能排序问题，可能跟系统有关。
我用的是linux。
用php　select出来的结果不能排序
但用mysql客户端select出的结果是可以排序

WWWWA 2011-10-31

打赏
举报

还有点问题，为了搜出尽量多的记录，我用的是 name like "%key%"
看到一些文档说这个不能使用索引？

对，这种形式%key% 不能使用索引

comey 2011-10-31

打赏
举报

[Quote=引用 11 楼 acmain_chm 的回复:]
引用引用 4 楼 wwwwb 的回复:
select * from tt where upper(f1)='XX' or lower(f1)='xx'
or
WHERE a.`subject` COLLATE gb2312_chinese_ci='xx'

这两个效率哪个好？

这个直接的字符集转换明显会比UPPER函数要快。但这个建议还是直接把你的字段设置为gb2312_ch……
[/Quote]

谢谢两位大牛，我听你们的使用gb2312_chinese_ci。

还有点问题，为了搜出尽量多的记录，我用的是 name like "%key%"
看到一些文档说这个不能使用索引？