Home / Questions / 88076
Accepted
user52024
Asked: 2020-07-20 10:49:53 +0800 CST 2020-07-20 10:49:53 +0800 CST

MySQL中的表关系

  • 772

我对mysql中的关系有一个大问题。你看，我正在创建一个看起来像电子商务的网站，它是为同事服务的。

我正在创建一个名为categories的表和另一个名为subcategories的表。在子类别表中，我有一个名为 的列categoria_id，其中包含所选类别的 ID。然后像这样，我将能够显示子类别及其各自的类别。

一个例子：手机配件（类别），充电器（子类别）等。

是否需要在类别 ID 和“子类别”表的 category_id 之间创建关系？我希望我已经正确地解释了自己。

问候。

php

4 Answers

  1. Best Answer

    如果你的意思是强制的，答案是否定的，也就是说，你可以创建两个表，虽然它们共享信息，但它们之间没有任何关系。

    但是，非常建议您在两者之间建立关系并注意数据的引用完整性，因为否则在技术上可以从没有现有类别的子类别中插入数据。

    我将向您传递有关Foreign Keys的 MySQL 文档。

    编辑：我给你一个例子，它会阻止你有一个没有父类别的子类别。

    CREATE TABLE categoria (
    id INT NOT NULL,
    nom_categoria VARCHAR(15) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE sub_categoria(
    id INT,
    nom_subcategoria VARCHAR(15) NOT NULL,
    categoria_id INT,
    INDEX par_ind (categoria_id),
    FOREIGN KEY (categoria_id)
        REFERENCES categoria(id)
        ON DELETE CASCADE
) ENGINE=INNODB;
    • 13

  2. 新答案：更简单的解决方案

    事实是，@PatricioMoracho 的回答让我感到惊讶，这帮助我重新考虑了一个更简单的解决方案来理解和维护问题。您的回答让我看到了以前模型的一个重要限制：嵌套类别和子类别的可能性！

    有趣的是，考虑到这种复杂性可以让我们以一种非常简单的方式解决问题。生活中经常发生，复杂的事情让我们看到有更简单的方法。

    让我们来看看：

    实际上，您可以有一个表categorias，我们将在其中添加一个名为 的新列padre_id。在该列中，我们将放置每个类别的父级的 id。如果它是父类别，我们将该值设置为零 ( 0)。如果我们愿意，这将帮助我们仅恢复主要类别......

    该代码提供了一个简单的查询，它将显示每个类别及其旁边的子项。

    显然，可以进行更复杂的查询，例如按级别显示每个类别及其子类别的查询，或者通过其父类别到达祖父类别的代码。

    Código: Ver Demo

    CREATE TABLE  IF NOT EXISTS categorias 
    (
      categoria_id          INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
      categoria_nombre      VARCHAR(50) NOT NULL,
      padre_id              INT NOT NULL

    );


INSERT INTO categorias (categoria_nombre, padre_id) 
            VALUES  
            ('Teléfonos',0),
            ('Celulares',1),
            ('Accesorios para celulares',2),
            ('Baterías',3),
            ('Protectores',3),
            ('Manos libres',3),
            ('Computadoras',0),
            ('Computadoras de Escritorio',7),
            ('Computadoras Portátiles',7),
            ('Teclado',8),
            ('Monitor',8),
            ('Mouse',8),                
            ('Funda de portátil',9);                

SELECT c.categoria_nombre as padre, 
       GROUP_CONCAT(hijos.categoria_nombre SEPARATOR ",") AS hijos     
       FROM categorias c
       INNER JOIN categorias hijos 
          ON c.categoria_id=hijos.padre_id    
       GROUP BY hijos.padre_id;

    Resultado:

        padre                        hijos
1   Teléfonos                    Celulares
2   Celulares                    Accesorios para celulares
3   Accesorios para celulares    Baterías,Protectores,Manos libres
4   Computadoras                 Computadoras de Escritorio,Computadoras Portátiles
5   Computadoras de Escritorio   Teclado,Monitor,Mouse
6   Computadoras Portátiles      Funda de portátil

    @deprecated 这是一个旧答案

    在这些情况下通常做的事情是这样的。

    注意：这是一个基本示例。有必要在表创建中添加外键、引用完整性、索引，具体取决于数据库的类型（InnoDB、MyISAM）。

    由于您刚刚开始，并且有些人质疑我没有在回答中提出任何有关参照完整性的内容，因此我想告诉您，在继续之前，确定 您的表将具有的引擎类型很重要.

    在 MySQL 中有两个：MyISAMe InnoDB。两者都有其优点和缺点。例如这篇文章（和其他文章）可以指导你：MyISAM 还是 InnoDB？选择您的 MySQL 存储引擎

    也可以查阅MySQL的官方文档（英文）：

    该代码提供了一个基本示例和一个查询，如果您想获取类别 x 的 sub_categories，它将起作用。

    如果您决定这样做，我InnoDB建议您阅读所述引擎的文档，以及表创建语法的工作原理、主键、引用、on delete...on update等的指示方式。这是一个比建立简单关系更广泛的话题。取决于您如何配置它们on delete ，例如，删除父级时将删除子级数据，以免留下孤立数据。在实施它之前，您需要了解它是如何工作的......

    Código: Ver Demo

    /*
-- ATENCIÓN NO USE DROP TABLE CON SUS TABLAS REALES
-- YA QUE DROP TABLE BORRARÁ SUS DATOS
-- DROP TABLE SE USA AQUÍ SÓLO PARA PODER PROBAR LOS DATOS */ 

DROP TABLE IF EXISTS categorias;
DROP TABLE IF EXISTS sub_categorias;



/* NO COPIE LA SENTENCIA ^ DROP TABLE ^ EN SUS DATOS REALES */



CREATE TABLE  IF NOT EXISTS categorias 
    (
      categoria_id          INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
      categoria_nombre      VARCHAR(50) NOT NULL
    );


CREATE TABLE  IF NOT EXISTS sub_categorias 
    (
      subcategoria_id          INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
      subcategoria_nombre      VARCHAR(50) NOT NULL,
      categoria_id             INT (10)  
    );



INSERT INTO categorias (categoria_nombre) 
            VALUES  ('Accesorios para celulares'), ('Accesorios PC');

INSERT INTO sub_categorias (subcategoria_nombre,categoria_id) 
            VALUES  
            ('Baterías', 1),
            ('Protectores', 1),
            ('Manos libres', 1),
            ('Teclado', 2),
            ('Monitor', 2),
            ('Mouse', 2)                
            ;   


SELECT  subcategoria_nombre FROM  sub_categorias sc
INNER JOIN categorias c 
ON sc.categoria_id=c.categoria_id
WHERE sc.categoria_id=1;

    Resultado

        subcategoria_nombre
1   Baterías
2   Protectores
3   Manos libres
    • 7

  3. Juance，您的问题已经得到解答，A. Cedano 和 David Isla 的回答都是有效的。问题是我注意到你自己提出的模型有一个限制，即类别/子类别模型，我问过你这个问题。基本上你只能管理两个级别，你不能从另一个子类别中“挂起”一个子类别，通常只有两个级别的编目通常是不够的，只要看看 MercadoLibre 或其他网站。我的建议是不加区别地处理单个表categorias，这在某种程度上是合理的，因为它们都是类别，而变化的是我们希望如何查看它们的层次结构，这将由一个属性处理nodo. 如果在任何时候我们需要移动一些东西或者直接为所有类别创建一个新的关卡，这个模型更加灵活。它有一个重要的复杂性：您必须注意更新nodo，它并不复杂，但它有其逻辑。

    我稍微扩展了 A.Cedano 的示例，以便您可以看到它：

    DROP TABLE IF EXISTS categorias;

CREATE TABLE  IF NOT EXISTS categorias 
    (
      categoria_id          INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
      nodo                  VARCHAR(40),
      categoria_nombre      VARCHAR(40) NOT NULL
    );


INSERT INTO categorias (categoria_nombre, nodo) 
    VALUES  ('Categorias de Productos', '0001'), 
            ('Accesorios para celulares', '00010001'), 
            ('Celulares', '00010002'),
            ('Accesorios PC', '00010003'),
            ('Baterías', '000100010001'),
            ('Protectores', '000100010002'),
            ('Plásticos', '0001000100020001'),
            ('Vidrio', '0001000100020001'),
            ('Manos libres', '000100010003'),
            ('Teclado', '000100020001'),
            ('Monitor', '000100020001'),
            ('Mouse', '000100020001'),                
            ('Gama Alta', '000100020001'),
            ('Iphone 6', '0001000200010001'),
            ('Samsung S8', '0001000200010002'),
            ('Gama Media', '000100020002'),
            ('Gama Baja', '000100020002')
            ;   


SELECT  c1.nodo, 
        CONCAT(SPACE(LENGTH(c1.nodo)-LENGTH(c.nodo)),  c1.categoria_nombre) AS 'categoria_nombre'
    FROM categorias c
    INNER JOIN categorias c1
        ON LEFT(c1.nodo, LENGTH(c.nodo)) = c.nodo
    WHERE c.categoria_id = 1
    ORDER BY c1.nodo

    输出将是这样的：

    nodo             categoria_nombre
================ =====================================
0001             Categorias de Productos
00010001             Accesorios para celulares
000100010001             Baterías
000100010002             Protectores
0001000100020001             Plásticos
0001000100020001             Vidrio
000100010003             Manos libres
00010002             Celulares
000100020001             Gama Alta
0001000200010001             Iphone 6
0001000200010002             Samsung S8
000100020002             Gama Media
000100020002             Gama Baja
00010003             Accesorios PC
000100030001             Teclado
000100030001             Monitor
000100030001             Mouse

    缩进只是为了可视化层次结构。每个产品都有一个categoria_id相关的表categorias，该表将指向树中的预定义级别。搜索某个类别以下的产品非常容易实现，例如，如果您想要所有手机配件，您只需指明categoria_id = 2. 如果您最终要为其创建 ABM，则维护nodo并不复杂，但您必须小心，如果通过脚本完成维护，这样的结构会更容易。

    在我提出的示例中，每个分支或节点最多可以容纳 9999 个类别和最多 10 个级别，但这很容易通过修改字段nodo和更新逻辑来扩展。一个更高级的模型是管理一些额外的表jerarquia并categoria_jerarquia最终管理依赖于某些标准的不同层次模型，例如“简单类别”或“扩展类别”，但我不想让你的生活复杂化，也许这个模型会帮助你。

    附加信息：我们如何添加更多类别？

    首先，我建议在第一级基础上保留第一个主类别，在“产品类别”示例中，任何新项目都必须始终位于其下方，因为稍后处理插入的逻辑更容易。我们来看一个例子：假设我们要添加一个新的移动配件“充电器”，我们该怎么做？

    • 首先，您必须确定父类别将是什么，在本例中它将是“手机配件”，在我放在一起的查询中，我没有显示 ID，但所有类别都有一个 id，附件的那个是 2 ，执行以下操作我们将获得“父”节点及其长度

      SELECT nodo AS NodoPadre,
        LENGTH(NODO) As LongitudNodoPadre
        FROM categorias
        WHERE categoria_id = 2 ;

    • “父”节点是00010001新项目，我们将把它插入到这个下面和那个分支的类别的末尾，所以我们需要计算最终节点，就像这样00010001????，精确计算部分的，????我们只需要查看它是该分支的最后一个节点并添加一个到它，如下所示：

      SELECT  CONCAT('00010001',RIGHT(CAST((10000 + CAST(MAX(right(nodo,4)) AS UNSIGNED ) + 1) AS CHAR(5)),4))
   FROM categorias
    WHERE nodo LIKE '00010001%'
          AND LENGTH(nodo) = 12;

    • 我们看到了父节点下方的分支，这是因为它LIKE '00010001%'，也是因为我们验证节点的长度是 4 + 使用父节点的长度LENGTH(nodo) = 12。结果将是：000100010004，如果您看到分支：

      nodo            categoria_nombre
=============== ===================
000100010001    Baterías
000100010002    Protectores
000100010003    Manos libres

      很明显，我们必须将新类别插入为：

      INSERT INTO categorias (categoria_nombre, nodo) 
        VALUES  ('Cargadores', '000100010004'),

    使用这种模型，插入总是在分支的最后一级，最终可以建立逻辑来对项目进行顺序重新排序，使项目之间的节点“交换”，对于delete，它们是方便的逻辑, 例如使用FlagHabilitado(1/0) 但最终它们也可能是物理的。

    • 4

  4. 在某种程度上，对于您提出的结构，您已经在类别和子类别之间建立了关系，但这只是一个参考，它实际上并不存在于数据库中。

    我认为在没有物理关系的情况下使用是危险的，因为数据库不会管理integridad referencial，也不会为您声明的该字段创建索引concepto_id，并且将来如果您将该列用作查询和更新操作，它可能会在查询和更新操作中产生性能问题一个标准。

    简而言之，这不是必需的，但是您承担了必须从编程中涵盖的不必要风险。

    我建议阅读所有内容之前的llaves primarias内容llaves foráneas以更好地理解。

    • 0