如何构建 mysql 数据库以便与 sphinx 一起使用？
我正在尝试建立一个可以通过多个方面搜索的产品数据库（例如 newegg 或 amazon）。起初我打算尝试用 mysql 来完成整个事情，但进一步的研究让我相信这是一个坏主意，所以我正在考虑使用 Sphinx。
我的问题是如何为此设置 mysql 表？我是否只需一张用于产品的表，另一张包含所有方面的表，其中只有几个大的 varchar 字段和产品的外键？
I am trying to make a database of products that can be searched by many facets(like newegg or amazon). At first I was going to try to do the whole thing with mysql but further research has led me to believe that is a bad idea so instead I am thinking about using Sphinx.
My question is how would I set up the mysql tables for this? Would I just have one table for the products and another one with all the facets that would just have a couple large varchar fields and foreign key to the product?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我不是 Sphinx 的专家，但我想说你不必将所有数据都放在一张表中。 Sphinx 可以很好地处理关联。如果您计划在前端使用 Rails，那么请看一下thinking_sphinx gem。它绝对允许您根据分布在许多表中的数据来指定属性。根据我的经验，我不必更改数据结构来适应 Sphinx。
I am not a huge Sphinx expert, but I'd say that you don't have to stick all your data in one table. Sphinx can handle associations just fine. If you are planning to use Rails for your front-end then take a look at thinking_sphinx gem. It definitely allows you to specify attributes based on data spread out into many tables. In my experience I didn't have to change my data structure to accommodate Sphinx.
我会插话的。
实际上你并不需要这样做。 Sphinx 中的 Facet 只是 ID（至少在当前稳定版本的 0.9.9 中）。我假设您有一个标准产品表，其中不同的方面存储为其他表的外键。
因此，假设您有这个，您只需选择主产品表并根据文档在 sphinx 中设置方面。
我真的需要查看您的表格结构才能进一步发表评论。听起来您的产品分布在多个桌子上。在这种情况下，正如您提到的，我将使用一个表，您可以在该表上建立索引，并在该表上填充所有其他表的内容。
I'll pipe in.
You don't really need to actually. Facets in Sphinx are just ID's (at least in 0.9.9 the current stable release). I am going to assume that you have a standard product table with your different facets stored as foreign keys to other tables.
So assuming you have this you can just select over the main product table and set up the facets in sphinx as per the documentation.
I would really need to see your table structure to comment further. It sounds like you have your products spread over multiple tables. In this case as you mentioned I would go with a single table which you index on which is populated with the contents of all the others.
Sphinx 的伟大之处在于您可以使用 MySQL 查询将数据导入 Sphinx。这允许您以针对业务逻辑优化的方式构建数据库，而不必担心搜索的执行方式。只要您对为
sql_query编写的查询富有创意，您就可以按照自己的意愿标准化数据库，并且仍然能够通过单个查询获取要索引的所有文本。例如，如果您需要将多对一关系中的字符串获取到索引中，则可以使用子查询来执行此操作。此外，如果您在下拉列表中搜索属性 ID，则可以使用 Sphinx 的
多值属性。这样，您可以按属性 ID 以及属性文本进行搜索。The great thing about Sphinx is that you can use a MySQL query to get your data into Sphinx. This allows you to structure your database in a way that's optimized for your business logic, without having to worry about how search will perform. As long as you're creative with the query you write for
sql_query, you can normalize your database however you'd like, and still be able to grab all the text to be indexed with a single query. For example, if you need to get strings from a many-to-one relationship into your index, you can do so using a subquery.Additionally, if you drop downs where you search on attribute IDs, you use Sphinx's
multi-value attribute. This way, you can search by attribute ID, as well as the text of the attrbute.