Secondary AutoIncrement for `field_options.option_order`

[apmuthu]

MySQL Multi Column Auto Increment

Ref: https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html

• For MyISAM tables, you can specify AUTO_INCREMENT on a secondary column in a multiple-column index.
• In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix.
• This is useful when you want to put data into ordered groups.

The following works (Only with MyISAM):

CREATE TABLE `test001` (
  `Name` VARCHAR(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  id1 INT(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`Name`, id1)
) ENGINE=MYISAM;

INSERT INTO test001 (`Name`) VALUES ('Hello'); 
INSERT INTO test001 (`Name`) VALUES ('Job'); 
INSERT INTO test001 (`Name`) VALUES ('Hello'); 

Name id1 ==== === Hello 1 Hello 2 Job 1

The following also works (Only with MyISAM):

CREATE TABLE `test001` (
  id2 INT(11) NOT NULL,
  id1 INT(11) NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (id2, id1)
) ENGINE=MYISAM;

INSERT INTO test001 (id2, `Name`) VALUES (1, 'Hello'); 
INSERT INTO test001 (id2, `Name`) VALUES (2, 'Job'); 
INSERT INTO test001 (id2, `Name`) VALUES (1, 'Hello'); 

id2 id1 Name === === ==== 1 1 Hello 2 1 Job 1 2 Hello

The above code is valid for MyISAM tables only and can be applied to the field_options.option_order field.