在Mysql中，使用select where查询区分大小写

Hi I am using Java front end and Mysql Backend,,,

你好，我正在使用Java前端和Mysql后端，

Actually in tbl_test contains

实际上tbl_test包含

name value
---------------
 abc   22   
 xyz   14   
 ABC   32   
 xyZ    4   
 ABc    4

In java I try to retrieve abc's value so written a code

在java中，我试图检索abc的值，因此编写了一个代码

ResultSet result=stmt.executeQuery("select value from tbl_test where name='abc'");
while(result.next())
{
     System.out.println("Answer : "+result.getInt(1));
}
result.close();

The current output is

当前的输出是

Answer :  22
Answer :  32
Answer :  4

Actually i want the result only 'abc' ie Answer : 22

实际上，我只想要“abc”的答案:22

I found result also, with below code

我也找到了结果，下面的代码

String name="abc";
ResultSet result=stmt.executeQuery("select name, value from tbl_test where name='"+name+"'");
while(result.next())
  {
    if(name.equals(result.getString(1))
       System.out.println("Answer : "+result.getInt(2));
  }
result.close();

Now i getting correct output, but this result from java code not in query,,, Is it possible to retrieve the same result in query...

现在我得到了正确的输出，但是这个来自java代码而不是查询的结果，有可能在查询中检索相同的结果吗……

Thank you

谢谢你！

2 个解决方案

#1

Use the MySQL Binary operator:

使用MySQL二进制操作符:

select value 
from tbl_test 
where CAST(name AS BINARY) ='abc';

SQL Fiddle Demo.

SQL小提琴演示。

#2

You have to use case sensitive search for example, or use Binary serach like Mahmoud said

例如，你必须使用区分大小写的搜索，或者像马哈茂德说的那样使用二进制serach

select value from tbl_test where name  COLLATE latin1_general_cs LIKE 'abc'

http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

#1