php - 如何在mysql数据库中存储数据对？

Question

我必须存储大量数据对。数据对的形式为:

parameter = value

并且与容器相关。它类似于 INI 文件。例如，我有一个具有这些值的容器:

p1 = 32
p2 = "hello world"
p3 = -54

还有一个有:

p1 = 32
p2 = 36
p5 = 42
p6 = "bye"

每个容器的参数数量未定义。这些值是任意长度的数字或字符串(数字可以转换为字符串)。数据检索是通过参数名称(“所有 p1 值”)或值(“具有值 'bye' 的 'p6' 参数”)进行的。该数据库将包含数百万对。插入和读取将非常频繁地进行，并且很少删除或更新记录。

我的第一次尝试是制作两个表格。第一个像这样:

CREATE TABLE IF NOT EXISTS `container` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  OTHER CONTAINER INFO
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

其中 id 将在另一个包含对的表中使用:

CREATE TABLE IF NOT EXISTS `data` (
  `container` int(11) NOT NULL,
  `name` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `value` text COLLATE utf8_unicode_ci NOT NULL,
  KEY `container` (`container`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

添加数据:

INSERT INTO `container` (`id`) VALUES ('1'), ('2');
INSERT INTO  `data` (`container`, `name`, `value`) VALUES 
     ('1',  'p1',  '32'), 
     ('1',  'p2',  'hello world'), 
     ('1',  'p3',  '-54'), 
     ('2',  'p1',  '32'), 
     ('2',  'p2',  '36'), 
     ('2',  'p5',  '42'), 
     ('2',  'p6',  'bye');

它可以工作，但不是很“可用”且速度不快。例如，如果我需要从每个具有 p1=32 的容器中查找所有参数，我必须首先进行两个 SQL 查询:

SELECT id FROM  `container` WHERE id IN (
   SELECT container
   FROM DATA WHERE name =  'p1'
  AND value =  '32')

然后为每个容器 id 获取所有数据:

SELECT * FROM  `data` WHERE container = '$id'

然后通过 PHP 进行后处理，因为它返回的表格如下:

container   name    value
    1       p1      32
    1       p2      hello world
    1       p3      -54

(后处理只是对 array_* 函数的几次调用，但它会使处理速度变慢)。

我一直在测试包含 50000 个数据对的数据库，使用此过程列出所有记录需要 5 秒以上的时间。问题是:如何存储这种数据？或者如何提高我的实现性能？

注意:问题不在于第一个 SQL 查询(0'003 秒)或第二个(每个 0'002 秒)。题目是对第一个SQL语句的每一个结果做一次SQL查询!!

注意 2:目标是拥有一个包含每个结果的 PHP 数组，并为每个结果创建一个包含键值对的关联数组。 print_r 的结果:

Array
(
    [1] => Array
        (
            [p1] => 32
            [p2] => hello world
            [p3] => -54
        )
    [2] => Array
        (
            [p1] => 32
            [p2] => 36
            [p5] => 42
            [p6] => bye
        )
)

当然，我在这里展示的数据结构是我的第一次尝试，我正在研究，那不是生产，所以我可以改变，一点问题都没有。

Answer 1

你不需要两个单独的查询，一个连接在那里工作得很好；也不需要子选择。你的两个查询变成这样:

SELECT id FROM  `container` c 
JOIN data d ON c.id = d.container
d.name =  'p1'
AND d.value =  '32';

好的，我看到了你的笔记，你想要同一行中的所有数据吗？我不会使用此查询，但这些是您想要的结果吗？

SELECT id, GROUP_CONCAT(CONCAT_WS("=", d.name, d.value)) results FROM  `container` c 
JOIN data d ON c.id = d.container
HAVING LOCATE("p1=32", results) > 0;