在MySQL列中存储整数数组最有效的方法是什么?

如果你要同时按森林和植物进行搜索,听起来你会从一个完整的多对多关系中受益。丢掉你的饭碗 plants 列,并创建一个全新的 areas_plants 表(或者你想叫它什么)来关联这两个表。

工厂区域 表如下所示:

area_id | plant_id | sort_idx
-----------------------------
      1 |        1 |     0
      1 |        2 |     1
      2 |        2 |     0
      2 |        3 |     1

然后可以从任意一方查找关系,并使用简单联接从任意一个表中获取相关数据。不需要在类似的条件下胡闹来弄清楚它是否在名单上,诸如此类。我在那里是为了一个遗留数据库。没有乐趣。最大限度地利用SQL。

表:植物

plant_ID | name
1        | tree
2        | shrubbery
20       | notashrubbery

表:面积

area_ID | name
1       | forest

表:厂区地图

area_ID | plant_ID | sequence
1       | 1        | 0
1       | 2        | 1
1       | 20       | 2

要查找所有具有灌木林(植物2)的区域,请执行以下操作:

SELECT *
FROM areas
INNER JOIN area_plant_map ON areas.area_ID = area_plant_map.area_ID
WHERE plant_ID = 2

通常,一个表会有一个areaplants表:area\u ID,plant\u ID,这两个表上有一个唯一的约束和其他两个表的外键。这个“链接”表提供了多对多或多对一的关系。

您的关系属性应该是原子的,而不是像列表那样由多个值组成。搜索它们太难了。您需要一个新的关系,将植物映射到area\u ID,area\u ID/植物组合是主键。

使用多对多关系:

CREATE TABLE plant (
    plant_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
) ENGINE=INNODB;

CREATE TABLE area (
    area_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
) ENGINE=INNODB;

CREATE TABLE plant_area_xref (
    plant_id INT NOT NULL,
    area_id INT NOT NULL,
    sort_idx INT NOT NULL,
    FOREIGN KEY (plant_id) REFERENCES plant(plant_id) ON DELETE CASCADE,
    FOREIGN KEY (area_id) REFERENCES area(area_id) ON DELETE CASCADE,
    PRIMARY KEY  (plant_id, area_id, sort_idx)    
) ENGINE=INNODB;

Bonus Question Is it time to step away from MySQL? Is there a better DB to manage this?

这与MySQL无关。这只是一个糟糕的数据库设计问题。在每个RDBMS(MySQL、Oracle、MSSQL、PostgreSQL等)中,都应该使用交集表和多对多关系。

在提出这个问题8年后,这里有两个想法:

(link)

2使用您自己的编码

area_id 输入一个字符串字段(varchar或text,您的选择,考虑性能),然后您可以表示值,例如 -21-30-2-4-20- %-2-% .

如果您尝试其中一种方法,我希望您能分享您的性能结果,并按照您的建议使用100万行。

记住,不要使用任何一种方法来打破规范化的第一条规则,即 every column should hold a single value