Composite Primary Key with auto increment in innodb

Question

I have created a table as below with MYISAM:

CREATE TABLE zzz_test.`table_with_index` (
  `grp_name` VARCHAR(70) NOT NULL,
  `sub_grp` INT NOT NULL,
  `item_index` INT NOT NULL AUTO_INCREMENT,
  `item` VARCHAR(5) NOT NULL,

  PRIMARY KEY (`grp_name`,`sub_grp`,`item_index`)
) ENGINE=MYISAM

grp_name	sub_grp	item_index	item
GRP_A	1	1	....
GRP_A	1	2	....
GRP_A	1	3	....
GRP_A	2	1	....
GRP_A	2	2	....
GRP_A	2	3	....
GRP_B	1	1	....
GRP_B	1	2	....
GRP_B	1	3	....
GRP_B	2	1	....
GRP_B	2	2	....
GRP_B	2	3	....

I want to know why INNODB does not support such configuration?
Also, is there any simple way to have same outcome in INNODB?
(I have to insert this table from another with select)

Answer 1

Thank you @Akina. This really help me get it done as follow.

CREATE DATABASE zzz_test;
CREATE TABLE zzz_test.`table_raw` (
  `grp_name` VARCHAR(70) NOT NULL,
  `sub_grp` INT NOT NULL,
  `item` VARCHAR(5) NOT NULL
) ENGINE=INNODB;

INSERT INTO zzz_test.`table_raw` VALUES 
  ('GRP_A',1, 'xxx'),
  ('GRP_A',2, 'yyy'),
  ('GRP_A',1, 'yyy'),
  ('GRP_A',2, 'xxx'),
  ('GRP_B',1, 'xxx'),
  ('GRP_B',1, 'yyy'),
  ('GRP_B',2, 'yyy'),
  ('GRP_B',2, 'xxx')
;

Records: 8  Duplicates: 0  Warnings: 0

CREATE TABLE zzz_test.`table_with_index` (
  `grp_name` VARCHAR(70) NOT NULL,
  `sub_grp` INT NOT NULL,
  `item_index` INT NOT NULL,
  `item` VARCHAR(5) NOT NULL,
  PRIMARY KEY (`grp_name`,`sub_grp`,`item_index`)
) ENGINE=INNODB;

INSERT INTO zzz_test.`table_with_index`
SELECT grp_name, sub_grp,
       ROW_NUMBER() OVER (
            PARTITION BY grp_name,sub_grp 
            ORDER BY grp_name,sub_grp,item
       ) AS item_index,
       item
FROM zzz_test.`table_raw`
ORDER BY grp_name,sub_grp,item;

Records: 8  Duplicates: 0  Warnings: 0

SELECT * FROM zzz_test.`table_with_index`;

grp_name	sub_grp	item_index	item
GRP_A	1	1	xxx
GRP_A	1	2	yyy
GRP_A	2	1	xxx
GRP_A	2	2	yyy
GRP_B	1	1	xxx
GRP_B	1	2	yyy
GRP_B	2	1	xxx
GRP_B	2	2	yyy

fiddle