向 varchar(2000) mysql 表等大数据类型添加外键

Question

我有两个表“affiliate_stats”和“transaction”， 'affiliate_stats' 有列 'affiliate_sales' varchar(2000) （子表）， 并且“transaction”具有列“_id”varchar(100) 主键（父表）

当我将外键添加到引用“_id”的“affiliate_sales”时 经过 更改表affiliate_stats添加约束fk_affili_sales外键（affiliate_sales）引用事务（_id）；

get me this error Specified key was too long; max key length is 3072 经过tes 我知道两列大小应该相等，但就我而言，我需要不同的大小来处理这个问题。 在许多来源中搜索，没有明确的答案，并且没有一个解决方案适合我。

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

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