如何用mysql存储数据,如何将数据对存储在mysql数据库中？

I have to store a big amount of data pairs. Data pairs are in the form:

parameter = value

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

and another one that have:

p1 = 32

p2 = 36

p5 = 42

p6 = "bye"

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 ;

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;

Adding the data:

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');

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')

and then for each container id get all data:

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

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

(post-processing is just a couple of calls to array_* functions, but it makes slower the processing).

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?

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!!

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.

解决方案

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.

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;

Giving a result something like:

|id |p1 |p2 |p3 |p4 |p5 |p6 |

-------------------------------------------------------

|1 |32 |hello world |-54 | | | |

|2 |32 |36 | | |42 |bye |