Case sensitive database - how to fetch, update, or delete data if case-insensitive _ci encoding is selected

02.07.21 IT / Database 3128

Selecting data from the database (DB) is a normal part of the work of any modern application. In most cases, it is sufficient to retrieve data in a case insensitive manner, and this is how most databases work, which can improve performance.

However, what to do in cases when it is necessary to case sensitive selection of data from the database ? This problem is relevant for non-binary strings (VARCHAR, TEXT, CHAR, etc.), while data in binary form is case-sensitive (BLOB, BINARY and other types).

There are several ways to solve this problem:

- use the BINARY command for binary data processing. In this case, the data will be processed in binary form. Example query: SELECT * FROM `table` WHERE BINARY` column` = 'value' ;

- use case-sensitive database encoding, collation is named ending in _cs or _bin. Most likely, this method will not work for most cases, since no one will change the encoding of the entire database due to one operation with the need to process data in case sensitive ;

- use in a request a command with an explicitly specified encoding for a specific request, but errors are possible if the specified encoding is not available on the database server. Example query: SELECT * FROM `table` WHERE` column` COLLATE latin1_general_cs = 'value' ;

- use special SQL UPPER or LOWER commands, but this may affect performance if there is a large amount of data. Example query: SELECT * FROM `table` WHERE LOWER (` column`) = LOWER ('value') ;

- use case conversion in the code and then execute queries, for this functions like mb_strtolower, mb_strtoupper, etc. can be used ;

- use logic in your code to filter strings with different case. This means that first all the necessary data is selected that meet the specified criteria, after which an additional case check occurs in the code, for example, in PHP .

The last two methods may not be the most efficient for a large dataset. This approach is not suitable for all cases, since it is recommended to use database tools for better performance .

We have covered some techniques for case sensitive database . Similar working methods can be used for update and delete operations, that is, for UPDATE and DELETE commands.