Is this possible in MySql ?? Can I have an auto-incrementing Primary Key, prefixed with a letter, som开发者_如何学JAVAething like R1234, R1235, R1236... ect ??
What you could do is store the key as two columns. A char prefix and an auto-incrementing 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),
...
No. But for MyIsam tables you can create a multi-column index and put auto_increment field on secondary column, so you will have pretty much the same you are asking:
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
You can get more details from here Note: it's not going to work for INNODB engine
you can do it with two fields like this. but you can't do it with one field to my knowledge.
create table foo (
code char,
id int unsigned not null auto_increment
primary key(id,code)
);
精彩评论