MySQL设计带有动态数量的字段 [英] MySQL design with dynamic number of fields

问题描述

我对MySQL的经验非常基础。简单的东西很容易，但我碰到了一些需要更多知识的东西。我需要一个表存储一个小列表的单词。存储的字的数量可以在1到15之间的任何地方。后来，我打算通过这些词搜索表。我已经考虑了几种不同的方法：

My experience with MySQL is very basic. The simple stuff is easy enough, but I ran into something that is going to require a little more knowledge. I have a need for a table that stores a small list of words. The number of words stored could be anywhere between 1 to 15. Later, I plan on searching through the table by these words. I have thought about a few different methods:

A。）我可以创建15个字段的数据库，只要数据小于我真的不喜欢这样。这似乎真的效率低下。

A.) I could create the database with 15 fields, and just fill the fields with null values whenever the data is smaller than 15. I don't really like this. It seems really inefficient.

B。）另一个选项是仅使用一个字段，并将数据存储为逗号分隔列表。每当我回来搜索，我只是在场上运行一个正则表达式。再次，这似乎真的效率低下。

B.) Another option is to use just a single field, and store the data as a comma separated list. Whenever I come back to search, I would just run a regular expression on the field. Again, this seems really inefficient.

我希望有一个很好的选择这两个选项。任何建议都将非常感激。

I would hope there is a good alternative to those two options. Any advice would be very appreciated.

- 谢谢

推荐答案

C ）使用正常表单;请使用多个具有相应键的行。示例：

C) use a normal form; use multiple rows with appropriate keys. an example:

mysql> SELECT * FROM blah;
+----+-----+-----------+
| K  | grp | name      |
+----+-----+-----------+
|  1 |   1 | foo       |
|  2 |   1 | bar       |
|  3 |   2 | hydrogen  |
|  4 |   4 | dasher    |
|  5 |   2 | helium    |
|  6 |   2 | lithium   |
|  7 |   4 | dancer    |
|  8 |   3 | winken    |
|  9 |   4 | prancer   |
| 10 |   2 | beryllium |
| 11 |   1 | baz       |
| 12 |   3 | blinken   |
| 13 |   4 | vixen     |
| 14 |   1 | quux      |
| 15 |   4 | comet     |
| 16 |   2 | boron     |
| 17 |   4 | cupid     |
| 18 |   4 | donner    |
| 19 |   4 | blitzen   |
| 20 |   3 | nod       |
| 21 |   4 | rudolph   |
+----+-----+-----------+
21 rows in set (0.00 sec)

这是我在其他问题中发布的表格 group_concat 。你会注意到，每一行都有一个唯一的键 K 。还有另一个键 grp ，代表每个类别。剩余字段表示类别成员，并且每个类别可以有可变数字。

This is the table I posted in this other question about group_concat. You'll note that there is a unique key K for every row. There is another key grp which represents each category. The remaining field represents a category member, and there can be variable numbers of these per category.

这篇关于MySQL设计带有动态数量的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！