How to make Case-Sensitive Search in MySQL for a Particular Field / Column?

more images

Databases in MySQL are known to correspond to directories within the data directory. Each table within a database relate to at least one file within the database directory (and possibly more, depending on the storage engine). Standing on these grounds the fact remains that, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database and table names. What comes in as an interesting yet confusing piece of information is that database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix. Mac OS X enjoys being the one notable exception. Mac OS X is Unix-based but uses a default file system type (HFS+) that is not case sensitive. However, Mac OS X also supports UFS volumes, which are case sensitive just as on any Unix.

Now, the issue of case-sensitivity arises mostly for authentication purposes. A case-sensitive approach delivers with additional security. But, unfortunately, Mysql columns mostly are case insensitive (contrary to Oracle) for search operations. Nevertheless, you should not refer to a given database or table using different cases within the same statement. The following statement would not work because it refers to a table both as my_table and as MY_TABLE.

Eg: mysql> SELECT * FROM user_avik WHERE USER_AVIK.col=1;

We are known to the fact that there are several solutions to a problem, but one needs to stick to the one that incurs least cost (in terms of time and effort). We have listed a number of approaches which you can try to make case-sensitive search in MySQL for a particular field / column.

One of the approaches can be to directly specify binary while creating the table and change the default behavior.

Eg:

CREATE TABLE USERS
(
USER_ID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
USER_NAME VARCHAR(50) BINARY NOT NULL
)

If you’re having a problems then you can check out some information on How to CREATE a Case-Sensitive Table in MySQL.

If it happens that the DB already exists and you want to add the “Binary” to an existing column then you may try this first, before moving onto the ALTER approach.

Eg.

SELECT BINARY * FROM USERS where USER_ID = 7 AND USER_NAME = ‘avik’ LIMIT 1;

If you think it would be better for you to go for Table Alteration then use this query,

Eg.

ALTER TABLE USERS MODIFY
USER_NAME VARCHAR(50)
CHARACTER SET latin1
COLLATE latin1_bin;

or

ALTER TABLE USERS CHANGE USER_NAME USER_NAME VARCHAR(50) BINARY NOT NULL;

There is yet another solution to the problem and this one is by far the best one yet.

Eg.

ALTER TABLE user_table MODIFY user_column BLOB;

ALTER TABLE user_table MODIFY user_column VARCHAR(50) BINARY;

ALTER TABLE `User` CHANGE `password` `password` VARCHAR( 30 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL

The first line converts to a binary data type (attempt to minimize character loss) and the second converts back to the VARCHAR type with BINARY collation.

If you need more explanation then check out How to ALTER a Table to make it Case-Sensitive in MySQL.

It may actually be preferable to store as one of the binary types (BLOB, BINARY, or VARBINARY) rather than simply collating BINARY. I would suggest you compare a bit, your mileage may vary depending on your actual data and the queries you need to run.

But you should keep a few things in mind, specifying the CHARACTER SET binary attribute for a character data type causes the column to be created as the corresponding binary data type: CHAR becomes BINARY, VARCHAR becomes VARBINARY, and TEXT becomes BLOB. For the ENUM and SET data types, this does not occur; they are created as declared. Suppose that you specify a table using this definition:

CREATE TABLE t
(
c1 VARCHAR(10) CHARACTER SET binary,
c2 TEXT CHARACTER SET binary,
c3 ENUM(’a',’b',’c') CHARACTER SET binary
);

The resulting table has this definition:

CREATE TABLE t
(
c1 VARBINARY(10),
c2 BLOB,
c3 ENUM(’a',’b',’c') CHARACTER SET binary
);

To see whether MySQL used a data type other than the one you specified, issue a DESCRIBE or SHOW CREATE TABLE statement after creating or altering the table.

For nonbinary strings (CHAR, VARCHAR, TEXT), string searches use the collation of the comparison operands. For binary strings (BINARY, VARBINARY, BLOB), comparisons use the numeric values of the bytes in the operands; this means that for alphabetic characters, comparisons will be case sensitive.

A comparison between a nonbinary string and binary string is treated as a comparison of binary strings.

Simple comparison operations (>=, >, =, <, <=, sorting, and grouping) are based on each character’s “sort value.” Characters with the same sort value are treated as the same character. For example, if “e” and “” have the same sort value in a given collation, they compare as equal.

The default character set and collation are latin1 and latin1_swedish_ci, so nonbinary string comparisons are case insensitive by default. This means that if you search with col_name LIKE ‘a%’, you get all column values that start with A or a. To make this search case sensitive, make sure that one of the operands has a case sensitive or binary collation. For example, if you are comparing a column and a string that both have the latin1 character set, you can use the COLLATE operator to cause either operand to have the latin1_general_cs or latin1_bin collation:

col_name COLLATE latin1_general_cs LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_general_cs
col_name COLLATE latin1_bin LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_bin

In-case you want a column always to be treated in case-sensitive fashion, declare it with a case sensitive or binary collation. If you want explanation on collation then take a look at How to to Set the Collation for the Table (or column) to be either Binary or Case-Sensitive.

So, we are done here, for now. If you find more information on case-sensitive search in MySQL for a particular field / column do remember to share with us.