Menu
I have a table in my Mysql database, which is used for authentication. And now, I need to make the authentication case sensitive. Googling around, I have realized Mysql columns are case insensitive (contrary to Oracle) for search operations and the default behavior can be changed while creating the table by specifying the "binary" ie.
CREATE TABLE USERS ( USER_ID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, USER_NAME VARCHAR(50) BINARY NOT NULL ) Can someone please tell me how to alter the table in Mysql to add the "Binary" to an existing column of a DB?
Thanks!
837
SQL Server is, by default case insensitive; however, it is possible to create a case sensitive SQL Server database and even to make specific table columns case sensitive. The way to determine a database or database object is by checking its “COLLATION” property and look for “CI” or “CS” in the result.
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.
Column, index, stored routine, and event names are not case-sensitive on any platform, nor are column aliases. However, names of logfile groups are case-sensitive. This differs from standard SQL. By default, table aliases are case-sensitive on Unix, but not so on Windows or macOS.
select * from users where lower(first_name) = 'ajay'; The method is to make the field you are searching as uppercase or lowercase then also make the search string uppercase or lowercase as per the SQL function.
ALTER TABLE USERS CHANGE USER_NAME USER_NAME VARCHAR(50) BINARY NOT NULL; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
