MySQL Query case-insensitive solution (two kinds) _mssql

When we enter regardless of the case can be queried data, such as: Enter AAA or AAA, AAA can query the same results, stating that the query condition is not sensitive to case.

Solution One:

So the question of MySQL is suspected. Do an experiment: Use the client directly to query the database with SQL. The discovery is indeed of a size insensitive.

Collate(proofreading) needs to be set up by querying the data. COLLATE rules :

*_bin: Binary case sensitive collation, which means case-sensitive.
*_cs:case sensitive collation, case-sensitive
*_ci:case insensitive collation, case-insensitive

Workaround.

1. You can enclose the query criteria in binary (). Like what:

SELECT * from TableA where binary ColumnA = ' AAA ';

2. The collation that can modify the field is binary

Like what:

ALTER TABLE tablename MODIFY COLUMN COLUMNNAME VARCHAR (m) BINARY CHARACTER SET UTF8 COLLATE utf8_bin DEFAULT NULL;

Solution Two:

MySQL queries are case-insensitive by default, such as:

SELECT * from some_table where str= ' abc ';
SELECT * from some_table where str= ' ABC ';

The results are the same, and if we need to make a distinction, we can do the following:

The first method:
To make the MySQL query case-sensitive, you can:

SELECT * from some_table where binary str= ' abc '
SELECT * from some_table where binary str= ' abc '

Second method:

Be identified when the table is being built

CREATE TABLE some_table (
   str char () binary 
)

Principle:

For char, varchar, and text types, the binary property can assign a collation to the column character set for the column. The binary property is a shorthand for the two-yuan collation of the specified column character set. Sorting and comparing are based on numeric character values. So it's natural to distinguish the case.

The above two solutions to solve the problem of MySQL query is not case-sensitive, I hope to help you, more MySQL data query questions, please login to the site to learn.