`
ma3310
  • 浏览: 16038 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

MySQL 检索时如何不忽略大小写

MySQL 
阅读更多
MySQL 中对默认创建的表进行模糊匹配查询时，是忽略大小写的。原因是 MySQL 比较两个值时会参考与预定义的字符集相匹配的比较规则（collation）。 
mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                    | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |
| cp1250   | Windows Central European    | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                 | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |
| macce    | Mac Central European        | macce_general_ci    |      1 |
| macroman | Mac West European           | macroman_general_ci |      1 |
| cp852    | DOS Central European        | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |
| cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |
| binary   | Binary pseudo charset       | binary              |      1 |
| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |
+----------+-----------------------------+---------------------+--------+
36 rows in set (0.00 sec)

比较规则名字中的后缀 _ci 表示 case-insensitive，代表大小写不敏感的比较规则。可以在建表时，通过 collate 子句指定大小写敏感的比较规则，以让 MySQL 在比较的时候区分大小写。 
CREATE TABLE employee (
       name VARCHAR(20) CHARACTER SET utf8 
                        COLLATE       utf8_bin
);

MySQL 中区分大小写的比较规则。 _cs 表示 case-sensitive；_bin 代表 binary。 
mysql> SHOW COLLATION LIKE '%_cs';
+--------------------+---------+----+---------+----------+---------+
| Collation          | Charset | Id | Default | Compiled | Sortlen |
+--------------------+---------+----+---------+----------+---------+
| latin1_general_cs  | latin1  | 49 |         | Yes      |       1 |
| latin2_czech_cs    | latin2  |  2 |         | Yes      |       4 |
| cp1250_czech_cs    | cp1250  | 34 |         | Yes      |       2 |
| latin7_estonian_cs | latin7  | 20 |         | Yes      |       1 |
| latin7_general_cs  | latin7  | 42 |         | Yes      |       1 |
| cp1251_general_cs  | cp1251  | 52 |         | Yes      |       1 |
+--------------------+---------+----+---------+----------+---------+
6 rows in set (0.00 sec)

mysql> SHOW COLLATION LIKE '%_bin';
+--------------+----------+----+---------+----------+---------+
| Collation    | Charset  | Id | Default | Compiled | Sortlen |
+--------------+----------+----+---------+----------+---------+
| big5_bin     | big5     | 84 |         | Yes      |       1 |
| dec8_bin     | dec8     | 69 |         | Yes      |       1 |
| cp850_bin    | cp850    | 80 |         | Yes      |       1 |
| hp8_bin      | hp8      | 72 |         | Yes      |       1 |
| koi8r_bin    | koi8r    | 74 |         | Yes      |       1 |
| latin1_bin   | latin1   | 47 |         | Yes      |       1 |
| latin2_bin   | latin2   | 77 |         | Yes      |       1 |
| swe7_bin     | swe7     | 82 |         | Yes      |       1 |
| ascii_bin    | ascii    | 65 |         | Yes      |       1 |
| ujis_bin     | ujis     | 91 |         | Yes      |       1 |
| sjis_bin     | sjis     | 88 |         | Yes      |       1 |
| hebrew_bin   | hebrew   | 71 |         | Yes      |       1 |
| tis620_bin   | tis620   | 89 |         | Yes      |       1 |
| euckr_bin    | euckr    | 85 |         | Yes      |       1 |
| koi8u_bin    | koi8u    | 75 |         | Yes      |       1 |
| gb2312_bin   | gb2312   | 86 |         | Yes      |       1 |
| greek_bin    | greek    | 70 |         | Yes      |       1 |
| cp1250_bin   | cp1250   | 66 |         | Yes      |       1 |
| gbk_bin      | gbk      | 87 |         | Yes      |       1 |
| latin5_bin   | latin5   | 78 |         | Yes      |       1 |
| armscii8_bin | armscii8 | 64 |         | Yes      |       1 |
| utf8_bin     | utf8     | 83 |         | Yes      |       1 |
| ucs2_bin     | ucs2     | 90 |         | Yes      |       1 |
| cp866_bin    | cp866    | 68 |         | Yes      |       1 |
| keybcs2_bin  | keybcs2  | 73 |         | Yes      |       1 |
| macce_bin    | macce    | 43 |         | Yes      |       1 |
| macroman_bin | macroman | 53 |         | Yes      |       1 |
| cp852_bin    | cp852    | 81 |         | Yes      |       1 |
| latin7_bin   | latin7   | 79 |         | Yes      |       1 |
| cp1251_bin   | cp1251   | 50 |         | Yes      |       1 |
| cp1256_bin   | cp1256   | 67 |         | Yes      |       1 |
| cp1257_bin   | cp1257   | 58 |         | Yes      |       1 |
| geostd8_bin  | geostd8  | 93 |         | Yes      |       1 |
| cp932_bin    | cp932    | 96 |         | Yes      |       1 |
| eucjpms_bin  | eucjpms  | 98 |         | Yes      |       1 |
+--------------+----------+----+---------+----------+---------+
35 rows in set (0.00 sec)

MySQL 中所有支持的比较规则。 
mysql> SHOW COLLATION;
+----------------------+----------+-----+---------+----------+---------+
| Collation            | Charset  | Id  | Default | Compiled | Sortlen |
+----------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci      | big5     |   1 | Yes     | Yes      |       1 |
| big5_bin             | big5     |  84 |         | Yes      |       1 |
| dec8_swedish_ci      | dec8     |   3 | Yes     | Yes      |       1 |
| dec8_bin             | dec8     |  69 |         | Yes      |       1 |
| cp850_general_ci     | cp850    |   4 | Yes     | Yes      |       1 |
| cp850_bin            | cp850    |  80 |         | Yes      |       1 |
| hp8_english_ci       | hp8      |   6 | Yes     | Yes      |       1 |
| hp8_bin              | hp8      |  72 |         | Yes      |       1 |
| koi8r_general_ci     | koi8r    |   7 | Yes     | Yes      |       1 |
| koi8r_bin            | koi8r    |  74 |         | Yes      |       1 |
| latin1_german1_ci    | latin1   |   5 |         | Yes      |       1 |
| latin1_swedish_ci    | latin1   |   8 | Yes     | Yes      |       1 |
| latin1_danish_ci     | latin1   |  15 |         | Yes      |       1 |
| latin1_german2_ci    | latin1   |  31 |         | Yes      |       2 |
| latin1_bin           | latin1   |  47 |         | Yes      |       1 |
| latin1_general_ci    | latin1   |  48 |         | Yes      |       1 |
| latin1_general_cs    | latin1   |  49 |         | Yes      |       1 |
| latin1_spanish_ci    | latin1   |  94 |         | Yes      |       1 |
| latin2_czech_cs      | latin2   |   2 |         | Yes      |       4 |
| latin2_general_ci    | latin2   |   9 | Yes     | Yes      |       1 |
| latin2_hungarian_ci  | latin2   |  21 |         | Yes      |       1 |
| latin2_croatian_ci   | latin2   |  27 |         | Yes      |       1 |
| latin2_bin           | latin2   |  77 |         | Yes      |       1 |
| swe7_swedish_ci      | swe7     |  10 | Yes     | Yes      |       1 |
| swe7_bin             | swe7     |  82 |         | Yes      |       1 |
| ascii_general_ci     | ascii    |  11 | Yes     | Yes      |       1 |
| ascii_bin            | ascii    |  65 |         | Yes      |       1 |
| ujis_japanese_ci     | ujis     |  12 | Yes     | Yes      |       1 |
| ujis_bin             | ujis     |  91 |         | Yes      |       1 |
| sjis_japanese_ci     | sjis     |  13 | Yes     | Yes      |       1 |
| sjis_bin             | sjis     |  88 |         | Yes      |       1 |
| hebrew_general_ci    | hebrew   |  16 | Yes     | Yes      |       1 |
| hebrew_bin           | hebrew   |  71 |         | Yes      |       1 |
| tis620_thai_ci       | tis620   |  18 | Yes     | Yes      |       4 |
| tis620_bin           | tis620   |  89 |         | Yes      |       1 |
| euckr_korean_ci      | euckr    |  19 | Yes     | Yes      |       1 |
| euckr_bin            | euckr    |  85 |         | Yes      |       1 |
| koi8u_general_ci     | koi8u    |  22 | Yes     | Yes      |       1 |
| koi8u_bin            | koi8u    |  75 |         | Yes      |       1 |
| gb2312_chinese_ci    | gb2312   |  24 | Yes     | Yes      |       1 |
| gb2312_bin           | gb2312   |  86 |         | Yes      |       1 |
| greek_general_ci     | greek    |  25 | Yes     | Yes      |       1 |
| greek_bin            | greek    |  70 |         | Yes      |       1 |
| cp1250_general_ci    | cp1250   |  26 | Yes     | Yes      |       1 |
| cp1250_czech_cs      | cp1250   |  34 |         | Yes      |       2 |
| cp1250_croatian_ci   | cp1250   |  44 |         | Yes      |       1 |
| cp1250_bin           | cp1250   |  66 |         | Yes      |       1 |
| cp1250_polish_ci     | cp1250   |  99 |         | Yes      |       1 |
| gbk_chinese_ci       | gbk      |  28 | Yes     | Yes      |       1 |
| gbk_bin              | gbk      |  87 |         | Yes      |       1 |
| latin5_turkish_ci    | latin5   |  30 | Yes     | Yes      |       1 |
| latin5_bin           | latin5   |  78 |         | Yes      |       1 |
| armscii8_general_ci  | armscii8 |  32 | Yes     | Yes      |       1 |
| armscii8_bin         | armscii8 |  64 |         | Yes      |       1 |
| utf8_general_ci      | utf8     |  33 | Yes     | Yes      |       1 |
| utf8_bin             | utf8     |  83 |         | Yes      |       1 |
| utf8_unicode_ci      | utf8     | 192 |         | Yes      |       8 |
| utf8_icelandic_ci    | utf8     | 193 |         | Yes      |       8 |
| utf8_latvian_ci      | utf8     | 194 |         | Yes      |       8 |
| utf8_romanian_ci     | utf8     | 195 |         | Yes      |       8 |
| utf8_slovenian_ci    | utf8     | 196 |         | Yes      |       8 |
| utf8_polish_ci       | utf8     | 197 |         | Yes      |       8 |
| utf8_estonian_ci     | utf8     | 198 |         | Yes      |       8 |
| utf8_spanish_ci      | utf8     | 199 |         | Yes      |       8 |
| utf8_swedish_ci      | utf8     | 200 |         | Yes      |       8 |
| utf8_turkish_ci      | utf8     | 201 |         | Yes      |       8 |
| utf8_czech_ci        | utf8     | 202 |         | Yes      |       8 |
| utf8_danish_ci       | utf8     | 203 |         | Yes      |       8 |
| utf8_lithuanian_ci   | utf8     | 204 |         | Yes      |       8 |
| utf8_slovak_ci       | utf8     | 205 |         | Yes      |       8 |
| utf8_spanish2_ci     | utf8     | 206 |         | Yes      |       8 |
| utf8_roman_ci        | utf8     | 207 |         | Yes      |       8 |
| utf8_persian_ci      | utf8     | 208 |         | Yes      |       8 |
| utf8_esperanto_ci    | utf8     | 209 |         | Yes      |       8 |
| utf8_hungarian_ci    | utf8     | 210 |         | Yes      |       8 |
| ucs2_general_ci      | ucs2     |  35 | Yes     | Yes      |       1 |
| ucs2_bin             | ucs2     |  90 |         | Yes      |       1 |
| ucs2_unicode_ci      | ucs2     | 128 |         | Yes      |       8 |
| ucs2_icelandic_ci    | ucs2     | 129 |         | Yes      |       8 |
| ucs2_latvian_ci      | ucs2     | 130 |         | Yes      |       8 |
| ucs2_romanian_ci     | ucs2     | 131 |         | Yes      |       8 |
| ucs2_slovenian_ci    | ucs2     | 132 |         | Yes      |       8 |
| ucs2_polish_ci       | ucs2     | 133 |         | Yes      |       8 |
| ucs2_estonian_ci     | ucs2     | 134 |         | Yes      |       8 |
| ucs2_spanish_ci      | ucs2     | 135 |         | Yes      |       8 |
| ucs2_swedish_ci      | ucs2     | 136 |         | Yes      |       8 |
| ucs2_turkish_ci      | ucs2     | 137 |         | Yes      |       8 |
| ucs2_czech_ci        | ucs2     | 138 |         | Yes      |       8 |
| ucs2_danish_ci       | ucs2     | 139 |         | Yes      |       8 |
| ucs2_lithuanian_ci   | ucs2     | 140 |         | Yes      |       8 |
| ucs2_slovak_ci       | ucs2     | 141 |         | Yes      |       8 |
| ucs2_spanish2_ci     | ucs2     | 142 |         | Yes      |       8 |
| ucs2_roman_ci        | ucs2     | 143 |         | Yes      |       8 |
| ucs2_persian_ci      | ucs2     | 144 |         | Yes      |       8 |
| ucs2_esperanto_ci    | ucs2     | 145 |         | Yes      |       8 |
| ucs2_hungarian_ci    | ucs2     | 146 |         | Yes      |       8 |
| cp866_general_ci     | cp866    |  36 | Yes     | Yes      |       1 |
| cp866_bin            | cp866    |  68 |         | Yes      |       1 |
| keybcs2_general_ci   | keybcs2  |  37 | Yes     | Yes      |       1 |
| keybcs2_bin          | keybcs2  |  73 |         | Yes      |       1 |
| macce_general_ci     | macce    |  38 | Yes     | Yes      |       1 |
| macce_bin            | macce    |  43 |         | Yes      |       1 |
| macroman_general_ci  | macroman |  39 | Yes     | Yes      |       1 |
| macroman_bin         | macroman |  53 |         | Yes      |       1 |
| cp852_general_ci     | cp852    |  40 | Yes     | Yes      |       1 |
| cp852_bin            | cp852    |  81 |         | Yes      |       1 |
| latin7_estonian_cs   | latin7   |  20 |         | Yes      |       1 |
| latin7_general_ci    | latin7   |  41 | Yes     | Yes      |       1 |
| latin7_general_cs    | latin7   |  42 |         | Yes      |       1 |
| latin7_bin           | latin7   |  79 |         | Yes      |       1 |
| cp1251_bulgarian_ci  | cp1251   |  14 |         | Yes      |       1 |
| cp1251_ukrainian_ci  | cp1251   |  23 |         | Yes      |       1 |
| cp1251_bin           | cp1251   |  50 |         | Yes      |       1 |
| cp1251_general_ci    | cp1251   |  51 | Yes     | Yes      |       1 |
| cp1251_general_cs    | cp1251   |  52 |         | Yes      |       1 |
| cp1256_general_ci    | cp1256   |  57 | Yes     | Yes      |       1 |
| cp1256_bin           | cp1256   |  67 |         | Yes      |       1 |
| cp1257_lithuanian_ci | cp1257   |  29 |         | Yes      |       1 |
| cp1257_bin           | cp1257   |  58 |         | Yes      |       1 |
| cp1257_general_ci    | cp1257   |  59 | Yes     | Yes      |       1 |
| binary               | binary   |  63 | Yes     | Yes      |       1 |
| geostd8_general_ci   | geostd8  |  92 | Yes     | Yes      |       1 |
| geostd8_bin          | geostd8  |  93 |         | Yes      |       1 |
| cp932_japanese_ci    | cp932    |  95 | Yes     | Yes      |       1 |
| cp932_bin            | cp932    |  96 |         | Yes      |       1 |
| eucjpms_japanese_ci  | eucjpms  |  97 | Yes     | Yes      |       1 |
| eucjpms_bin          | eucjpms  |  98 |         | Yes      |       1 |
+----------------------+----------+-----+---------+----------+---------+
127 rows in set (0.00 sec)


1
顶
0
踩
分享到：
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    MySQL 通配符学习小结

    * 在 MySQL 中，SQL 模式缺省是忽略大小写的。 * 在使用 SQL 模式时，不能使用“=”或“!=”操作符，而是使用“LIKE”或“NOT LIKE”比较操作符。 * MySQL 通配符可以用来匹配字符串的开头、结尾或中间部分。 下面...

    MySQL 5.1参考手册

    9.2.2. 识别符大小写敏感性 9.3. 用户变量 9.4. 系统变量 9.4.1. 结构式系统变量 9.5. 注释语法 9.6. MySQL中保留字的处理 10. 字符集支持 10.1. 常规字符集和校对 10.2. MySQL中的字符集和校对 10.3. 确定默认字符...

    MySQL 5.1中文手冊

    9.2.2. 识别符大小写敏感性 9.3. 用户变量 9.4. 系统变量 9.4.1. 结构式系统变量 9.5. 注释语法 9.6. MySQL中保留字的处理 10. 字符集支持 10.1. 常规字符集和校对 10.2. MySQL中的字符集和校对 10.3. 确定默认字符...

    MySQL 5.1参考手册中文版

    9.2.2. 识别符大小写敏感性 9.3. 用户变量 9.4. 系统变量 9.4.1. 结构式系统变量 9.5. 注释语法 9.6. MySQL中保留字的处理 10. 字符集支持 10.1. 常规字符集和校对 10.2. MySQL中的字符集和校对 10.3. 确定...

    MySQL 5.1官方简体中文参考手册

    9.2.2. 识别符大小写敏感性 9.3. 用户变量 9.4. 系统变量 9.4.1. 结构式系统变量 9.5. 注释语法 9.6. MySQL中保留字的处理 10. 字符集支持 10.1. 常规字符集和校对 10.2. MySQL中的字符集和校对 10.3. 确定默认字符...

    MySQL 5.1参考手册 （中文版）

    9.2.2. 识别符大小写敏感性 9.3. 用户变量 9.4. 系统变量 9.4.1. 结构式系统变量 9.5. 注释语法 9.6. MySQL中保留字的处理 10. 字符集支持 10.1. 常规字符集和校对 10.2. MySQL中的字符集和校对 10.3. 确定默认字符...

    mysql5.1中文手册

    识别符大小写敏感性 9.3. 用户变量 9.4. 系统变量 9.4.1. 结构式系统变量 9.5. 注释语法 9.6. MySQL中保留字的处理 10. 字符集支持 10.1. 常规字符集和校对 10.2. MySQL中的字符集和校对...

    MYSQL中文手册

    9.2.2. 识别符大小写敏感性 9.3. 用户变量 9.4. 系统变量 9.4.1. 结构式系统变量 9.5. 注释语法 9.6. MySQL中保留字的处理 10. 字符集支持 10.1. 常规字符集和校对 10.2. MySQL中的字符集和校对 10.3. 确定...

    mysql官方中文参考手册

    9.2.2. 识别符大小写敏感性 9.3. 用户变量 9.4. 系统变量 9.4.1. 结构式系统变量 9.5. 注释语法 9.6. MySQL中保留字的处理 10. 字符集支持 10.1. 常规字符集和校对 10.2. MySQL中的字符集和校对 10.3. 确定默认字符...

    mysql网络数据库指南(中文版) part1

    3.2.2 SQL语句中的大小写规则 101 3.3 创建、删除和选择数据库 101 3.4 创建、删除、索引和更改表 102 3.4.1 CREATE TABLE语句 102 3.4.2 DROP TABLE 语句 106 3.4.3 创建和删除索引 106 3.4.4 ALTER TABLE...

    MySQL5.1参考手册官方简体中文版

    9.2.2. 识别符大小写敏感性 9.3. 用户变量 9.4. 系统变量 9.4.1. 结构式系统变量 9.5. 注释语法 9.6. MySQL中保留字的处理 10. 字符集支持 10.1. 常规字符集和校对 10.2. MySQL中的字符集和校对 10.3. 确定默认字符...

    MYSQL网络数据库PDF学习资源

    3.2.2 SQL语句中的大小写规则 101 3.3 创建、删除和选择数据库 101 3.4 创建、删除、索引和更改表 102 3.4.1 CREATE TABLE语句 102 3.4.2 DROP TABLE 语句 106 3.4.3 创建和删除索引 106 3.4.4 ALTER TABLE语句 109 ...

    MYSQL常用命令大全

    在进行开发和实际应用中，用户不应该只用root用户进行连接数据库，虽然使用root用户进行测试时很方便，但会给系统带来重大安全隐患，也不利于管理技术的提高。我们给一个应用中使用的用户赋予最恰当的数据库权限。如...

    MySQL命令大全

    在进行开发和实际应用中，用户不应该只用root用户进行连接数据库，虽然使用root用户进行测试时很方便，但会给系统带来重大安全隐患，也不利于管理技术的提高。我们给一个应用中使用的用户赋予最恰当的数据库权限。如...

    2009达内SQL学习笔记

    大小写不敏感，即不区分大小写。提倡关键字大写，便于阅读和调式。 “！”在SQL环境下执行Unix命令。 SQL语句是由简单的英语单词构成；这些英语单词称为关键字/保留字，不做它用。SQL由多个关键字构成。 SQL语句...

    DBTSearch软件开发包

    * DBTSearch支持精确匹配以及模糊匹配，默认为精确匹配，忽略字母大小写进行模糊匹配； * DBTSearch提供Windows,Linux的C和JNI接口； * DBTSearch实现的是多线程搜索服务； * DBTSearch每秒可索引3000条记录...

Magicbox
Global site tag (gtag.js) - Google Analytics