MYSQL: Create Unique ID based on Another Column

Question

If I have the following column in database:

Email 
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]

I would like to ALTER THE TABLE and create a unique ID column based on the 'Email' column. Like the following:

Email           Email_ID
[email protected]   001
[email protected]   002
[email protected]   003
[email protected]   002
[email protected]   004
[email protected]   001

Answer 1

I would suggest that you use an integer for the value -- rather than a string. Then, you can use variables as for the assignment:

alter table t add email_id int;

update t join
       (select email, (@rn := @rn + 1) as rn
        from (select distinct email from t order by email) t cross join
             (select @rn := 0) params
       ) tt
       on t.email = tt.email
    set t.email_id = tt.rn;

If you run the subquery, you will see that this assigns to each email a distinct number.

The outer query then assigned this number into the email_id column. In MySQL 8+, you could also write:

alter table t add email_id int;

update t join
       (select email, row_number() over (order by email) as rn
        from (select distinct email from t order by email) t 
       ) tt
       on t.email = tt.email
    set t.email_id = tt.rn;

Answer 2

If you are using MySQL version 8 or later, then DENSE_RANK provides a nice option here:

SELECT
    Email,
    LPAD(CAST(DENSE_RANK() OVER (ORDER BY Email) AS CHAR(3)), 3, '0') AS Email_ID
FROM yourTable
ORDER BY
    Email;

I would have suggested maybe just adding an auto increment column to your table, but that wouldn't quite meet your requirements, because an auto increment column would always be unique.

Answer 3

Here's what I'd do...

1. Create a new table with a unique email column

   CREATE TABLE `emails` (
     id INT(3) PRIMARY KEY AUTO_INCREMENT,
     email VARCHAR(255) UNIQUE
   );
   

2. Seed it with your current data

   INSERT INTO `emails` (`email`)
   SELECT DISTINCT `email` FROM `some_mystery_table`
   ORDER BY `email`;
   

3. Alter your existing tables to reference emails(id) as a foreign key. This could be a little tricky as you'd need to (probably)

   1. Add a new int column email_id where required

   2. Update your data with the id value corresponding to the email address

      UPDATE some_mystery_table, emails
      INNER JOIN emails ON some_mystery_table.email = emails.email
      SET some_mystery_table.email_id = emails.id;
      

   3. Remove the email column

   4. Add a foreign key where email_id references emails(id)

4. When displaying your data and you need a zero-padded email id, join the emails table, eg

   SELECT a.whatever, e.email, LPAD(e.id, 3, '0') AS email_id
   FROM some_mystery_table a
   INNER JOIN emails e ON a.email_id = e.id;
   

5. When adding new email records, you add them to emails first, then use the generated id in any other related tables.

Answer 4

ALTER TABLE emails add column `email_id` int(5) ZEROFILL PRIMARY KEY AUTO_INCREMENT;
SET @x:=0;
UPDATE emails SET email_id = LPAD(@x:= (@x+1),4, '0') WHERE 1=1;

We first added the column email_id to the table emails and set it as primary key, using this query:

ALTER TABLE emails add column `email_id` int(5) ZEROFILL PRIMARY KEY AUTO_INCREMENT;

Then we declared a global variable called x with a default value of 0:

SET @x:=0;

And finally, we filled the column with the incremental zero filled id:

UPDATE emails SET email_id = LPAD(@x:= (@x+1),4, '0') WHERE 1=1;

We used LPAD to zero fill.