MySQL - how to use VARCHAR as AUTO INCREMENT Prima

问题:

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

回答1:

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

回答2:

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.