How to run multiple sql at once?

[udoless]

I have a MySql SQL that needs to set some user variables initially, but I can't find the right way to achieve it. I can run this SQL directly in the MySql client. Below are some code pieces.

let mut query = sqlx::query(r#"SET @order_num = 0;
        SET @msg_id = '';
        SELECT message_id, reaction, user_id, score, created_at
        FROM (
            SELECT message_id, reaction, user_id, score, created_at,
                CASE
                    WHEN @msg_id = message_id THEN @order_num := @order_num + 1
                    ELSE @order_num := 1
                END AS rownum,
                @msg_id := message_id
            FROM (
                SELECT message_id, reaction, user_id, CAST(SUM(score) AS UNSIGNED) AS score, MAX(created_at) AS created_at
                FROM message_reaction_1
                WHERE biz_id = 'reward'
                GROUP BY message_id, reaction, user_id
                ORDER BY message_id, reaction, score DESC
            ) tmp
        ) tmp2
        WHERE rownum <= 5;
    "#);
let mut s = query.fetch_many(self.get_connection(&db)).await;

panicked at 'called Result::unwrap()on anErr value: Database(MySqlDatabaseError { code: Some("42000"), number: 1064, message: "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 'set @msg_id='';\n select message_id,reaction,user_id,score,created_at from' at line 2" })'

[BeaulahShimkus4x]

You can set the user-defined variables at the start of your SQL query using a single SET statement and separate each variable assignment with a comma.

[udoless]

Thanks for your reply! I tried, but I still get the same error.

I ended up using a workaround to solve the problem.

SELECT @msg_id:='',@order_num:=0,message_id, reaction, user_id, score, created_at
        FROM (
            SELECT message_id, reaction, user_id, score, created_at,
                CASE
                    WHEN @msg_id = message_id THEN @order_num := @order_num + 1
                    ELSE @order_num := 1
                END AS rownum,
                @msg_id := message_id
            FROM (
                SELECT message_id, reaction, user_id, CAST(SUM(score) AS UNSIGNED) AS score, MAX(created_at) AS created_at
                FROM message_reaction_1
                WHERE biz_id = 'reward'
                GROUP BY message_id, reaction, user_id
                ORDER BY message_id, reaction, score DESC
            ) tmp
        ) tmp2
        WHERE rownum <= 5;