在 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<外鍵（affiliate_sales）引用事務（_id

get me this error Specified key was too 長; 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 行中的每個affiliate_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

一開始有點困難，但非常強大。這就是關係資料庫的工作原理。