如何在启用 FTS3 的情况下将二进制数据存储在 SQLite 表中？
我正在将应用程序从使用普通 SQLite 数据库转换为启用全文搜索的数据库。它有几个 BLOB 列，用于存储数字签名等内容，显然不需要索引。我读到其他人通过将所有非文本数据移动到一个单独的非 FTS3 表中来解决此问题，必要时将其与 FTS3 表进行内部连接，但这是一个非常不优雅的解决方案。有没有办法简单地从索引中排除某些列？
I'm transitioning an application from using a normal SQLite database to one with full text search enabled. It has several BLOB columns that store things like digital signatures, which obviously do not need to be indexed. I've read that other people solve this by moving all non-TEXT data to a separate, non-FTS3 table that they inner join with the FTS3 table when necessary, but this is a very inelegant solution. Is there no way to simply exclude certain columns from indexing?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这并不不优雅。 SQL 是关系型的，连接是生活中很自然的一部分。认为它们“不优雅”是导致典型的糟糕数据库设计的原因。
您已确定拥有两种不同类型的数据 - 经常需要搜索的文本和不需要搜索的 blob。将它们存储在两个不同的表中绝对没有任何错误或不雅观。
It's not inelegant. SQL is relational, joins are a natural part of life. Thinking they're "inelegant" is what leads to stereotypically poor database designs.
You've determined you have two different kinds of data -- text you often need to search, and blobs you do not. There is absolutely nothing wrong or inelegant about storing these in two different tables.
您可以通过创建连接视图来减少麻烦，然后您可以在其中进行选择。您仍然可以对源自 fts 表的列使用 MATCH，并且不必每次想要查询时都执行该连接。
You can make it less of a hassle by creating a view of the join that you can then select over. You can still use MATCH on the columns that originated in the fts table, and you don't have to do that join every time you want to query.