我有一个函数,返回5个混合大小写字符。如果我对这个字符串进行查询,不管大小写,它都会返回值。
如何使MySQL字符串查询区分大小写?
我有一个函数,返回5个混合大小写字符。如果我对这个字符串进行查询,不管大小写,它都会返回值。
如何使MySQL字符串查询区分大小写?
当前回答
不需要改变任何DB级别,只是你必须改变SQL查询它将工作。
的例子,
“SELECT * FROM <TABLE> where userId = ' ' ' + iv_userId + ' ' AND password = ' ' ' ' ' + iv_password + ' '";
二进制关键字将区分大小写。
其他回答
不需要改变任何DB级别,只是你必须改变SQL查询它将工作。
的例子,
“SELECT * FROM <TABLE> where userId = ' ' ' + iv_userId + ' ' AND password = ' ' ' ' ' + iv_password + ' '";
二进制关键字将区分大小写。
http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html
The default character set and collation are latin1 and latin1_swedish_ci, so nonbinary string comparisons are case insensitive by default. This means that if you search with col_name LIKE 'a%', you get all column values that start with A or a. To make this search case sensitive, make sure that one of the operands has a case sensitive or binary collation. For example, if you are comparing a column and a string that both have the latin1 character set, you can use the COLLATE operator to cause either operand to have the latin1_general_cs or latin1_bin collation:
col_name COLLATE latin1_general_cs LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_general_cs
col_name COLLATE latin1_bin LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_bin
如果希望始终以区分大小写的方式对待列,请使用区分大小写或二进制排序规则来声明它。
太好了!
我和你们分享一个比较密码的函数的代码:
SET pSignal =
(SELECT DECODE(r.usignal,'YOURSTRINGKEY') FROM rsw_uds r WHERE r.uname =
in_usdname AND r.uvige = 1);
SET pSuccess =(SELECT in_usdsignal LIKE BINARY pSignal);
IF pSuccess = 1 THEN
/*Your code if match*/
ELSE
/*Your code if don't match*/
END IF;
下面是MySQL版本等于或高于5.5的情况。
添加到/etc/mysql/my.cnf
[mysqld]
...
character-set-server=utf8
collation-server=utf8_bin
...
我尝试的所有其他排序似乎都不区分大小写,只有“utf8_bin”有效。
在这之后不要忘记重新启动mysql:
sudo service mysql restart
根据http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html,还有一个“latin1_bin”。
“utf8_general_cs”不被mysql启动接受。(我把“_cs”读成“区分大小写”- ??)
要在使用BINARY之前使用索引,如果有大型表,可以这样做。
SELECT
*
FROM
(SELECT * FROM `table` WHERE `column` = 'value') as firstresult
WHERE
BINARY `column` = 'value'
子查询将生成一个非常小的不区分大小写的子集,然后选择其中唯一区分大小写的匹配项。