MySQL键值表关联关系及查询方法咨询（附表结构示例）

Alright, let's break this down clearly. First up, we need to iron out the relationships between these three tables—your initial schema is missing a couple of key foreign keys that make this key-value attribute setup work, which is totally normal when you're starting out.

Let's start by defining the logical connections, then add the necessary schema tweaks:

• Attribute ↔ Option (One-to-Many)
  Each attribute (like shape or colour) maps to multiple possible options (e.g., round, square for shape). To make this official, add an attribute_id foreign key column to the Option table—this links every option to its parent attribute.

• Product ↔ Option (Many-to-Many, via a Junction Table)
  A single product can have multiple attribute values (e.g., Product #1 might be round and red), and one option can apply to multiple products. We’ll need a junction table (let’s call it Product_Option) with two foreign keys: product_id (linking to Product) and option_id (linking to Option). This table acts as the bridge between products and their specific attribute options.

If you need to explicitly track both the attribute and its value for a product, you could use a Product_Attribute table with product_id, attribute_id, and option_id instead—but the Product_Option approach is cleaner since options are already tied to their attributes.

Here’s how to update your schema with these keys:

-- Add foreign key to Option table to link it to Attribute
ALTER TABLE Option ADD COLUMN attribute_id INT;
ALTER TABLE Option ADD FOREIGN KEY (attribute_id) REFERENCES Attribute(id);

-- Create the junction table for Product and Option
CREATE TABLE Product_Option (
  product_id INT,
  option_id INT,
  PRIMARY KEY (product_id, option_id), -- Prevent duplicate entries for the same product-option pair
  FOREIGN KEY (product_id) REFERENCES Product(id),
  FOREIGN KEY (option_id) REFERENCES Option(id)
);

Now let’s cover common query scenarios for this setup:

Scenario 1: Get all attributes and their values for a specific product #

If you want to list every attribute and its chosen option for, say, Product #1:

SELECT 
  p.name AS product_name,
  a.title AS attribute_name,
  o.title AS option_value
FROM Product p
JOIN Product_Option po ON p.id = po.product_id
JOIN Option o ON po.option_id = o.id
JOIN Attribute a ON o.attribute_id = a.id
WHERE p.id = 1;

This will return a row for each attribute-value pair for the product, like:

Scenario 2: Pivot key-value pairs into columns (row-to-column) #

If you want a more compact view where each product is a single row, with attributes as columns (great for reports):

SELECT
  p.name AS product_name,
  MAX(CASE WHEN a.title = 'shape' THEN o.title END) AS shape,
  MAX(CASE WHEN a.title = 'colour' THEN o.title END) AS colour,
  MAX(CASE WHEN a.title = 'height' THEN o.title END) AS height,
  MAX(CASE WHEN a.title = 'weight' THEN o.title END) AS weight
FROM Product p
LEFT JOIN Product_Option po ON p.id = po.product_id
LEFT JOIN Option o ON po.option_id = o.id
LEFT JOIN Attribute a ON o.attribute_id = a.id
GROUP BY p.id, p.name;

This will give you a table where each product’s attributes are neatly aligned as columns.

Scenario 3: Find products matching specific attribute values #

Say you want all products that are round in shape:

SELECT DISTINCT p.name
FROM Product p
JOIN Product_Option po ON p.id = po.product_id
JOIN Option o ON po.option_id = o.id
JOIN Attribute a ON o.attribute_id = a.id
WHERE a.title = 'shape' AND o.title = 'round';

The DISTINCT ensures you don’t get duplicate product names if a product matches multiple times (though our junction table’s primary key prevents that, it’s a safe habit).

内容的提问来源于stack exchange，提问作者StefanNch