MySQL is sensitive to capitalization some summary

Source: Internet
Author: User
Tags lowercase

This way MySQL will automatically convert all table names to lowercase characters when created and found (this option defaults to 1 in Windows and 0 in Unix). Starting with MySQL 4.0.2, this option also applies to database names.

When you change this option, you must first convert the old table name to lowercase before starting mysqld.

In other words, if you want to keep the uppercase and lowercase character state when you create the table in the database, you should put this parameter in the 0:lower_case_table_names=1.

Otherwise, you'll find that the same sqldump scripts are eventually imported differently under different operating systems (all uppercase characters in Windows are lowercase).

Attention:

On Win32, although database and table names are ignored in MySQL capitalization, you should not use different capitalization in the same query to refer to a given database and table. The following query will not work because it references a table as a my_table and as a my_table:

The code is as follows Copy Code

1.mysql> SELECT * from my_table WHERE my_table.col=1;

2, Column name

Column names are ignored in all cases.

3, the table alias

The alias of the table is case-sensitive. The following query will not work: because it refers to aliases with A and a:

The code is as follows Copy Code

1.mysql> SELECT col_name from Tbl_name as a

2.WHERE a.col_name = 1 OR a.col_name = 2;

4, the alias of the column

The alias of the column is case-insensitive.

5, string comparison and pattern matching

By default, MySQL searches are case insensitive (although some character sets have never ignored MySQL capitalization, such as Czech). This means that if you search with col_name like ' a% ', you'll get all the column values starting with a or a. If you want to make this search case-sensitive, use like index (Col_name, "a") =0 check a prefix. Or if the column value must be exactly "a", use strcmp (Col_name, "a") = 0.

Simple comparison operations (>=, >, =, <, <=, sorting, and aggregation) are based on the "sorted value" of each character. Characters that have the same sort value (like e,e) are treated as the same characters!

Like comparisons are performed on the uppercase value of each character ("e" = "E").

If you want a column to always be used as a MySQL case sensitive way, declare it as binary.

For example:

The code is as follows Copy Code


1.mysql> Select "E" = "E", "E" =binary "E";
2.+---------+----------------+| "E" = "E" | "E" =binary "E"
|+---------+----------------+| 1 | 0 |+---------+----------------+

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.