在PHP和MySQL中存储和搜索关键字以获取记录的最佳方法？

I haven't touched any code in a good 4-5 months so just getting back into it today, usually takes me a week or so to get all the info flowing through my brain again once I take months off like that. So my project I am about to start will be a PHP/MySQL backend bookmarks database.

我没有在4-5个月内接触到任何代码,所以今天回到它,通常需要一个星期左右的时间才能让我的大脑再次流过我的大脑。所以我即将开始的项目将是一个PHP / MySQL后端书签数据库。

I want to create a nice searchable database with all my favorite websites/bookmarks. Each record will have multiple keywords assigned to it so I can easily search all my bookmarks for the term "php" and all records with "php" in there keyword column or title or otherwise will come back in a result set.

我想用我最喜欢的网站/书签创建一个很好的可搜索数据库。每条记录都会分配多个关键字,因此我可以轻松搜索所有书签中的术语“php”,所有带有“php”的关键字列或标题或其他记录将返回到结果集中。

Here is my idea for the database so far...

到目前为止,这是我对数据库的想法......

auto_id = /*Auto incremented ID number for database*/
name/title = /*Name/title of the Website*/
description = /*brief description of the site*/
URL = /*URL to open when I click a link*/
clicks = /*increments by 1 everytime I click the link*/
date_created = /*datetime that URL bookmark was added*/
date_accessed   = /*datetime field for when last clicked on*/
category = /*category name or number to create a folder like structure of bookmarks in groups*/
sub_category = /*some categories will have subcategories (ie programming->c##  programming->PHP )*/
keywords = /*Keywords used for searching*/

This is pretty straight forward for me on how to build this system all except I am looking for help/advice on the best way to store the keywords. Each website/record I add to the DB can have 1 up to multiple keywords per site. These keywords need to be able to help with the searching part of my app. So how should I store keywords for a site in my database? I know I could just have a "keywords" row in the table and store the keywords for each record like this "php, web, etc, keyword4" so all keywords for each site are saved in 1 column but this does not seem to be the best method when it comes to searching the database.

关于如何构建这个系统,这对我来说非常简单,除了我正在寻找关于存储关键字的最佳方式的帮助/建议。我添加到数据库的每个网站/记录每个站点最多可以包含1个关键字。这些关键字需要能够帮助我的应用程序的搜索部分。那么我应该如何在我的数据库中存储网站的关键字?我知道我可以在表格中有一个“关键字”行并存储每个记录的关键字,如“php,web等,keyword4”,因此每个网站的所有关键字都保存在1列中,但这似乎不是搜索数据库时最好的方法。

Please tell me how you would do this part? Thanks for any help

请告诉我你将如何做这部分?谢谢你的帮助

3 个解决方案

#1

The best way to do this is to create a separate table to contain your keywords and then add an intersection (or join) table to join keywords with bookmarks.

最好的方法是创建一个单独的表来包含您的关键字,然后添加一个交集(或连接)表来连接带有书签的关键字。

CREATE TABLE bookmarks (
  id INT NOT NULL,
  ... etc.
)

CREATE TABLE keywords (
  id INT NOT NULL,
  ... etc.
)

CREATE TABLE bookmark_keywords (
  bookmark_id INT NOT NULL,
  keyword_id INT NOT NULL,
  PRIMARY KEY (bookmark_id, keyword_id),
  FOREIGN KEY bookmark_id REFERENCES bookmarks (id),
  FOREIGN KEY keyword_id REFERENCES keywords (id)
)

When you insert a bookmark, you'd also insert any keywords that are being used and aren't already in the keywords table, as well as a row in bookmark_keywords in order to join the keyword with the bookmark.

当您插入书签时,您还会插入正在使用但尚未在关键字表中的任何关键字,以及bookmark_keywords中的一行,以便将关键字与书签结合起来。

Then, when you want to query for what keywords a bookmark has:

然后,当您要查询书签具有哪些关键字时:

SELECT k.*
FROM keywords AS k
LEFT JOIN bookmark_keywords AS kb
  ON kb.keyword_id = k.id
WHERE kb.bookmark_id = [ID of the bookmark]

And to query for what bookmarks share a particular keyword:

并查询共享特定关键字的书签:

SELECT b.*
FROM bookmarks AS b
LEFT JOIN bookmark_keywords AS kb
  ON kb.bookmark_id = b.id
WHERE kb.keyword_id = [ID of the keyword]

#2

You're right, storing a comma-separated list in one column is not a good way to do it (this is called a repeating group and it violates the First Normal Form of relational database design).

你说的没错,在一列中存储一个逗号分隔的列表是不这样做(这被称为重复组和它违反了关系数据库设计的第一范式)的好方法。

Using a LIKE predicate is not a good choice, because it cannot benefit from an index. Searching for keywords this way is hundreds or thousands of times slower than designing a proper database in normal form, and adding indexes.

使用LIKE谓词不是一个好的选择,因为它不能从索引中受益。以这种方式搜索关键字比以正常形式设计正确的数据库并添加索引要慢几百或几千倍。

You need to store a second table listing keywords, and a third many-to-many table to pair keywords to applicable bookmarks. This is a pretty standard design for "tagging" in a relational database.

您需要存储列出关键字的第二个表,以及第三个多对多表,以将关键字与适用的书签配对。这是在关系数据库中“标记”的非常标准的设计。

In non-relational databases like CouchDB or MongoDB, you can make one field a set of keywords, and index them so queries can be efficient. But not in a relational database.

在像CouchDB或MongoDB这样的非关系型数据库中,您可以将一个字段设为一组关键字,并对其进行索引,以便查询可以高效。但不是在关系数据库中。

#3

The easiest, and fastest, search technique to implement is the use of MySQL's LIKE statement. LIKE lets you search through a column for a specific string. Consider the following example...

要实现的最简单,最快速的搜索技术是使用MySQL的LIKE语句。 LIKE允许您在列中搜索特定字符串。考虑以下示例......

auto_id    name            description
1          Cool PHP Site   you know you love it  
2          PLARP! its Ruby gems gems gems!  
3          SqlWha          sql for the masses  
4          FuzzD00dle      fun in the sun, with some fuzz

You could find all rows that contain the string 'php' in either the 'name' or 'description' field using the following query...

您可以使用以下查询在“名称”或“描述”字段中找到包含字符串“php”的所有行...

SELECT * FROM bookmarks WHERE name LIKE '%php%' OR description LIKE '%php%';

'%' is a wildcard character.

'%'是一个通配符。

Reference on MySQL LIKE: http://www.tutorialspoint.com/mysql/mysql-like-clause.htm

参考MySQL LIKE:http://www.tutorialspoint.com/mysql/mysql-like-clause.htm

You could also add a 'keywords' column and store the keywords in a comma delimited format (ie: plarp1, plarp2, plarp3), then search through that.

您还可以添加“关键字”列并以逗号分隔格式存储关键字(即:plarp1,plarp2,plarp3),然后搜索。

#1