优化的 MySQL 数据存储 [英] Optimized MySQL data store

问题描述

我想将用户信息存储在一个表中.

I would like to store user information in a table.

第一个例子这些字段是例如.有车"、有公寓"、有猫"、有狗"、有液晶电视"、有笔记本"……

First example The fields are eg. "has car", "has flat", "has cat", "has dog", "has LCD TV", "has notebook"...

字段的值可以是 y/n 或 y/null(null 表示表中为空值).

The value of fields can be y/n or y/null (null means empty value in table).

上面的字段是随机填充的，例如."y", "null", "null", "null", "y", "null"或者"y", "n", "n", "n", "y", "n"

The fields above are filled random, eg. "y", "null", "null", "null", "y", "null" OR "y", "n", "n", "n", "y", "n"

所以只有那些字段包含对我有用的信息，填写y"，因为当我使用查询时，我只想查看那些只包含y"值的字段的结果.

So only those fields contain useful information for me which is filled in "y", because when I use query I just want to see the result of those fields, which contain only "y" value.

这是否有可能，因为显然每条记录的结果都会与其他记录不同?

Is there any possibility for this because obviously each records' result will differ from the others?

第二种方案我创建了一个存储用户 ID 和用户属性的表，例如.

The second solution I create a table which stores the user id and the property of the user, eg.

用户 ID 属性1 有车1 有狗2 有液晶电视3 有平3 有笔记本3有猫

user id property 1 has car 1 has dog 2 has LCD TV 3 has flat 3 has notebook 3 has cat

这里没有存储不必要的信息，但 DB 可以有超过 100000 行.

Here the unnecessary information are not stored but DB can have more than 100000 rows.

我的问题是最好的存储方法是什么.一个用户将使用大约 100-120 个属性.并且将有另一个表连接到原始表，该表也将包含另外 50 个类似的属性(y/null).

My question is what is the best store method. About 100-120 property will be used to one user. And there will be another table which will connect to the original table which also will contain further 50 similar property (y/null).

我认为第二种解决方案是最好的，但我对速度有疑问.当然，主键将被使用，更重要的字段(外键)将被接收唯一".(我听说使用 unique 我们可以更快地收到结果.)

I think the second solution is best but I have doubt with speed. Of course primary key will be used and the more important fields (foreign key) will be received "Unique". (I heard with unique we can received the results more quickly.)

你觉得上面写的是什么?

What do you think of writing above?

提前感谢您的回答.

最好的问候和美好的一天，态度

Best Regards&Nice Day, Atti

推荐答案

从我的角度来看，如果我理解正确的话，这里最好的解决方案是在表 user 之间创建多对多关系(其中包含 user_id 和有关用户的一些信息(如果需要)和第二个表，我们称之为 has ，其中将包含 has_id 和名称(将存储用户拥有的狗、电视、猫等).

From my point of view, if I understand correctly, best solution here is to create many to many relationship between table user (which contain user_id and some info about user if it's required) and second table let's call it has which will contain has_id and name(there will be stored what user have dog, tv, cat, etc.).

要创建多对多关系，您需要第三个表，称为联结表，可以命名为 user_had 并包含字段对 user_id 和 has_id 这将是主键对(这意味着表中不允许有两个相同的对(user_id 和 has_id)).

To create many to many relationship you need third table, called a junction table, which can be named user_had and which contains pair of field user_id and has_id which will be primary key pair (that means that you won't be allowed to have two identical pair (user_id and has_id) in the table).

所以，当你想从表中提取数据时，让我们说 user_id 5 下的用户有什么，你会用这样的东西来做

So when you want to pull out data from table let's say what user under user_id 5 have you will do that with something like this

SELECT user_has.user_id, user_has.has_id, has.name
FROM user_has INNER JOIN has
ON user_has.has_id = has.has_id
WHERE user_has.user_id = 5;

当您插入时，您只需将 user_id 和 has_id 插入 user_has 表中，这将生成新对......等等.

when you inserting you have to insert only user_id and has_id into user_has table which will make new pair... etc..

这篇关于优化的 MySQL 数据存储的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！