CODEWITHSUNDEEP
mysqlprimary-keyauto-increment
Kenny Cason
Kenny Cason

Reputation: 12328

MySQL - how to use VARCHAR as AUTO INCREMENT Primary Key

I am using a VARCHAR as my primary key. I want to auto increment it (base 62, lower/upper case, numbers), However, the below code fails (for obvious reasons):

CREATE TABLE IF NOT EXISTS `campaign` (
  `account_id` BIGINT(20) NOT NULL,
  `type` SMALLINT(5)  NOT NULL,
  `id` VARCHAR(16) NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

however, this works:

CREATE TABLE IF NOT EXISTS `campaign` (
  `account_id` BIGINT(20) NOT NULL,
  `type` SMALLINT(5)  NOT NULL,
  `id` VARCHAR(16) NOT NULL PRIMARY KEY
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

What is the best way to keep track of incrementation of 'id' myself? (Since auto_increment doesn't work). Do i need to make another table that contains the current iteration of ID? Or is there a better way to do this?

EDIT: I want to clarify that I know that using INT is a auto_increment primary key is the logical way to go. This question is in response to some previous dialogue I saw. Thanks

Upvotes: 8

Views: 54315

Answers (4)

ArtfulOne
ArtfulOne

Reputation: 1

Assuming that for reasons external to the database, you do need that varchar column, and it needs to autoIncrement, then how about creating a trigger that grabs the existing autoIncrement value and uses Convert() to convert that value into a VarChar, dropping the VarChar into the field of interest. As mentioned in a previous answer, you could concatenate the table-name with the new varChar value, if there is some advantage to that.

Upvotes: 0

Jonas
Jonas

Reputation: 39

Or just create a sequence and maintain the pk field using the sequence to generate the primary key value with nextval function. And if perf is an issue, use cache on sequence.

But as others have stated, this is sub-optimal, if your primary key contains a numbered sequence then it's better to use int and auto-increment. I don't see a use case where pk has to auto-increment but be a varchar data type, it doesn't make sense.

Upvotes: 0

Gabriel Balbuena
Gabriel Balbuena

Reputation: 51

example of a solution to your problem:

create a file with a unique number and then increment with a function.

the filename can be the prefix and the file binary content represent a number.

when you need a new id to the reg invoque the function

Example

    String generateID(string A_PREFIX){
        int id_value = parsetoInt(readFile(A_PREFIX).getLine())
        int return_id_value = id_value++
        return return_id_value
    }

where "A_PREFIX-" is the file name wich you use to generate the id for the field.

Upvotes: 0

Your Common Sense
Your Common Sense

Reputation: 157828

you have to use an INT field
and translate it to whatever format you want at select time

Upvotes: 8

Related Questions