mysql 汉字拼音首字母

import pymysql
from pypinyin import lazy_pinyin

# 连接数据库
conn = pymysql.connect(host='localhost', user='root', password='password', db='test')
cursor = conn.cursor()

# 查询所有汉字数据
cursor.execute("SELECT id, name FROM your_table")
rows = cursor.fetchall()

# 转换拼音首字母并更新数据库
for row in rows:
    id, name = row
    pinyin_initials = ''.join([p[0][0] for p in lazy_pinyin(name)])
    cursor.execute("UPDATE your_table SET pinyin_initials = %s WHERE id = %s", (pinyin_initials, id))

conn.commit()
cursor.close()
conn.close()

DELIMITER $$

CREATE FUNCTION GetPinyinInitials(name VARCHAR(255))
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    DECLARE result VARCHAR(255);
    -- 调用外部程序或API获取拼音首字母
    -- 这里假设调用一个名为get_pinyin_initials的外部程序
    SET result = (SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(get_pinyin_initials(name), ',', 1), ',', -1), ' ', ''), '(', '') FROM DUAL);
    RETURN result;
END$$

DELIMITER ;