Use Auto_increment column as part of the key : AUTO_INCREMENT « Table « MySQL Tutorial






mysql>
mysql> CREATE TABLE employee (
    ->     grp ENUM('A','B','C') NOT NULL,
    ->     id MEDIUMINT NOT NULL AUTO_INCREMENT,
    ->     name CHAR(30) NOT NULL,
    ->     PRIMARY KEY (grp,id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> INSERT INTO employee (grp,name) VALUES ('A','A1'),
    ->                                        ('B','B1'),
    ->                                        ('C','C1'),
    ->                                        ('A','A2'),
    ->                                        ('B','B2'),
    ->                                        ('C','C2');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM employee ORDER BY grp,id;
+-----+----+------+
| grp | id | name |
+-----+----+------+
| A   |  1 | A1   |
| A   |  2 | A2   |
| B   |  1 | B1   |
| B   |  2 | B2   |
| C   |  1 | C1   |
| C   |  2 | C2   |
+-----+----+------+
6 rows in set (0.00 sec)

mysql>
mysql>
mysql> drop table employee;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>








4.18.AUTO_INCREMENT
4.18.1.Use Auto_increment column as part of the key
4.18.2.To start with an AUTO_INCREMENT value other than 1
4.18.3.You can retrieve the most recent AUTO_INCREMENT value with the LAST_INSERT_ID().
4.18.4.For a multiple-row insert, LAST_INSERT_ID() returns the AUTO_INCREMENT key from the first of the inserted rows