Use ENUM type column : ENUM « Data Types « 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.03 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.01 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>








10.10.ENUM
10.10.1.ENUM Type
10.10.2.Possible value for a ENUM column
10.10.3.ENUM with default value
10.10.4.Use ENUM type column
10.10.5.Inserting data to ENUM type column
10.10.6.Use numeric operation in ENUM type column
10.10.7.If you retrieve an ENUM value in a numeric context, the column value's index is returned