基于外键的MySQL自动增量 [英] MySQL Auto Increment Based on Foreign Key

问题描述

假设我有下面的表称为资产与以下字段：

Lets say I have the following table called assets with the following fields:

id | job_id | title

我想使用id和job_id作为主键。 job_id是外键。
id字段自动递增。
如果没有具有相同job_id的行，我将如何让id开始递增为0。
如果有一行有相同的job_id，然后增加1，依此类推？

I would like to use id and job_id as the primary keys. job_id is the foreign key. The id field is auto incremented. How would I get id to start incrementing at 0 if there is no row with the same job_id. If there is a row with the same job_id then increment id by 1 and so on?

我想找的结果是一个表，看起来像这样：

The result I am looking for is a table that looks like this:

id | job_id | title
0     1        hi
1     1        hello
2     1        goodbye
0     2        hi
1     2        hello

现在假设添加了一个包含job_id = 3的新行。 id字段应该从0开始自动递增。

Now lets say a new row with job_id = 3 is added. The id field should start auto incrementing from 0 again.

推荐答案

如果使用MyISAM存储引擎，多列主键将重新启动非自动增量列中的每个新值。

If you use the MyISAM storage engine, the auto-increment column in a multi-column primary key starts over with each new value in the non-auto-increment column.

请参阅 http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html

这篇关于基于外键的MySQL自动增量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！