自动增量复合键InnoDB [英] Auto Increment Composite Key InnoDB
本文介绍了自动增量复合键InnoDB的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想为InnoDB MySQL表创建类似于MyISAM的行为.我想要一个复合主键:
I want to create a MyISAM like behavior for an InnoDB MySQL table. I want to have a composite primary key:
PRIMARY KEY(id1,id2)
PRIMARY KEY(id1, id2)
其中id1根据id2的值自动递增.用InnoDB做到这一点的最佳方法是什么?
Where id1 auto increments based on the value of id2. What's the best way to accomplish this with InnoDB?
+----------+-------------+--------------+
| id1 | id2 | other_column |
+----------+-------------+--------------+
| 1 | 1 | Foo |
| 1 | 2 | Bar |
| 1 | 3 | Bam |
| 2 | 1 | Baz |
| 2 | 2 | Zam |
| 3 | 1 | Zoo |
+----------+-------------+--------------+
推荐答案
您可以使用此BEFORE INSERT触发器替换零个id值-
You can use this BEFORE INSERT trigger to substitute zero id-values -
CREATE TRIGGER trigger1
BEFORE INSERT
ON table1
FOR EACH ROW
BEGIN
SET @id1 = NULL;
IF NEW.id1 = 0 THEN
SELECT COALESCE(MAX(id1) + 1, 1) INTO @id1 FROM table1;
SET NEW.id1 = @id1;
END IF;
IF NEW.id2 = 0 THEN
IF @id1 IS NOT NULL THEN
SET NEW.id2 = 1;
ELSE
SELECT COALESCE(MAX(id2) + 1, 1) INTO @id2 FROM table1 WHERE id1 = NEW.id1;
SET NEW.id2 = @id2;
END IF;
END IF;
END
然后插入零值(id1或id2)以生成新值-
Then insert zero-values (id1 or id2) to generate new values -
INSERT INTO table7 VALUES(0, 0, '1');
INSERT INTO table7 VALUES(0, 0, '2');
INSERT INTO table7 VALUES(1, 0, '3');
INSERT INTO table7 VALUES(1, 0, '4');
INSERT INTO table7 VALUES(1, 0, '5');
...
这篇关于自动增量复合键InnoDB的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文