引用自增列? [英] reference auto-increment columns?

问题描述

我有 2 个使用同一列的表；一张表包含文本，另一张表包含图像；他们使用列 listing_id 以便正确的文本与正确的图像一起显示；

I have 2 tables that I am working with that use the same column; one table contains the text and the other table contains the images; they use the column listing_id so that the right text shows up with the right images;

我的问题是，因为列 listing_id 是自动递增的，所以我的第一个表能够插入到查询中，该查询能够插入文本然后 +1 列 listing_id；但是，我使用另一个 INSERT INTO 查询的第二个表将没有正确的列表 ID，

my problem is that because column listing_id is auto-increment, my first table is able to have an insert into query that is able to insert the text and then +1 the column listing_id; however the 2nd table I use another INSERT INTO query will not have the right listing_id,

因为listing_id的一些条目已被删除，这意味着第二个表的listing_id将始终在第一个表listing_id之后；

because some entries for listing_id have been deleted, meaning that the 2nd table's listing_id will always be behind the 1st tables listing_id;

如何引用列 listing_id?

how do I reference the column listing_id?

推荐答案

您需要在依赖表中创建一个名为parent_id"的 INT 列，该列存储它所引用的主表的 ID.当您从第一个选择记录时，您将使用第一个字段的 auto_increment 字段对第二个字段的parent_id"加入表.

You need to create an INT column called something like "parent_id" in the dependant tables that stores the id of the main table that it is referencing. When you select records from the first, you would then JOIN the tables with the auto_increment field of the first field against the "parent_id" of the second.

正如 MrSlayer 提到的，使用第一张表的新插入的 ID 来更新parent_id".您应该通常在第二个表中有一个唯一的 ID 字段以确保唯一性，但它不应该是与第一个表的关系的一部分.

As MrSlayer mentions, use the newly inserted ID of the first table to update "parent_id". You should typically have a unique ID field in the second table for uniqueness, but it shouldn't be part of the relationship to the first table.

如果您不清楚如何获取插入时第一个表自动递增的 id，请使用 mysql_insert_id().

If you're unclear about how to get the id that the first table auto_increments to when you insert, use mysql_insert_id().

mysql_query("INSERT INTO table1 ...");
echo "Last inserted record_id in table1 was " .  mysql_insert_id();

INSERT INTO table1 (mytextcolumn) VALUES('text');
INSERT INTO table2 (parent_id,image_name) VALUES(LAST_INSERT_ID(),'someimage.png'); 

这篇关于引用自增列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！