搜索功能实现：模糊查询与全文检索

📂 所属阶段：第四阶段 — 实战演练（道满博客）
🔗 相关章节：SQLAlchemy ORM · 文章发布与 Markdown 支持

1. 基础搜索：SQLAlchemy 模糊查询

1.1 文章搜索

# app/articles/routes.py
from sqlalchemy import or_

@articles_bp.route("/search")
def search():
    query = request.args.get("q", "").strip()
    page = request.args.get("page", 1, type=int)

    if not query:
        return render_template("articles/search.html", results=[], query="")

    # 模糊搜索：标题、摘要、正文
    search_pattern = f"%{query}%"
    pagination = Post.query.filter(
        Post.is_published == True,
        or_(
            Post.title.ilike(search_pattern),
            Post.summary.ilike(search_pattern),
            Post.content.ilike(search_pattern),
        )
    ).order_by(Post.created_at.desc())\
     .paginate(page=page, per_page=20, error_out=False)

    return render_template("articles/search.html",
                           results=pagination.items,
                           query=query,
                           pagination=pagination,
                           total=pagination.total)

1.2 高亮搜索词

# app/utils/search.py
import re

def highlight(text, query, max_length=300):
    """在文本中高亮搜索词"""
    if not text or not query:
        return text

    # 截取包含搜索词的部分
    pattern = re.compile(re.escape(query), re.IGNORECASE)
    match = pattern.search(text)

    if match:
        start = max(0, match.start() - 50)
        end = min(len(text), match.end() + 50)
        excerpt = text[start:end]
        if start > 0:
            excerpt = "..." + excerpt
        if end < len(text):
            excerpt = excerpt + "..."
    else:
        excerpt = text[:max_length]

    # 高亮
    highlighted = pattern.sub(f'<mark>{match.group()}</mark>', excerpt)
    return highlighted
<!-- templates/articles/search.html -->
<h2>搜索结果： "{{ query }}" ({{ total }} 个结果)</h2>

{% for post in results %}
<article class="search-result">
    <h3>
        <a href="{{ url_for('articles.detail', post_id=post.id) }}">
            {{ post.title }}
        </a>
    </h3>
    <p>{{ post.summary or (post.content[:200] + "...") }}</p>
</article>
{% else %}
<p>未找到相关文章。</p>
{% endfor %}

2. 高级搜索：分类 + 时间过滤

@articles_bp.route("/search")
def search():
    query = request.args.get("q", "").strip()
    category_id = request.args.get("category", type=int)
    date_from = request.args.get("from")
    page = request.args.get("page", 1, type=int)

    base_query = Post.query.filter(Post.is_published == True)

    if query:
        search_pattern = f"%{query}%"
        base_query = base_query.filter(
            or_(
                Post.title.ilike(search_pattern),
                Post.summary.ilike(search_pattern),
                Post.content.ilike(search_pattern),
            )
        )

    if category_id:
        base_query = base_query.filter_by(category_id=category_id)

    if date_from:
        from datetime import datetime
        base_query = base_query.filter(
            Post.created_at >= datetime.fromisoformat(date_from)
        )

    pagination = base_query.order_by(Post.created_at.desc())\
        .paginate(page=page, per_page=20, error_out=False)

    return render_template("articles/search.html",
                           pagination=pagination, query=query)

3. 全文检索（PostgreSQL）

# PostgreSQL 全文检索
from sqlalchemy import func, to_tsvector, to_tsquery

@articles_bp.route("/search")
def search():
    query = request.args.get("q", "").strip()
    page = request.args.get("page", 1, type=int)

    if query:
        # PostgreSQL 全文检索
        search_vector = func.to_tsvector("chinese", Post.title) | \
                       func.to_tsvector("chinese", Post.content)

        # 简单分词（实际应使用 jieba 等中文分词）
        ts_query = func.plainto_tsquery("chinese", query)

        pagination = Post.query\
            .filter(
                Post.is_published == True,
                search_vector.op("@@")(ts_query)
            )\
            .order_by(
                func.ts_rank(search_vector, ts_query).desc()
            )\
            .paginate(page=page, per_page=20)

    return render_template("articles/search.html",
                           pagination=pagination, query=query)

4. 小结

# 搜索速查

# 模糊搜索
Post.title.ilike(f"%{query}%")

# 多字段搜索
or_(Post.title.ilike(...), Post.content.ilike(...))

# PostgreSQL 全文检索
to_tsvector("chinese", Post.title).op("@@")(ts_query)
ts_rank(vector, query)  # 排序

💡 生产建议：小型博客用 LIKE 模糊搜索足够。中大型网站推荐 PostgreSQL + 全文索引 + 权重排序。真正大规模搜索用 Elasticsearch。

🔗 扩展阅读