如何在单个mySQL条目中找到许多可能的模式之一?里面更多 [英] How can I find one of many possible patterns among a single mySQL entry? More inside

问题描述

我很难总结我的问题.基本上:

I had a hard time summing up my question. Basically:

有一个名为文件"的表.文件包含一个名为成绩"的条目.它用于标识文件可能适用的特定等级.因为文件对于大于1年级的文件很有用，所以我存储这样的内容

There's a table called "files". Files holds an entry called "grades". It is used to identify the particular grade level a file might be useful for. Because a file can be useful for > 1 grade level, I store things like this

如果仅适合三年级 年级:3

If it's only good for 3rd grade grades: 3

如果对第三，第四和第五有利: 年级:3,4,5

If it's good for 3rd, 4th and 5th: grades: 3,4,5

等等等

在组合SQL查询以检索这些文件时，我遇到了一个奇怪的问题-基本上，用户可以说我只想要适合2年级和3年级的东西".因此，我应该在成绩"区域中查找具有"2,3"的文件.简单！但是！

When putting together a SQL query to retrieve these files, I ran into a weird issue- Basically a user can say "I only want things that are good for 2nd and 3rd grade". So I should look for files that have "2,3" in the Grades area. Easy! BUT!

它也可以有"1,2,3"或"2,3,4"或"2,4".

It could also have "1,2,3" or "2,3,4" or "2,4".

我只是想着这件事而感到头疼.通过逗号解析这些条目以获取"1"和"2"非常容易，但是将SQL记录与查询匹配的最有效方法是什么?将每个记录都存储在数据库中，将它们解析下来，然后再次进行匹配，这似乎是一种浪费.

I;m getting a headache just thinking about it. It's easy enough to parse those entries via the commas to get "1" and "2", but what's the most efficient way to match a SQL record to the query? It seems like a waste to get EVERY RECORD in the DB, parse them down and then match them up again.

回到上一个方框并为每个年级创建一个名为文件"的数据库和单独的表格是否更好?似乎也很浪费-为一个文件写多个记录.

Is it better to go back to square one and create a DB called "files" and individual tables for each grade? That also seems like a waste- Writing multiple records for one file.

这里的解决方案是什么?我有点糊涂了.

What's the solution here? I'm a little flummoxed.

推荐答案

此处有多个选项...

several options here...

1)将等级存储为整数，其中每个等级对应一位.等级1 =位0，等级2 =位1，等级3 =位2，依此类推.那么1,2,3级将对应于0x00000111(8)，而2,4级将对应于0x00001010(10)等;那么查询就变成了进行AND比较的简单问题...如果您希望选择2级和4级的所有行(可能还选择了其他行)，然后从文件中(级10)== true的文件中选择*

1) store the grades as an integer where each grade corresponds to a bit. grade 1 = bit 0, grade 2 = bit 1, grade 3 = bit 2, and so on. then grades 1,2,3 would correspond to 0x00000111 (8) and grades 2,4 would be 0x00001010 (10) etc; then querying becomes a simple matter of doing an AND comparison... if you want all rows where grades 2 and 4 are selected (and possibly others) then select * from files where (grades & 10) == true

2)如果只有几个等级，则可以将每个等级存储为布尔列.

2) if there are only a relatively few grades you could store each as a boolean column.

3)将成绩存储在单独的表中，然后将成绩和文件之间的关系存储在第3个联接表中(因为这是多对多关系).

3) store the grades in a separate table and then the relationship between grades and files n a 3rd join table (since it is a many to many relationship).

这篇关于如何在单个mySQL条目中找到许多可能的模式之一?里面更多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！