How does auto increment work with composite keys in the Myisam engine?

Question

I ended up taking a table to manipulate, I noticed that sometimes the value of auto increment repeated, the first thing that called me the attention was that the key Primary was composed of two fields one of them was AI and the other not.

I set an example that reproduces this behavior:

CREATE TABLE t (
   id int(11) NOT NULL AUTO_INCREMENT,
   c2 varchar(45) NOT NULL,
   c3 varchar(45) NOT NULL,
   PRIMARY KEY (c2, id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Inserts

INSERT INTO t(c2, c3) VALUES ('A', 'A'), ('A', 'B'), ('B', 'A'), ('B', 'C'), ('C', 'A'), ('C', 'B'), ('A', 'C') 

Select result:

# |id|c2|c3
1 |1 |A |A
2 |1 |B |A
3 |1 |C |A
4 |2 |A |B
5 |2 |B |C
6 |2 |C |B
7 |3 |A |C

What is the logic of auto increment when it is part of a composite primary key?

Answer 1

Solution (if you really need a composite PK with AI field)

The 'problem' is restricted to the Myisam engine, as Innodb does not allow this type of construction. This repetition happens only when the auto increment field is not the first of the Primary composite key PRIMARY KEY (c2, id). Envying the order of the fields auto increment works sequentially as expected.

DROP TABLE t;

CREATE TABLE t (
  id int(11) NOT NULL AUTO_INCREMENT,
  c2 varchar(45) NOT NULL,
  c3 varchar(45) NOT NULL,
  PRIMARY KEY (id, c2)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO t(c2, c3) VALUES ('A', 'A'), ('A', 'B'), ('B', 'A'), ('B', 'C'), ('C', 'A'), ('C', 'B'), ('A', 'C');

SELECT * FROM t

Select result:

# |id|c2|c3
1 |1 |A |A
2 |2 |A |B
3 |3 |B |A
4 |4 |B |C
5 |5 |C |A
6 |6 |C |B
7 |7 |A |C

How it works

Before the auto increment value is determined, a value check is made of the other(s) column(s)(PK), if it exists (see Inserts 1, 2 and 7) the AI column will have its value incremented by +1 otherwise its value will be 1, or increment works by the 'group'.