Change user ID of existing users When I check my users list via by ID, I get IDs like these:


User: admin
Id: 1
User: second user
ID: 11
User: another user
ID: 21


I want them to be consecutive numbers from 1 to N.

What's my problem? How can I fix it?
While updating the user id is not recommended, for arguments sake, you could reset the user id column in the database.

This is with the caveat that you must also update all referencing tables that use that user id so that those tables also use the new user id value - to maintain "referential integrity". Otherwise, if you do not update all the related user ids, you will corrupt and mixup private user data

For example, the users_roles, file_managed, node, node_revision and other tables...

To match the users with the new uid, you could make an export first of user id to email address. Then you can make another export later and cross-reference the uid from the mail address - to check the new user id match. Keep in mind email address isn't strictly unique in drupal by default.

SELECT uid, mail FROM users;


Backup the users table first.

Then you could do something terrible like:

ALTER TABLE users DROP COLUMN uid;
ALTER TABLE users ADD uid INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;


That will obliterate and regenerate the users uid column.

UPDATE: As clarified by Berdir, curiously the user id field is not an autoincrement field in Drupal 7 by default. So, you may need to remove the autoincrement property after the UID sequence is recreated and convert to normal int with:

ALTER TABLE users MODIFY uid int(10) unsigned;


Note: you will need to regenerate the anonymous user account with:

INSERT INTO users (name, pass, mail, theme, signature, language, init, timezone) values ('', '', '', '', '', '', '', '');
UPDATE users SET uid = 0 WHERE name = '';


If you just want to hide the uid field value, you could instead try a user alias module like:

https://www.drupal.org/project/me