CODEWITHSUNDEEP
sqlmysql
Yada
Yada

Reputation: 31225

Best way to implement alphanumeric id

I'm noticing short URL sites are using alphnumeric id instead of integers for unqiue id.

Have anybody have experience with implementation? What is the best way to implement this feature in MySQL? I believe the tricky part would probably be the incrementation and indexing of the id.

example: bit.ly/AbC123

Upvotes: 1

Views: 4480

Answers (4)

codeholic
codeholic

Reputation: 5848

If you don't care about the length of an ID, consider using UUID().

There are also various means for server-side programming languages such as uniqid in PHP or Data::UUID in Perl.

Upvotes: 1

ggiroux
ggiroux

Reputation: 6724

I suspect most of these alphanumeric ids are just a numeric ID converted to Base 36 - see this sample stored function, or you could do it using a UDF.

Upvotes: 5

hobodave
hobodave

Reputation: 29303

I would generate the hash separately but maintain an integer (or BIGINT) column for the primary key. MySQL works best with smaller primary keys, especially InnoDB.

For example:

CREATE TABLE `urls` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `hash` char(6) NOT NULL,
  `url` varchar(1000) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_hash` (`hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Edit: For generating the hash see this SO post for a base62 converter implementation.

Upvotes: 1

David Pfeffer
David Pfeffer

Reputation: 39833

Honestly, I'd just use an integer internally and then convert to an alphanumeric to expose to the user, if you must use an alphanumeric sequence. As an example encoding scheme, you could use each digit as an index into an array consisting of letters from A through J (corresponding to 0 through 9).

Integers are a much better unique id in general, because they take less memory and because they are more easily indexed.

Upvotes: 3

Related Questions