Detailed description of how MySQL queries Time Zone strings with uppercase and lowercase letters, mysql Case

Source: Internet
Author: User

Detailed description of how MySQL queries Time Zone strings with uppercase and lowercase letters, mysql Case

If you insert two rows of values 'A' and 'A' in A column with a unique constraint on mysql, Mysql considers it to be the same, but it does not exist in oracle. Is the default mysql field value case insensitive? This is a headache. Directly use the client to query the database using SQL. It is found that the size is not sensitive.
You need to set collate by querying the data ).
Collate rules:

  • * _ Bin: binary case sensitive collation, that is, case sensitive.
  • * _ Cs: case sensitive collation, case sensitive
  • * _ Ci: case insensitive collation, case insensitive

Mysql can:

1. Use character sets to store strings and support multiple character sets;
2. Use verification rules to compare strings. You can use multiple verification rules to compare the same character set;
3. Use different character sets or verification rules in the same server, database, or even table to mix character strings;
4. Different character sets and verification rules can be defined at any level (server, database, table, field, string.

Case Sensitive

You can use the binary keyword. There are two methods:
First: Make mysql query Time Zone case sensitive

select * from usertable where binary id='AAMkADExM2M5NjQ2LWUzYzctNDFkMC1h'; 

Type 2: Identify a table during table Creation

create table `usertable`(  `id` varchar(32) binary,  PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Or

CREATE TABLE `usertable` (  `id` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',  PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

In mysql, Case sensitivity issues exist:
(1) KEYWORDS: case-insensitive select * fRom table_name and select * from table_name
(2) identifier (such as database name and table name): case-insensitive. If the users table exists, select * from users and select * from uSers have the same effect. This is related to the operating system. All Unit operating systems (except for Mac OS using HFS +) are case sensitive, while windows systems are case insensitive. (This statement has not been verified on the Internet. I am case insensitive on windows server2003)
(3) table alias: case-insensitive select m. * from users m where M. username = 'a ';
(4) column alias: case-insensitive select uName from (select username as uname from users where id = 768 );

Related Article

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.