Geek Answers Handbook

MySql auto-increment alpha digital primary key?

Is this possible in MySql ?? Can I have an automatically increasing primary key with a letter prefix, something like R1234, R1235, R1236 ... ect ??

+5
source share
3 answers

What you can do is save the key as two columns. The char prefix and auto increment int, both of which are grouped for the primary key.

CREATE TABLE myItems (
    id INT NOT NULL AUTO_INCREMENT,
    prefix CHAR(30) NOT NULL,
    PRIMARY KEY (id, prefix),
    ...
+10
source

No. But for MyIsam tables, you can create an index with multiple columns and put the auto_increment field in the secondary column, so that you will have approximately the same thing you specify:

CREATE TABLE t1 (prefix CHAR(1) NOT NULL, id INT UNSIGNED AUTO_INCREMENT NOT NULL,  
..., PRIMARY KEY(prefix,id)) Engine = MyISAM;
INSERT INTO t1(prefix) VALUES ('a'),('a'),('b'),('b');
SELECT * FROM t1;
a  1
a  2
b  1
b  2

: INNODB

+3

you can do this with two fields like this. but you cannot do this with one field as far as I know.

create table foo (
  code char,
  id int unsigned not null auto_increment
  primary key(id,code)
);
+2
source

More articles:


All Articles