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)
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 |