Differences between different engines in MySQL auto_increment

Recently, for performance problems, I used the MyISAM engine to create tables and found that the difference between the two is still a little big in auto_increment. The following describes the differences.

1. InnoDB:

Only one column in the table is auto_increment and must be the primary key. If you use the following statement to create a table, an error is returned:

 
Create TableDd (GRPVarchar(10)Not Null, IDInt Not NullAuto_increment,Primary Key(GRP, ID) Engine=InnoDB;
 

The following is OK, because ID is the primary key.

Create TableDd (GRPVarchar(10)Not Null, IDInt Not NullAuto_increment,Primary Key(ID) Engine=InnoDB;
 

The following is a test example:

Sample

 Drop   Table   If   Exists Dd;
Create   Table Dd (GRP Varchar ( 10 ) Not   Null , ID Int   Not   Null Auto_increment, Primary   Key (ID) Engine = InnoDB;
 Insert   Into DdValues ( '  A  ' , Null ),( '  A  ' , Null ),( '  B  ' , Null ),( '  B  ' ,Null ),( '  B  ' , Null );
 Select   *   From Dd;

2. MyISAM:
Relatively loose than InnoDB. If the primary key is multiple columns, one column can be auto_increment. When a new column is inserted, the auto-increment column is calculated by grouping values of other columns of the primary key, then the auto-increment column increments in the same group. The formula is newvalue = (max (value) in the same group) + 1;

The following is an example:

Sample

 Drop   Table  If   Exists Dd;
 Create   Table Dd (GRP Varchar ( 10 ) Not   Null , ID Int   Not   Null Auto_increment, Primary   Key (GRP, ID) Engine = MyISAM;
 Insert   Into Dd Values ( '  A  ' , Null ),( '  A  ' , Null ),( '  B  ' , Null ),('  B  ' , Null ),( '  B  ' , Null );
 Select   *   From Dd;