MySQL queries are case-sensitive

Source: Internet
Author: User

1. One way is to set the collation of a table or row so that it is binary or case sensitive. In MySQL, the naming method for column collate's conventions is as follows:

*_bin: Indicates a binary case sensitive collation, which means
*_cs:case sensitive collation, case-sensitive
*_ci:case insensitive collation, case insensitive




2. Another method




3. Another way is to specify collation at query time

mysql> CREATE TABLE case_test (Word VARCHAR) CHARACTER SET latin1; Query OK, 0 rows affected (0.01 sec) mysql> INSERT into Case_test VALUES (' Frank '), (' Google '), (' Froogle '), (' Flickr '), (' FlicKr '); Query OK, 7 rows affected (0.01 sec) records:7 duplicates:0 warnings:0 mysql> SELECT * from case_test WHERE word LIK E ' f% '; +---------+ | Word | +---------+ | Frank | | Froogle | | Flickr | | FlicKr | +---------+ 6 rows in Set (0.01 sec) mysql> SELECT * from case_test WHERE word like ' f% '; +---------+ | Word | +---------+ | Frank | | Froogle | | Flickr | | FlicKr | +---------+ 6 rows in Set (0.01 sec) mysql> SELECT * from case_test WHERE word COLLATE latin1_bin like ' f% '; +---------+ | Word | +---------+ | Frank | | FlicKr | +---------+ 4 rows in Set (0.05 sec) mysql> SELECT * from case_test WHERE word COLLATE latin1_bin like ' f% '; +---------+ | Word | +---------+ | Froogle | | Flickr | +---------+ 2 rows in Set (0.00 sec) 
Mysql> SELECT * from case_test WHERE word like ' f% ' COLLATE latin1_general_cs; +---------+ | Word | +---------+ | Frank | | FlicKr | +---------+ 4 rows in Set (0.04 sec)

To make MySQL queries case-sensitive, you can:

    1. SELECT * FROM table_name where binary a like ' a% '
    2. SELECT * FROM table_name where binary a like ' A% '

Note: This query is very slow and does not recommend using

can also be identified when the table is built

Create table table_name (

A varchar (a) binary

)

MySQL queries are case-sensitive

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.