CODEWITHSUNDEEP
mysqlunique
Vincenzo
Vincenzo

Reputation: 1158

MySQL + PHP ensure unique username (auto increment suffix)

Hi i am looking for the most performant way to ensure a unique username. I did already check similar questions but none of them made me happy. So here I came up with my solution. I appreciate your comments.

CREATE TABLE IF NOT EXISTS `user` (
`guid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`firstname` varchar(48) NOT NULL,
`lastname` varchar(48) NOT NULL,
`username` varchar(128) NOT NULL,
`unique_username` varchar(128) NOT NULL,


PRIMARY KEY (`guid`),
KEY `firstname` (`firstname`),
KEY `lastname` (`lastname`),
KEY `username` (`username`),
UNIQUE KEY `unique_username` (`unique_username`),
UNIQUE KEY `email` (`email`)


) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

username contains firstname.lastname without numeric suffix while unique_username contains firstname.lastname.(count of equal usernames)

to get the count of equal usernames I am performing following query against the user table (in advance to the insert).

SELECT COUNT(*) FROM user WHERE username = 'username'

Unfortunately I can't use a lookup against firstname and lastname since they are case sensitive.

Upvotes: 1

Views: 553

Answers (2)

Dan
Dan

Reputation: 11104

To get around the case sensitivity you can use LCASE(column) to compare lower case values:

SELECT COUNT(*) FROM user 
WHERE LCASE(lastname) = LCASE('Lastname') 
  AND LCASE(firstname) = LCASE('firstName');

You could also use LIKE to check the username field:

SELECT COUNT(*) FROM user WHERE username LIKE 'username%';

That way 'this.name', 'this.name.1' and 'this.name.2' would all get counted together.

I think both of these solutions will not let the optimizer take advantage of the indexes, so the performance might go down, but might be a non-issue.

Upvotes: 0

Honore Doktorr
Honore Doktorr

Reputation: 1625

The docs say “nonbinary strings (CHAR, VARCHAR, TEXT), string searches use the collation of the comparison operands… nonbinary string comparisons are case insensitive by default”, so you should be able to do this:

SELECT COUNT(*) FROM user WHERE CONCAT_WS('.', `firstname`, `lastname`) = 'username`

Upvotes: 1

Related Questions