Mysql Remove Case Sensitive
Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive. You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or macOS).
I have a function that returns five characters with mixed case. If I do a query on this string it will return the value regardless of case.
How can I make MySQL string queries case sensitive?
StevenBStevenB11 Answers
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:
If you want a column always to be treated in case-sensitive fashion, declare it with a case sensitive or binary collation.
drudgedrudgeThe good news is that if you need to make a case-sensitive query, it is very easy to do:
Craig WhiteCraig WhiteInstead of using the = operator, you may want to use LIKE or LIKE BINARY
It will take 'a' and not 'A' in its condition
Answer posted Craig White, has big performance penalty
because it don't use indexes. So, either you need to change the table collation like mention here https://dev.mysql.com/doc/refman/5.7/en/case-sensitivity.html.
OR
Easiest fix, you should use a BINARY of value.
Eg.
VS
1 row in set (0.00 sec)
Mysql Case Sensitive Table Names
To make use of an index before using the BINARY, you could do something like this if you have large tables.
The subquery would result in a really small case-insensitive subset of which you then select the only case-sensitive match.
Following is for MySQL versions equal to or higher than 5.5.
Add to /etc/mysql/my.cnf
All other collations I tried seemed to be case-insensitive, only 'utf8_bin' worked.
Do not forget to restart mysql after this:
According to http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html there is also a 'latin1_bin'.
The 'utf8_general_cs' was not accepted by mysql startup. (I read '_cs' as 'case-sensitive' - ???).
You can use BINARY to case sensitive like this
unfortunately this sql can't use index, you will suffer a performance hit on queries reliant on that index
Fortunately, I have a few tricks to solve this problem
Excellent!
I share with you, code from a function that compares passwords:
No need to changes anything on DB level, just you have to changes in SQL Query it will work.
Example -
'SELECT * FROM <TABLE> where userId = ' + iv_userId + ' AND password = BINARY ' + iv_password + '';
Binary keyword will make case sensitive.
Gaurav JeswaniThe most correct way to perform a case sensitive string comparison without changing the collation of the column being queried is to explicitly specify a character set and collation for the value that the column is being compared to.
Why not use binary?
Using the binary operator is inadvisable because it compares the actual bytes of the encoded strings. If you compare the actual bytes of two strings encoded using the different character sets two strings that should be considered the same they may not be equal. For example if you have a column that uses the latin1 character set, and your server/session character set is utf8mb4, then when you compare the column with a string containing an accent such as 'café' it will not match rows containing that same string! This is because in latin1 é is encoded as the byte 0xE9 but in utf8 it is two bytes: 0xC3A9.
Why use convert as well as collate?
Collations must match the character set. So if your server or session is set to use the latin1 character set you must use collate latin1_bin but if your character set is utf8mb4 you must use collate utf8mb4_bin. Therefore the most robust solution is to always convert the value into the most flexible character set, and use the binary collation for that character set.
Why apply the convert and collate to the value and not the column?
When you apply any transforming function to a column before making a comparison it prevents the query engine from using an index if one exists for the column, which could dramatically slow down your query. Therefore it is always better to transform the value instead where possible. When a comparison is performed between two string values and one of them has an explicitly specified collation, the query engine will use the explicit collation, regardless of which value it is applied to.
Accent Sensitivity
It is important to note that MySql is not only case insensitive for columns using an _ci collation (which is typically the default), but also accent insensitive. This means that 'é' = 'e'. Using a binary collation (or the binary operator) will make string comparisons accent sensitive as well as case sensitive.
What is utf8mb4?
The utf8 character set in MySql is an alias for utf8mb3 which has been deprecated in recent versions because it does not support 4 byte characters (which is important for encoding strings like 🐈). If you wish to use the UTF8 character encoding with MySql then you should be using the utf8mb4 charset.
mysql is not case sensitive by default, try changing the language collation to latin1_general_cs