自动增加字段直到满足特定条件 mysql [英] Autoincrementing a field till a certain condition is met mysql

问题描述

我有下表

SNo Value Item

其中 Sno 是另一个表中也存在的列.现在，我需要的是一个自增字段，如果 sno 的值是一个常量，它将继续增加，然后回到 0 并在 sno 的值发生变化时再次开始增加.有没有办法做到这一点?

假设我有四列:

SNO |值 |项目 |自增字段1 344 一个 01 345 b 11 346 c 22 568 天 0

所以，当我说插入到这个表中，并且 SNO 的值从原来的改变时，自动递增字段的值应该回到 0.有没有内置的方法可以做到这一点，或者在上面写一些代码上面的mysql来实现这个.如果不是，我还有什么其他选择可以唯一标识属于特定 sno 值的每个值/项目?

解决方案

虽然这对 InnoDB 没有帮助，但值得指出的是 MyISAM 本身就支持此功能.如使用AUTO_INCREMENT 下所述:

<块引用>

MyISAM 笔记

对于 MyISAM 表，您可以在多列索引中的辅助列上指定 AUTO_INCREMENT.在这种情况下，AUTO_INCREMENT 列的生成值计算为 MAX(auto_increment_column) + 1 WHERE prefix=given-prefix.当您想将数据放入有序组中时，这很有用.

<前>创建餐桌动物(grp ENUM('fish','mammal','bird') NOT NULL,id MEDIUMINT NOT NULL AUTO_INCREMENT,名称 CHAR(30) 非空，主键 (grp,id)) 引擎=MyISAM;插入动物 (grp,name) 值('哺乳动物','狗'),('哺乳动物','猫'),('鸟','企鹅'),('鱼','松散'),('哺乳动物','鲸鱼'),('鸟','鸵鸟');SELECT * FROM 动物 ORDER BY grp,id;

哪个返回:

<前>+--------+----+---------+|grp |身份证 |姓名 |+--------+----+---------+|鱼 |1 |松弛||哺乳动物| 高分辨率照片| CLIPARTO1 |狗 ||哺乳动物| 高分辨率照片| CLIPARTO2 |猫 ||哺乳动物| 高分辨率照片| CLIPARTO3 |鲸鱼||鸟|1 |企鹅 ||鸟|2 |鸵鸟|+--------+----+---------+

在这种情况下(当 AUTO_INCREMENT 列是多列索引的一部分时)，如果删除具有最大 的行，将重新使用 AUTO_INCREMENT 值AUTO_INCREMENT 值在任何组中.即使对于 MyISAM 表，通常不会重用 AUTO_INCREMENT 值的表也会发生这种情况.

I have the following table

SNo Value Item

where Sno is a column which exists in another table also. Right now , what I need is a self incrementing field which will go on incrementing if the value of sno is a constant and then get back to 0 and start incrementing again once the value of sno changes. IS there any way to do this?

Lets say I have four columns:

SNO |Value |Item | AUtoIncrementingField
1    344      a            0 
1    345      b            1 
1    346      c            2 
2    568      d            0 

So when I say insert into this table , and the value of SNO changes from whatr it originally was the value of the auto incrementing field should go back to 0. Is there any inbuilt way of doing this, or writing some code on top of mysql to achieve this. If not what other option do I have to uniquely identify each value/item belonging to a certain value of sno?

解决方案

Whilst this doesn't help you on InnoDB, it's worth pointing out that MyISAM natively supports this functionality. As documented under Using AUTO_INCREMENT:

MyISAM Notes

For MyISAM tables, you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

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

In this case (when the AUTO_INCREMENT column is part of a multiple-column index), AUTO_INCREMENT values are reused if you delete the row with the biggest AUTO_INCREMENT value in any group. This happens even for MyISAM tables, for which AUTO_INCREMENT values normally are not reused.

这篇关于自动增加字段直到满足特定条件 mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！