mysql - 在mysql表中存储和查询json

Question

我试图在必须允许用户创建、修改和删除表字段的项目中避免反模式。所以我正在寻找在表中存储 JSON 数据。例如我有表产品:

Products
----------------
id,
user_id,
created,
modified,
price,
_custom <-- there will be stored additional columns for each product needs
        {"adjustment":0.13, "weight":14.60, "have_some_individual_label":"value"}

但我看不出如何在查询中包含 _column 参数。例如，如何查询所有 user_id = 1 AND have_some_individual_label = value 的产品。第二个参数可以是一个或多个(它将用于过滤器和分析)。如果这是不好的方法 - 什么会更好？谢谢!

Answer 1

If this is bad approach - what would be better one?

Entity–attribute–value model型号:

CREATE TABLE `ProductInfo` (
  `ProductID` BIGINT UNSIGNED NOT NULL,
  `AttributeKey` VARCHAR(20) NOT NULL,
  `AttributeVal` VARCHAR(20),
  PRIMARY KEY (`ProductID`, `AttributeKey`),
  FOREIGN KEY (`ProductID`) REFERENCES `Products` (`id`)
);

INSERT INTO ProductInfo
  (`ProductID`, `AttributeKey`              , `AttributeVal`)
VALUES
  (         1 , 'adjustment'                ,         '0.13'),
  (         1 , 'weight'                    ,        '14.60'),
  (         1 , 'have_some_individual_label',        'value')
;