数据库 MySQL select by default case insensitive

betterthornbird · 2014年05月14日 · 最后由 betterthornbird 回复于 2014年05月16日 · 3398 次阅读

今天才知道 加上 BINARY 才行

mysql> select user_id, user_name, company_id from users where BINARY user_name = 'cgrant' limit 8;

看 MySQL 官方文档 http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

以及 stackoverflow 的问题 http://stackoverflow.com/questions/7857669/mysql-case-sensitive-query

然后凌乱了 不知道怎么样才能写出 rails 里面支持 case sensitive 的查询了

想要固定字段大小写敏感的话只要把该字段的 collation 从 utf8_general_ci 改成 utf8_bin.

#2 楼 @tnt 谢谢。今天一直在看 charset 和 collation 是怎么回事。有些疑问。

从 Mysql 的文档创建 table http://dev.mysql.com/doc/refman/5.0/en/create-table.html里面有提及

column_definition: data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] [reference_definition]

data_type: BIT[(length)] .....这里略过很多行........... | CHAR[(length)] [CHARACTER SET charset_name] [COLLATE collation_name]

-> Question: 这行是指定一个 CHARACTER SET 或者指定一个 COLLATE? 语法上应该是比如 CHAR COLLATE=utf8_general_ci? 从 Mysql 的文档 http://dev.mysql.com/doc/refman/5.0/en/charset-charsets.html 来看 collation list 中 char set=utf8 default collation=utf8_general_ci 我不知道你说的 utf8_bin 是哪里来的?有没有参考?

| VARCHAR(length) [CHARACTER SET charset_name] [COLLATE collation_name] .....这里略过很多行...........

其实我只想解决一个问题,将一些 colomn 变成 case sensitive 然后我可以放心大胆的用 find_by_column_name 去把那些区分大小写的匹配内容找出来,而不会出错。

比如 find_by_user_name('LiLei') 不会把 user_name 是 LiLei 和 Lilei/lilei 的人都找出来 只要那个唯一的 LiLei.

#2 楼 @tnt 我的疑问都解决了

#4 楼 @betterthornbird 你需要的这种 binary 查找的效率会比正则高吗?不知道可不可以利用索引

#6 楼 @alsotang binary 查找是很快的

找到了另外个解决方案 就是把 varchar 变成 tinyblob

#8 楼 @betterthornbird 字符窜类型用 blob/tinyblob 这类有副作用, 比如 string 想返回 char_length, varchar/char 都是根据编码返回字数 (就是 ruby 里的.size), 但是 blob/tinyblob 返回的是字节长度 (就是 ruby 里的.bytesize)

#9 楼 @tnt 多谢提醒 还好这个副作用不影响我这边 我要改掉的字段是从别的地方 sync 过来的一些英文字符串信息 结果不会有什么区别

需要 登录 后方可回复, 如果你还没有账号请点击这里 注册