Turn!! MySQL query condition is case insensitive

Source: Internet
Author: User
Tags mysql query

When the user login module, enter the user name (large/lowercase) and password, MySQL can find out. --MySQL queries are case-insensitive.

Transfer from http://blog.csdn.net/qishuo_java/article/details/40118937

Find data for users with user name ID ' aamkadexm2m5njq2lwuzyzctndfkmc1h ': SELECT * from usertable where id = ' aamkadexm2m5njq2lwuzyzctndfkmc1h ‘; Results showed two records. This is strange, the ID has been set as the primary key, how can it be repeated? is the MySQL loophole. Later found that the original query out the value of the two ID is different, cheat a look no difference, look carefully you will find that the two ID just have a letter of the case of different, these two IDs are:

' Aamkadexm2m5njq2lwuzyzctndfkmc1h ',

' aamkadexm2m5njq2lwuzyzctndfkmc1h '.

The original MySQL query, there is case-insensitive. Can be solved by binary keyword.

There are two ways to resolve this:

first: Make MySQL query case-sensitive

SELECT * from usertable where binary id = ' aamkadexm2m5njq2lwuzyzctndfkmc1h ';

second type: Identify when building a table

CREATE TABLE table_name {

ID varchar (+) binary;

}

In MySQL, there are also case problems:

(1) Keywords: case insensitive SELECT * FROM table_name and select * FROM table_name effect is the same

(2) identifiers (such as database names and table names): case-insensitive. If table uSers exists, the select * from Users and select * from users effect. Online says it's about the operating system, which is case-sensitive on all unit operating systems (except Mac OS that use hfs+) and is case-insensitive on Windows. (This statement on the Internet has not been verified, I am not case-sensitive on Windows server2003)
(3) Table aliases: Case-insensitive Select m.* from users m where m.username = ' AA ';
(4) Alias of column: Case-insensitive Select UName from (select Username as UName from users where id = 768) t

Turn!! MySQL query condition is case insensitive

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.