By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test ( id INT,
type VARCHAR(192),
value INT,
PRIMARY KEY (id, type) );
-- create additional service table which will help
CREATE TABLE test_sevice ( type VARCHAR(192),
id INT AUTO_INCREMENT,
PRIMARY KEY (type, id) ) ENGINE = MyISAM;
-- create trigger which wil generate id value for new row
CREATE TRIGGER tr_bi_test_autoincrement
BEFORE INSERT
ON test
FOR EACH ROW
BEGIN
INSERT INTO test_sevice (type) VALUES (NEW.type);
SET NEW.id = LAST_INSERT_ID();
END
-- now let's test
INSERT INTO test (type, value) VALUES
('type 1',1),
('type 1',2),
('type 2',3),
('type 3',4),
('type 2',5),
('type 3',6),
('type 1',7),
('type 2',8),
('type 1',9),
('type 4',10);
SELECT * FROM test ORDER BY value; -- in insertion order
SELECT * FROM test ORDER BY type, id; -- in enumerated order
Records: 10 Duplicates: 0 Warnings: 0
| id | type | value |
|---|---|---|
| 1 | type 1 | 1 |
| 2 | type 1 | 2 |
| 1 | type 2 | 3 |
| 1 | type 3 | 4 |
| 2 | type 2 | 5 |
| 2 | type 3 | 6 |
| 3 | type 1 | 7 |
| 3 | type 2 | 8 |
| 4 | type 1 | 9 |
| 1 | type 4 | 10 |
| id | type | value |
|---|---|---|
| 1 | type 1 | 1 |
| 2 | type 1 | 2 |
| 3 | type 1 | 7 |
| 4 | type 1 | 9 |
| 1 | type 2 | 3 |
| 2 | type 2 | 5 |
| 3 | type 2 | 8 |
| 1 | type 3 | 4 |
| 2 | type 3 | 6 |
| 1 | type 4 | 10 |