时间戳

首页 > 解决方案 > 从 TEXT 字段转到外键

mysql - 从 TEXT 字段转到外键

问题描述

我有这些表：

产品表

CREATE TABLE IF NOT EXISTS product (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    product_name TEXT,
    nutriscore_grade TEXT,
    url TEXT,
    stores TEXT,
    purchase_places TEXT,
    pnns_groups_1 TEXT,
    pnns_groups_2 TEXT
);

和类别表

CREATE TABLE IF NOT EXISTS category (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255)

然后用如下数据填充产品表：

mysql> select id, product_name, pnns_groups_2 from product limit 10;
+----+----------------------------------------------------+----------------------------------+
| id | product_name                                       | pnns_groups_2                    |
+----+----------------------------------------------------+----------------------------------+
|  1 | Prince Chocolat                                    | Biscuits and cakes               |
|  2 | Cocacola Zero                                      | Artificially sweetened beverages |
|  3 | Nutella                                            | Sweets                           |
|  4 | Muesli Raisin, Figue, Abricot                      | Breakfast cereals                |
|  5 | Biscuit Sésame                                     | Biscuits and cakes               |
|  6 | Nutella biscuits                                   | Biscuits and cakes               |
|  7 | 100% mie complet                                   | Bread                            |
|  8 | Soupe 3 poissons aux algues                        | One-dish meals                   |
|  9 | Chocapic                                           | Breakfast cereals                |
| 10 | Nocciolata Pâte à tartiner au cacao et noisettes   | Sweets                           |
+----+----------------------------------------------------+----------------------------------+

和这样的类别表：

mysql> select * from category;
+----+--------------------+
| id | name               |
+----+--------------------+
|  1 | Biscuits and cakes |
|  2 | Breads             |
|  3 | Breakfast cereals  |
|  4 | Sweets             |
|  5 | Cheese             |
+----+--------------------+

这里的目标是将 pnns_groups_2 字段转换为外键以引用它匹配的类别。

显然，这是更好的做法，因为虽然我可以SELECT * WHERE pnns_groups_2 = {my_category}，但如果我使用外键代替它会更好地优化，所以有人告诉我。

但是，我不知道如何继续，我试过了ALTER TABLE product ADD CONSTRAINT fk_pnns_groups_2 FOREIGN KEY product(pnns_groups_2) REFERENCES category(id)，但似乎我的字段不兼容。

然后我尝试使用 更新以匹配类别 ID UPDATE product SET pnns_groups_2 = (SELECT id FROM category WHERE category.name = pnns_groups_2)，然后ALTER TABLE product MODIFY pnns_groups_2 INTEGER;匹配数据类型，但我所有的值都NULL在这样做。

我是 SQL 方面的初学者，欢迎任何建议。

标签： mysqlsql

解决方案

外键应该是被引用表中的主键。我强烈建议您也使用有意义的名称。所以，我希望第一张桌子是：

CREATE TABLE IF NOT EXISTS product (
        id INTEGER PRIMARY KEY AUTO_INCREMENT,
        category_id INTEGER,
        product_name TEXT,
        nutriscore_grade TEXT,
        url TEXT,
        stores TEXT,
        purchase_places TEXT,
        pnns_groups_1 TEXT,
        FOREIGN KEY (category_id) REFERENCES category (ID)
    );

id然后，您将使用或使用 a来过滤特定类别，JOIN以将类别与产品匹配。

推荐阅读