970_90

Thursday, 18 February 2016

How can I make SQL Query case sensitive string comparison on MySQL?

How can I make SQL Query case sensitive string comparison on MySQL

In Websites we display the data which fetch from database using string based conditions.
OR
Login Feature / Change password Featured based on string based conditions.
OR
Show the user detils behalf on user slug like "http://example.com/users/user-slug-value"


If you want to make your SQL Query as case sensitive, We have following 3 ways.

  1. Do at MySQL Server Level
    Set Following in /etc/mysql/my.cnf (Works for >MySQL 5.5 only)
    character-set-server=utf8
    collation-server=utf8_bin
  2. Set the column as Case sensitive,For Example
    ALTER TABLE `users` CHANGE `name` `name` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;
  3. Set the Query as case sensitive, For Example
    SELECT *  FROM `users` WHERE BINARY `name` = 'raJ'



No comments:

Post a Comment