MySQL query case-insensitive solution (two types)

When we enter data regardless of the case, for example: Enter AAA or AAA, AAA can query the same results, indicating that the query condition is not sensitive to case.

Solution One:

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

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

*_bin: Indicates a binary case sensitive collation, which means
*_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. You can modify the field's collation to binary

Like what:

ALTER TABLE TABLENAME MODIFY COLUMN COLUMNNAME VARCHAR () 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 differentiate, we can do it as follows:

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

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

The second method:

To be identified when the table is under construction

CREATE TABLE some_table (   str char () binary)

Principle:

For char, varchar, and text types, the binary attribute can assign a collation to a column for that column's character set. The binary property is shorthand for the two-dollar collation of the specified column character set. Sorting and comparisons are based on numeric character values. So the case is also naturally differentiated.

Portal: http://www.jb51.net/article/70884.htm

MySQL query case-insensitive solution (two types)