Case sensitive database - how to fetch, update, or delete data if case-insensitive _ci encoding is selected
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.
- IT / Misc 08.07.21 How to make a free translation for a website without an API, translate documents in Google Translate
- IT / Misc 06.07.21 How to make a subscription button on a website, a subscriber base and automatic mailing
- Food / Misc 06.07.21 How to quickly cook delicious fried pies with potatoes and onions
- IT / Misc 04.07.21 Caching - create, load and reset. Where to store the cache, methods and types of caching
- IT / Database 03.07.21 Custom NoSQL - storing data in files and not only in a database. Storing settings, small data and caching files