Mysql case sensitivity

[johnphpnewb]

I am trying to make my login database better. I am currently thinking about case sensitivity. I’ve spent two hours researching this subject. I think that i have a grip on the matter but i don’t know if the information is accurate. So in order to enforce case sensitivity for a username, i add a collation?

CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_cs;

so this collation (utf8mb4_unicode_cs) will automatically void john where i am looking for John? or do i still need to use a BINARY command?

select * from users where **binary** username = '$username'

from what i understand, LIKE allows ci comparison.

is this correct? thus my database now looks like the following:

CREATE DATABASE user_accounts CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_cs;

create table user_login(id int(11) not null unsigned auto_increment primary key, username varchar(255) not null unique key, password varchar(255) not null);

I don’t know why tutorials do not address these issues. Everyone just writes “create database users;” and finish with “now you have a login database.” SO much info is not mentioned. I always have to spend hours, days, weeks, months or years to learn how to do something properly. Very frustrating.

[JimL]

normally db/table names are lower and snake_case. Adding to that then most logins use email and password. Email is also lower case while passwords don’t matter as long as you only store the hash. ^^

Since the majority of queries beyond this is select by id, or search queries then case sensitivity often isn’t considered.

[johnphpnewb]

Hi, Jim. Thank you for taking time to post a reply. I was just discussing this with my wife and we think that case insensitive is better for usability. I was really thinking about avoiding duplicate entries in the database for usernames. Yet it doesn’t make sense to allow john and John or j0hn or J0hn etc. I suppose that i prefer case insensitive with my own user experience, so why should i expect different from my own customers? I’ll just move on with case insensitive. Atleast i have learned something new about databases today.