Fügen Sie Fremdschlüssel zu Big-Data-Typen wie varchar(2000)-MySQL-Tabellen hinzu

Question

Ich habe zwei Tabellen „affiliate_stats“ und „transaction“, „affiliate_stats“ hat die Spalte „affiliate_sales“ varchar(2000) (Untertabelle), und „transaction“ hat die Spalte „_id“ varchar(100) Primärschlüssel (übergeordnete Tabelle)

Wenn ich einen Fremdschlüssel zu „affiliate_sales“ hinzufüge, der auf „_id“ verweist durchgehen Tabelle ändernaffiliate_statsEinschränkung hinzufügenfk_affili_salesFremdschlüssel (affiliate_sales) verweist auf Transaktion (_id); /p>

Diese Fehlermeldung wird angezeigt. Der angegebene Schlüssel war zu lang. Die maximale Schlüssellänge beträgt 3072 von tes Ich weiß, dass beide Spalten gleich groß sein sollten, aber in meinem Fall benötige ich unterschiedliche Größen, um damit umzugehen. In vielen Quellen gesucht, keine klare Antwort, und keine der Lösungen hat für mich funktioniert.

Answer 1

将列 affiliate_sales 更改为 VARCHAR(100)。

ALTER TABLE `affiliate_stats` 
  MODIFY COLUMN affiliate_tales VARCHAR(100) NOT NULL,
  ADD CONSTRAINT `fk_affili_sales` 
  FOREIGN KEY (affiliate_sales) 
  REFERENCES transaction(`_id`);

如果它是 transaction(_id) 的外键，那么它不需要是 VARCHAR(2000) 因为它永远不能容纳超过 100 的字符串无论如何，字符。

执行此操作之前，请确保该列中当前没有长度超过 100 个字符的字符串。

---

回复您的评论：

如果您打算在列中存储“数组”（即带有逗号分隔列表的字符串），您应该明白无论如何您都无法在其上创建外键。外键要求列将一个值与transaction表中的一行相关联。如果添加外键约束，则该列中只能存储一个 id。

听起来 affiliate_stats 和 transaction 之间确实存在多对多关系。您需要第三个表来建模多对多关系。

Answer 2

varchar(2000) 存储 2000 个字符，但每个字符不一定是 1 个字节。简单的欧洲字符，例如 a、1 和 ?都是一个字节。但是，ü 或 å 或 َََُِِّْ‎ 占用多个字节。

---

这是可能的，字符串类型可以不需要具有相同的长度。

但是，这是没有必要的。 密钥不可能超过 100 个字符。您的主键是 varchar(100)，因此外键不会超过 100 个字符。

但是，您似乎想在此列中存储两种不同类型的数据。一种是某种数组类型，一种是外键。你不能。 外键确保每个值都有一个匹配的主键键值。

---

相反，请使用自动递增主键将表链接在一起。它更简单、更快、使用更少的存储、明确且永远不会改变。

添加一个新列作为两个表的主键。然后引用它。

---

您不能在一列中存储多个键。 MySQL 需要能够确认每个外键都有一个匹配的主键，并且只能通过检查它们是否完全相等来做到这一点。

相反，如果每个 affilate_stats 行对应多个affiliate_sales，则您需要一个联接表。这称为一对多关系。一行affiliate_stats 与许多affiliate_sales 相关。

-- I'm assuming we've changed to bigint primary keys.
create table affiliate_stats_sales (
    affiliate_sales_id bigint not null,
    foreign key(affiliate_sales_id) references affiliate_sales(id),

    affiliate_stats_id bigint not null,
    foreign key(affiliate_stats_id) references affiliate_stats(id)
);

现在，对于要添加到affiliate_stats 行中的每个af​​filiate_sale，请在affiliate_stats_sales 中插入一行。

-- Relate affiliate_sale 100 to affiliate_stats 20
insert into affiliate_stats_sales(affiliate_sales_id, affiliate_stats_id) values (100, 20)

销售和统计数据通过连接该表来关联。例如，如果您想查看销售 100 件的统计信息。

select affiliate_stats.*
from affiliate_stats
join affiliate_stats_sales
  on affiliate_stats_sales.affiliate_stats_id = affiliate_stats.id
where affiliate_sales.id = 100

一开始有点困难，但非常强大。这就是关系数据库的工作原理。