如何将数据对存储在mysql数据库中? [英] How to store data pairs in a mysql database?
问题描述
我必须存储大量数据对.数据对的格式为:
I have to store a big amount of data pairs. Data pairs are in the form:
参数=值
并与一个容器有关.它类似于INI文件.例如，我有一个具有以下值的容器:
and are related to a container. It's similar to a INI file. For example, I have a container that have these values:
p1 = 32
p2 = "hello world"
p3 = -54
和另外一个具有:
p1 = 32
p2 = 36
p5 = 42
p6 = "bye"
每个容器中参数的数量不确定.值是数字或任意长度的字符串(数字可以转换为字符串).数据检索是通过参数名称(所有p1值" )或值(具有值'bye''的'p6'参数" )进行的.该数据库将包含数百万对.插入和读取操作非常频繁，并且很少删除或更新记录.
There is an undefined number of parameters per container. The values are numbers or strings of any length (numbers can be converted to strings). Data retrieval is made by parameter name ("all p1 values") or value ("a 'p6' parameter with the value 'bye'"). The database will contain milions of pairs. Inserts and reads will be made very often and will be rare to delete or update a record.
我的第一个尝试是制作两个表.第一个是这样的:
My first attempt has been make two tables. First one like this:
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将在包含该对的另一个表中使用:
where id will be used in another table that contains the pairs:
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查询，首先:
It works, but it's not very "usable" and fast. For instance, if I need to find all parameters from each container that has a p1=32 I have to make two SQL queries, first:
SELECT id FROM `container` WHERE id IN (
SELECT container
FROM DATA WHERE name = 'p1'
AND value = '32')
，然后为每个容器id获取所有数据:
and then for each container id get all data:
SELECT * FROM `data` WHERE container = '$id'
，然后通过PHP进行后处理，因为它返回了一个像这样的表:
and then make a post-processing via PHP because it returns a table like this:
container name value
1 p1 32
1 p2 hello world
1 p3 -54
(后处理只是对array_ *函数的几个调用，但它会使处理速度变慢).
(post-processing is just a couple of calls to array_* functions, but it makes slower the processing).
我一直在测试包含50000个数据对的数据库，并使用此过程列出所有记录需要5秒钟以上.问题是:如何存储这种数据?或如何提高实施效果?
I've been testing with a database that contains 50000 data pairs and listing all the records using this procedure takes more than 5 seconds. The question is: how to store this kind of data? or how to improve the performance of my implementation?
注意:问题不是第一个SQL查询(0'003秒)或第二个SQL查询(每个0'002秒).问题是要对第一个SQL语句的每个结果进行SQL查询！
NOTE: The problem is not the first SQL query (0'003 seconds) or the second one (0'002 seconds each one). The problem is to make a SQL query for each result of the first SQL sentence!!
注意2 :目标是拥有一个包含每个结果的PHP数组，并为每个结果提供一个具有键值对的关联数组. print_r:
NOTE 2: The goal is to have a PHP array that contains each result and for each result an associative array with pairs key-value. The result for a print_r:
Array
(
[1] => Array
(
[p1] => 32
[p2] => hello world
[p3] => -54
)
[2] => Array
(
[p1] => 32
[p2] => 36
[p5] => 42
[p6] => bye
)
)
当然，我在这里显示的数据结构是我的第一次尝试，我正在研究，它不是生产产品，因此我可以进行更改，完全没有问题.
And, of course, the data structure I have shown here is my first attempt, I'm researching, that's not a production, so I can change, no problem at all.
推荐答案
如果每个容器的所有结果都在一行上，则可能需要使用数据透视表.但是，鉴于您可能有可变数量的data.name值，您不能仅在单个简单查询中执行此操作.您需要编写一个proc来动态生成sql.
If you want all the results per container on one line, you probably need to use a pivot table. However, given that you have potentially got a variable number of data.name values, you can't just do this in a single simple query. You'll need to write a proc to dynamically generate the sql.
此链接应为您提供帮助: http://www.artfulsoftware.com/infotree/queries.php#78
This link should help you out: http://www.artfulsoftware.com/infotree/queries.php#78
生成的SQL应该看起来像(未经测试的)
The resulting SQL should look something like (untested)
SELECT
c.id,
GROUP_CONCAT(if(d.name = 'p1', d.value, NULL)) AS 'p1',
GROUP_CONCAT(if(d.name = 'p2', d.value, NULL)) AS 'p2',
GROUP_CONCAT(if(d.name = 'p3', d.value, NULL)) AS 'p3',
GROUP_CONCAT(if(d.name = 'p4', d.value, NULL)) AS 'p4',
GROUP_CONCAT(if(d.name = 'p5', d.value, NULL)) AS 'p5',
GROUP_CONCAT(if(d.name = 'p6', d.value, NULL)) AS 'p6',
FROM container c
JOIN data d ON c.id = d.container
GROUP BY c.id;
给出类似这样的结果
|id |p1 |p2 |p3 |p4 |p5 |p6 |
-------------------------------------------------------
|1 |32 |hello world |-54 | | | |
|2 |32 |36 | | |42 |bye |
这篇关于如何将数据对存储在mysql数据库中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
