how to generate AUTO_INCREMENT

[chrisschristou]

hello friend i just wanna learn if a way exist to generate AUTO_INCREMENT i have my datble and the colum ID is an AUTO_INCREMENT and the id is from 1, 2, 3 .... i would like to diferent id for exemple : CC13445356 i want 2 letter at begin or 3 and 6 number and i dont want write it every time i want a AUTO_INCREMENT do that for me i use phpmyadmin

[cereal]

An auto increment column MUST be integer, so you cannot use cc123, but you can create composite indexes, so you can create unique content, for example:

create table mytest(
    id int unsigned auto_increment,
    code varchar(3) not null,
    message text,
    primary key (code, id)
) engine = myisam default charset=utf8;

insert into mytest(code, message) values('aaa', 'hello'), ('bb', 'hi'), ('cc','hey'), ('aaa', 'world');
select * from mytest;

Will output:

+----+------+---------+
| id | code | message |
+----+------+---------+
|  1 | aaa  | hello   |
|  1 | bb   | hi      |
|  1 | cc   | hey     |
|  2 | aaa  | world   |
+----+------+---------+

Note that the id does not increments because the primary key is composed by the code and the id column in this order, in practice it is like using a unique index. If you reverse the index order:

primary key (id, code)

Then you get:

+----+------+---------+
| id | code | message |
+----+------+---------+
|  1 | aaa  | hello   |
|  2 | bb   | hi      |
|  3 | cc   | hey     |
|  4 | aaa  | world   |
+----+------+---------+
4 rows in set (0.00 sec)

As you see in this case there are consecutives increments on the id. More information:

• http://dev.mysql.com/doc/refman/5.5/en/optimization-indexes.html
• http://dev.mysql.com/doc/refman/5.5/en/example-auto-increment.html

You can also consider uuid_short() which return unique numeric strings:

• http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_uuid-short

An example of usage:

create table mytable_us(
    id bigint unsigned not null,
    code varchar(3) not null,
    message text,
    primary key (code, id)
) engine = myisam default charset = utf8;

insert into mytable_us (id, code, message) values(uuid_short(), 'aaa', 'hello'), (uuid_short(), 'bb', 'hi'), (uuid_short(), 'cc', 'hey'), (uuid_short(), 'aaa', 'world');

select * from mytable_us;

That outputs:

+-------------------+------+---------+
| id                | code | message |
+-------------------+------+---------+
| 23148386475573269 | aaa  | hello   |
| 23148386475573270 | bb   | hi      |
| 23148386475573271 | cc   | hey     |
| 23148386475573272 | aaa  | world   |
+-------------------+------+---------+
4 rows in set (0.00 sec)

But there are some limitations about this kind of solution, read the link above.

[chrisschristou]

thank you