如何从论坛数据提取信号名并排除URL中的干扰内容？

我需要从论坛的帖子和主题数据中整理出型号、信号名（格式如PPV_SIGNAL_NAME或PP5_VDDIO_XO这类带下划线的字符串）、特定芯片组等技术术语列表。

最初的MySQL尝试及问题 #

一开始我用MySQL查询直接提取并导出为CSV：

(SELECT DISTINCT TRIM(
    CASE
        WHEN CHAR_LENGTH(REGEXP_SUBSTR(message, '[A-Za-z0-9]{1,7}(_[A-Za-z0-9]{1,7})+')) >= 3
        THEN REGEXP_SUBSTR(message, '[A-Za-z0-9]{1,7}(_[A-Za-z0-9]{1,7})+')
    END
) FROM xf_post)
UNION
(SELECT DISTINCT TRIM(
    CASE
        WHEN CHAR_LENGTH(REGEXP_SUBSTR(title, '[A-Za-z0-9]{1,7}(_[A-Za-z0-9]{1,7})+')) >= 3
        THEN REGEXP_SUBSTR(title, '[A-Za-z0-9]{1,7}(_[A-Za-z0-9]{1,7})+')
    END
) FROM xf_thread)

INTO OUTFILE 'signal_names.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

但这个方案有明显问题：它会把URL里类似pixel_phone_4_sale的带下划线内容误判为信号名。如果用WHEN message NOT LIKE '%https://%'过滤掉含URL的帖子，又会丢失这些帖子里的有效信号（比如PM_PWRMGMT_SLP_R）。由于数据条目超过9000条，手动清理完全不现实，所以改用Python脚本实现。

Python解决方案 #

以下是最终使用的Python脚本，先清理文本（移除URL、HTML/BBCode标签），再用正则提取目标术语并去重：

import pandas as pd
import pymysql
from bs4 import BeautifulSoup
import html
import re

# 移除粗体/斜体标签和URL
def clean_text(text):
    text = re.sub(r'\[/?[bi]\]', '', text)  # 移除BBCode格式标签
    url_pattern = r'http[s]?://(?:[a-zA-Z]|[0-9]|[$-_@.&+]|[!*\\(\\),]|(?:%[0-9a-fA-F][0-9a-fA-F]))+'
    text = re.sub(url_pattern, '', text)    # 移除所有URL
    return text

# 移除HTML标签
def remove_html_tags(text):
    return BeautifulSoup(text, 'html.parser').get_text()

# 解码HTML实体（如&转为&）
def decode_html_entities(text):
    return html.unescape(text)

# 数据库连接参数
db_params = {
    'host': '127.0.0.1',
    'user': 'me',
    'password': 'lookitsme',
    'db': 'randomdb'
}

# 初始化存储各帖子数据的列表
dataframes = []

try:
    # 建立数据库连接
    connection = pymysql.connect(**db_params)

    # 获取所有帖子ID
    thread_ids_query = "SELECT thread_id FROM xf_thread;"
    thread_ids_df = pd.read_sql(thread_ids_query, connection)

    # 遍历每个帖子，提取数据并清洗
    for thread_id in thread_ids_df['thread_id']:
        thread_query = f"""
        SELECT
            p.thread_id,
            t.title,
            p.post_id,
            p.message
        FROM
            xf_post AS p
        JOIN
            xf_thread AS t ON p.thread_id = t.thread_id
        WHERE
            p.thread_id = {thread_id};
        """
        df = pd.read_sql(thread_query, connection)

        # 依次清洗内容和标题
        df['message'] = df['message'].apply(remove_html_tags)
        df['message'] = df['message'].apply(decode_html_entities)
        df['message'] = df['message'].apply(clean_text)
        df['title'] = df['title'].apply(remove_html_tags)
        df['title'] = df['title'].apply(decode_html_entities)
        df['title'] = df['title'].apply(clean_text)

        dataframes.append(df)

finally:
    # 确保数据库连接关闭
    connection.close()

# 用正则查找文本中的匹配项
def find_regex_matches(text, pattern):
    if pd.isna(text):
        return []
    return re.findall(pattern, text)

# 合并所有帖子数据
all_threads_df = pd.concat(dataframes, ignore_index=True)

# 匹配技术术语的正则模式（适配信号名、型号等带下划线的格式）
regex_pattern = r'[A-Za-z0-9]{1,10}(_[A-Za-z0-9]{1,10})+'

# 在标题和内容中提取所有匹配项
titles_matches = all_threads_df['title'].apply(find_regex_matches, pattern=regex_pattern)
messages_matches = all_threads_df['message'].apply(find_regex_matches, pattern=regex_pattern)

# 去重处理
all_matches = set()
for matches_list in titles_matches:
    all_matches.update(matches_list)
for matches_list in messages_matches:
    all_matches.update(matches_list)

# 转换为DataFrame并保存到CSV
distinct_matches_df = pd.DataFrame(list(all_matches), columns=['去重后技术术语'])
distinct_matches_df.to_csv('signal_names.csv', index=False)

print("去重后的技术术语已保存到signal_names.csv")

内容的提问来源于Stack Exchange，提问作者Louis