I am importing data from a student information system into MySQL. I
need to create unique usernames in the format of "first.last". If
there is a duplicate, I would like to be able to insert the username
as "first.last[n]". Currently I have 262 duplicate combinations of
first and last names.

I imagine this can be done with some sort of looping structure, but I
would like to ensure that it remains fast (currently importing 10,000+
users in under 10 seconds).

I am thinking of doing the following (not any particular language, but
you should get the idea):
<code>
i = 1
dups = false
dupnum = 0

dupnum = SELECT COUNT(*) FROM students s WHERE s.uername IS NULL

if dupnum > 0 then
dups = true
else
dups = false
end

Do while dups = true
UPDATE IGNORE students s SET s.user_name = CONCAT(s.firstname, '.',
s.lastname, i)
dupnum = SELECT COUNT(*) FROM students s WHERE s.username IS NULL
if dupnum >= 1 then
dups = true
else
dups = false
Loop
</code>

Any thoughts or better ideas?

Thanks!

Shane wrote:
> I am importing data from a student information system into MySQL. I
> need to create unique usernames in the format of "first.last". If
> there is a duplicate, I would like to be able to insert the username
> as "first.last[n]". Currently I have 262 duplicate combinations of
> first and last names.
>
> I imagine this can be done with some sort of looping structure, but I
> would like to ensure that it remains fast (currently importing 10,000+
> users in under 10 seconds).
>
> I am thinking of doing the following (not any particular language, but
> you should get the idea):
> <code>
> i = 1
> dups = false
> dupnum = 0
>
> dupnum = SELECT COUNT(*) FROM students s WHERE s.uername IS NULL
>
> if dupnum > 0 then
> dups = true
> else
> dups = false
> end
>
> Do while dups = true
> UPDATE IGNORE students s SET s.user_name = CONCAT(s.firstname, '.',
> s.lastname, i)
> dupnum = SELECT COUNT(*) FROM students s WHERE s.username IS NULL
> if dupnum >= 1 then
> dups = true
> else
> dups = false
> Loop
> </code>
>
> Any thoughts or better ideas?
>
> Thanks!
>

A PK should be independent of the data. What are you going to do if,
for instance, a student's name changes (i.e. gets married)?

Use an auto_increment column for the primary key instead. Or, if you
have a student id number, that would be a second choice.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

>I am importing data from a student information system into MySQL. I
>need to create unique usernames in the format of "first.last". If
>there is a duplicate, I would like to be able to insert the username
>as "first.last[n]". Currently I have 262 duplicate combinations of
>first and last names.

Are you going to do this *ONCE*, or *MULTIPLE TIMES*?
If you do the import once, a few more weeks pass and a few more John Smiths
enroll, and then you need to do the import again, is it a problem that
John.Smith65535 isn't the same guy as previously had John.Smith65535?

I hope this username is NOT a primary key. Obviously it's not being
imported from the student information system since you are trying
to create it. I hope there is a different primary (student number?
but that might cause privacy issues) to relate records on SIS with
records on your system.

>I imagine this can be done with some sort of looping structure, but I
>would like to ensure that it remains fast (currently importing 10,000+
>users in under 10 seconds).

>I am thinking of doing the following (not any particular language, but
>you should get the idea):
><code>
>i = 1
>dups = false
>dupnum = 0
>
>dupnum = SELECT COUNT(*) FROM students s WHERE s.uername IS NULL
>
>if dupnum > 0 then
> dups = true
>else
> dups = false
>end

Assuming that records are initially inserted with username as NULL,
and you have a unique index on `username`, these queries can set up username:

update ignore students set username = concat(fn, '.', 'ln') where username is null;
update ignore students set username = concat(fn, '.', 'ln', '1') where username is null;
update ignore students set username = concat(fn, '.', 'ln', '2') where username is null;
....

Keep repeating until the number of records where username is null
is zero. After the first update, only records with username still
NULL are affected, so speed should be related to the number of
records with still-unassigned usernames. You can use these as
fire-and-forget queries in a batch file without a lot of support
from a language like PHP, as long as the highest number you need.

You can also do a partial import of *new* records and use the same
set of queries above to assign new usernames while leaving
previously-assigned usernames unchanged.

This depends on a couple of features of MySQL which I'm not sure are standard:
1. You can have a UNIQUE index with null values in it.
2. UPDATE IGNORE will cause key conflicts to not abort the update, and
the first query above will assign all possible non-conflicting usernames,
leaving NULL for conflicting ones.

>Do while dups = true
> UPDATE IGNORE students s SET s.user_name = CONCAT(s.firstname, '.',
>s.lastname, i)
> dupnum = SELECT COUNT(*) FROM students s WHERE s.username IS NULL
> if dupnum >= 1 then
> dups = true
> else
> dups = false
>Loop