MySQL非主键自增

发布于 2024-11-09 17:34:40 字数 559 浏览 4 评论 0原文

我想知道是否/如何为每个主键创建第二列自动增量：

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;

不幸的是，只有当我指定 ID 作为主键并且subId 作为索引键（但我需要它们一起，并且 ID 可以重复）。

示例数据（我需要的）：

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

将 ID 设置为主索引和 subId 索引的问题是 subId 将独立于 ID< /代码>。

如何实现这一目标？这可能吗？

原文

I'd like to know if / how it's possible to make a second column auto-increment 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, doesn't work, only if I specify ID as primary key and subId as index key (but I need them both together and ID can repeat).

Example data (what I need):

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

The problem with making ID primary and subId index is that subId will increment independently of ID.

How to achieve this and is it even possible?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦亿 2024-11-16 17:34:40

我不得不处理类似的问题，以不自然的方式排序类别树。
如果您一次插入一个 id 的所有行，您可以为每个 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;

这当然需要管理id 由应用程序而不是 mySQL 指定。

您也可以执行与最后一个代码块相同的操作来获取下一个可用的 id。

I had to deal with a similar problem ordering a category tree unnaturally.
If you are inserting all the rows for one id at one time, you could do something like this for each subId:

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

If you need to "add" a row to an id, you can set @seq with

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

This would of course require management of id by the application and not mySQL.

You could do that same as the last code block to get the next available id as well.

回复收藏 0 原文

旧故 2024-11-16 17:34:40

它可能不完全是您想要的，并且它仅适用于 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 |
+--------+----+---------+

It may not be exactly what you want and it only works with MyISAM and BDB tables, but I think the closest you will come to what you're asking for is a grouped primary key.

From the manual:

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

回复收藏 0 原文

~没有更多了~