MySQL:使用InnoDB分组主键

问题描述

使用InnoDB MySQL数据库是否有等效于分组的主键，并允许第二个键自动递增?

Is there an equivalent to a grouped primary key using an InnoDB MySQL database and allowing auto-increment on the second key?

我要实现的是一个基于父对象ID存储图像URL的表，例如:

What I am trying to implement is a table to store image URL's based on a parent object ID something like:

CREATE TABLE images (
   parent_id INT(11) NOT NULL,
   image_id INT(11) AUTO_INCREMENT, //will this work with a grouped primary key?
   source_url VARCHAR(255) NOT NULL,
   caption VARCHAR(255) NOT NULL,
) ENGINE = InnoDB;

对于MyISAM，我可以做类似的事情:

So for MyISAM I could do something like:

PRIMARY KEY(parent_id, image_id)

[edit]使用触发器将类似于:(对不起，我的SQL不太强)

[edit] Using a trigger would be something like: (sorry my SQL is not very strong)

delimiter //
DROP TRIGGER IF EXISTS test_img_trigger;
CREATE TRIGGER test_img_trigger BEFORE INSERT ON venue_images
FOR EACH ROW
BEGIN
DECLARE img_id INT UNSIGNED DEFAULT 0;

SELECT image_id + 1 INTO img_id FROM venue_images WHERE venue_id = NEW.venue_id;
UPDATE venue_images SET image_id = img_id WHERE venue_id = NEW.venue_id;

END IF;
END;//
delimiter;

推荐答案

以防万一，这可以解决我的问题.

In case this can help anyone else this was my solution.

我不能简单地使用触发器，因为MySQL尚未(或至少是我的5.1.53版)不支持使用触发器来更新调用该触发器的表的功能.所以我创建了一个序列表:

I could not simply use a trigger as MySQL does not yet (or at least my version 5.1.53) support the ability to use a trigger to update the table the trigger is called on. So I created a sequence table:

CREATE TABLE image_seq (
    parent_id INT(11) NOT NULL,
    catagory INT(11) NOT NULL,
    next_seq_id INT(11) NOT NULL,
    KEY(catagory),
    PRIMARY KEY(parent_id, catagory);
);

然后在images表上创建触发器:

And then created a trigger on the images table:

CREATE TRIGGER bi_image_trig
BEFORE INSERT ON images
FOR EACH ROW
BEGIN

DECLARE id INT UNSIGNED DEFAULT 1;
 SELECT next_seq_id + 1 INTO id FROM image_seq WHERE parent_id=NEW.parent_id AND       catagory=NEW.catagory;
SET NEW.image_id = id;
UPDATE image_seq SET next_seq_id=id WHERE parent_id=NEW.parent_id AND catagory=NEW.catagory;

END;

不确定它是否是最佳解决方案，但它是否有效，并允许我跟踪每个父对象的目录中的图像编号.

Not sure if its the optimal solution but it works and allows me to keep track of image numbers in catagories for each parent object.

这篇关于MySQL:使用InnoDB分组主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！