Geek asks and answers

Mysql auto-increment on non-primary key

Another question today :)
This time I would like to know if it is possible / possible to make the second step for each primary key:

CREATE TABLE test (
    `id` INTEGER UNSIGNED NOT NULL,
    `subId` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `text` VARCHAR(45) NOT NULL,
    PRIMARY KEY (`id`, `subId`)
)
ENGINE = InnoDB;

This creation, unfortunately, does not work, only if I specify IDas the primary key and subIdas the index key (but I need them together, but I IDcan repeat ...)
Examples of data (what I need):

1, 1
1, 2
1, 3
2, 1
2, 2
3, 1

The problem with creating the IDprimary index subIdis that it subIdwill increase regardless ID.
How to achieve this, and is it possible?

+3
source share
2 answers

, . , - subId:

SET @seq = 0;
INSERT INTO test
  (id,  subId,            text) VALUES
  (_id, @seq := @seq + 1, 'Some text')
;

"" id, @seq

SELECT IFNULL(MAX(subId), 0) INTO @seq FROM test WHERE id = _id;

, , , mySQL.

, , .

+2

, , MyISAM BDB, , , , .

:

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;
Which returns: 
+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+
+2

More articles:


All Articles