如何用Python获取MySQL表中出现最频繁的词

1. 先搞定MySQL的词频统计查询 #

你的genre列是逗号加空格分隔的多类型字符串，需要先拆分每个类型，再统计出现次数。根据你的MySQL版本选择对应语句：

适用于MySQL 8.0及以上（支持递归CTE） #

用递归方式拆分所有类型，再分组统计：

WITH RECURSIVE genre_split AS (
    SELECT 
        TRIM(SUBSTRING_INDEX(genre, ',', 1)) AS genre_word,
        TRIM(SUBSTRING(genre, LOCATE(',', genre) + 1)) AS remaining_genres
    FROM your_table_name
    WHERE genre IS NOT NULL AND genre != ''
    UNION ALL
    SELECT 
        TRIM(SUBSTRING_INDEX(remaining_genres, ',', 1)) AS genre_word,
        TRIM(SUBSTRING(remaining_genres, LOCATE(',', remaining_genres) + 1)) AS remaining_genres
    FROM genre_split
    WHERE remaining_genres IS NOT NULL AND remaining_genres != ''
)
SELECT genre_word, COUNT(*) AS count
FROM genre_split
GROUP BY genre_word
ORDER BY count DESC
LIMIT 1;

适用于MySQL 5.x版本（无递归CTE支持） #

用数字辅助表拆分，这里按最多3个类型设计，若你的数据有更多类型，可扩展数字表的条目：

SELECT 
    TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(t.genre, ',', n.n), ',', -1)) AS genre_word,
    COUNT(*) AS count
FROM your_table_name t
CROSS JOIN (
    SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3
) n
WHERE t.genre IS NOT NULL AND t.genre != ''
AND n.n <= LENGTH(t.genre) - LENGTH(REPLACE(t.genre, ',', '')) + 1
GROUP BY genre_word
ORDER BY count DESC
LIMIT 1;

2. 结合Python代码实现变量赋值 #

把上面的查询语句嵌入你的数据库连接代码，完整实现如下：

import mysql.connector

# 替换成你的数据库实际配置
db_config = {
    'host': 'localhost',
    'user': '你的用户名',
    'password': '你的密码',
    'database': '你的数据库名'
}

most_common_word = None

try:
    conn = mysql.connector.connect(**db_config)
    cursor = conn.cursor()
    
    # 这里选MySQL8.0的查询语句，若用5.x则替换成对应SQL
    query = """
    WITH RECURSIVE genre_split AS (
        SELECT 
            TRIM(SUBSTRING_INDEX(genre, ',', 1)) AS genre_word,
            TRIM(SUBSTRING(genre, LOCATE(',', genre) + 1)) AS remaining_genres
        FROM your_table_name
        WHERE genre IS NOT NULL AND genre != ''
        UNION ALL
        SELECT 
            TRIM(SUBSTRING_INDEX(remaining_genres, ',', 1)) AS genre_word,
            TRIM(SUBSTRING(remaining_genres, LOCATE(',', remaining_genres) + 1)) AS remaining_genres
        FROM genre_split
        WHERE remaining_genres IS NOT NULL AND remaining_genres != ''
    )
    SELECT genre_word, COUNT(*) AS count
    FROM genre_split
    GROUP BY genre_word
    ORDER BY count DESC
    LIMIT 1;
    """
    
    cursor.execute(query)
    result = cursor.fetchone()
    
    if result:
        most_common_word = result[0]  # 提取最频繁的类型词
        print(f"最频繁的电影类型：{most_common_word}")

except mysql.connector.Error as e:
    print(f"数据库错误：{e}")
finally:
    # 确保资源释放
    if cursor:
        cursor.close()
    if conn:
        conn.close()

# 后续可直接使用most_common_word变量
# 示例：
# print(f"后续操作使用：{most_common_word}")

关键注意点 #

• 把代码中的your_table_name替换成你实际的表名
• 数据库连接参数要改成你自己的配置
• TRIM()函数是为了去除类型前后的空格，避免统计时出现" Romance"和"Romance"被当成两个不同词的情况

内容的提问来源于stack exchange，提问作者Phuc