如何创建MySQL事件实现XP达800时更新数据库？代码报错求助

需求说明 #

数据库每秒检查表中XP值，当XP达到（或超过）800时，将rank字段设为Elite Genin并重置XP为0。

原代码及报错 #

原Python代码 #

@bot.event
async def on_message(message):
    guild = message.guild.id
    table = "PEOPLE_" + str(guild)

    try:
        connection = mysql.connector.connect(
        host="localhost",
        port="3306",
        user="root",
        password="root",
        database="naruto_game"
        )
        cursor = connection.cursor()

        sql_event_query = """CREATE EVENT geninpromotion
                             ON SCHEDULE EVERY 1 Second
                             STARTS CURRENT_TIMESTAMP + INTERVAL 1 Second 
                             ENDS CURRENT_TIMESTAMP + INTERVAL 24 Hours 
                             DO UPDATE """ + table + """ SET rank = 'Elite Genin' where xp = 800 AND SET xp = 0"""
        cursor.execute(sql_event_query)

    except mysql.connector.Error as error:
        print("Failed to find name: {}".format(error))
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL connection has been closed.")
    print("Event created.")

报错信息 #

Failed to find name: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Hours DO UPDATE " + table + " SET rank = 'Elite Genin' where xp = 800 AND SET xp' at line 2

错误根源 #

1. 时间单位语法错误：MySQL时间间隔单位要求为单数形式，24 Hours需改为24 HOUR
2. UPDATE语句格式错误：多个字段更新需用逗号分隔，不能用AND SET连接，正确写法为SET rank='Elite Genin', xp=0
3. 事件重复创建风险：每次发送消息都会执行CREATE EVENT，会触发"事件已存在"的错误，需添加IF NOT EXISTS判断
4. 字符串拼接不规范：直接拼接表名易引发格式问题，用f-string更简洁安全

修正后的代码 #

@bot.event
async def on_message(message):
    guild = message.guild.id
    table = f"PEOPLE_{guild}"  # 用f-string简化表名拼接

    try:
        connection = mysql.connector.connect(
            host="localhost",
            port="3306",
            user="root",
            password="root",
            database="naruto_game"
        )
        cursor = connection.cursor()

        # 修正SQL事件语句，解决语法问题并优化逻辑
        sql_event_query = f"""CREATE EVENT IF NOT EXISTS geninpromotion_{guild}
                             ON SCHEDULE EVERY 1 SECOND
                             STARTS CURRENT_TIMESTAMP + INTERVAL 1 SECOND 
                             ENDS CURRENT_TIMESTAMP + INTERVAL 24 HOUR 
                             DO UPDATE {table} 
                             SET rank = 'Elite Genin', xp = 0 
                             WHERE xp >= 800"""  # 改为>=避免XP超过800时无法触发

        cursor.execute(sql_event_query)
        connection.commit()  # DDL操作需显式提交事务

    except mysql.connector.Error as error:
        print(f"操作失败: {error}")
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL连接已关闭。")
    print("事件创建完成。")

关键优化说明 #

• 事件命名唯一：给事件加上guild.id后缀，避免不同服务器的事件重名冲突
• XP判断逻辑优化：将xp=800改为xp>=800，防止用户XP超过800时无法触发升级
• 事务提交：CREATE EVENT属于DDL操作，显式执行connection.commit()确保生效
• 事件调度器检查：确保MySQL事件调度器已开启，执行SET GLOBAL event_scheduler = ON;，或在my.cnf中配置event_scheduler=ON永久生效

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