MySQL5.7使用JSON變數作為計數器來對Laravel5.4中的行進行排名-有解無憂

我正在使用 MySQL 5.7 并嘗試使用 JSON 變數模擬排名（5.7 中不可用）。

問題是在 Laravel DB 或 PDO 下面的查詢不能正常作業，但在 TablePlus 中作業正常。

查詢按組計算行數并過濾計數器小于 250 的行的想法。每個組行在 json 變數中都有自己的計數器。

這個查詢的想法：

select result.*
from (
    select 
        @extracted_counter:=JSON_EXTRACT(@row_number, CONCAT('game', t.game_id)) as extracted_counter,
        JSON_SET(@row_number, CONCAT('key', t.game_id), @extracted_counter   1) as counters,
        t.*
    from (@row_number := '{"game1": 0, "game27": 0}') as x,
        table_name as t
    where t.app_id = 16325
) as result
where result.extracted_counter < 250;

@json_variable從另一個表構建。我把它做成一個字串來簡化。

同樣，原始查詢在 TablePlus 程式中作業正常，但是當涉及到 Laravel 5.4 DB 時，@row_number不能執行超過 3 個的加法，例如： "{"game27718": 0,"game27398": 3,"game26592": 0,"game24042": 0,"game23957": 3}"

我認為 Laravel 使用特定的 PDO 設定但找不到任何東西。此外，我嘗試使用CAST但它并沒有真正幫助，因為添加對前三個記錄有效。

不太確定 TablePlus 和 PDO Laravel 的不同設定有何不同。我試過用谷歌搜索 TablePlus 的設定，但我無法想象如何才能正確地用谷歌搜索它而不碰到界面檔案。

我將不勝感激任何幫助。

我嘗試使用的原始查詢如下所示：

SELECT temp.*
FROM (
    SELECT
    @extracted_counter:=CAST(JSON_EXTRACT(@row_number, CONCAT('$.game', sao.game_id)) AS UNSIGNED) AS extracted_rank,
    if(
        @extracted_counter < @games_length,
        @row_number:=JSON_SET(
            @row_number,
            CONCAT('$.game', sao.game_id),
            @extracted_counter   1
        ),
        @const_length
    ) AS rnk,
    sao.email, sao.name,
    sao.game_id, sao.order_total_price,
    sao.device_type, sao.coupon_id,
    sao.coupon_code, sao.browser,
    sao.city, sao.country,
    sao.order_id, sao.created_at
    FROM (SELECT @row_number := (
        SELECT concat('{', group_concat(concat('"game', s.game_id, '"'), ': 0'), '}') AS str_json
        FROM (
            SELECT g.id AS game_id
            FROM users AS u
            LEFT JOIN games AS g ON u.id = g.user_id
            WHERE u.app_id = 16325
                AND g.deleted_at IS NULL
        ) AS s
    )) AS x,
    (SELECT @games_length := 250) AS games_length,
    (SELECT @const_length := @games_length   100) AS const_length,
    shopify_app_orders as sao
    WHERE sao.app_id = 16325
        AND sao.coupon_id > 0
    ORDER BY sao.id DESC
) AS temp
LEFT JOIN coupons AS c ON c.id = temp.coupon_id
WHERE temp.extracted_rank < @games_length

更新

我發現user_variables_by_thread在查詢資料庫之前，MySQL 中仍然存在一些變數。

查詢執行前的變數：

array:5 [
  0 => {#1171
     "THREAD_ID": "2314"
     "VARIABLE_NAME": "extracted_counter"
     "VARIABLE_VALUE": null
  }
  1 => {#1172
     "THREAD_ID": "2314"
     "VARIABLE_NAME": "const_length"
     "VARIABLE_VALUE": "350"
  }
  2 => {#1173
     "THREAD_ID": "2314"
     "VARIABLE_NAME": "games_length"
     "VARIABLE_VALUE": "250"
  }
  3 => {#1174
     "THREAD_ID": "2314"
     "VARIABLE_NAME": "parse"
     "VARIABLE_VALUE": "{"game27718": 0,"game27398": 0,"game26592": 0,"game24042": 0,"game23957": 0}"
  }
  4 => {#1175
     "THREAD_ID": "2314"
     "VARIABLE_NAME": "row_number"
     "VARIABLE_VALUE": "{"game23957": 250.0, "game24042": 250.0, "game26592": 250.0, "game27398": 250.0, "game27718": 250.0}"
  }
]

執行查詢后的變數：

array:9 [
  0 => {#1178
     "THREAD_ID": "2419"
     "VARIABLE_NAME": "extracted_counter"
     "VARIABLE_VALUE": null
  }
  1 => {#1176
     "THREAD_ID": "2419"
     "VARIABLE_NAME": "const_length"
     "VARIABLE_VALUE": "350"
  }
  2 => {#1179
     "THREAD_ID": "2419"
     "VARIABLE_NAME": "games_length"
     "VARIABLE_VALUE": "250"
  }
  3 => {#1180
     "THREAD_ID": "2419"
     "VARIABLE_NAME": "row_number"
     "VARIABLE_VALUE": "{"game23957": 3, "game24042": 3, "game26592": 3, "game27398": 3, "game27718": 3}"
  }
  4 => {#1181
     "THREAD_ID": "2314"
     "VARIABLE_NAME": "extracted_counter"
     "VARIABLE_VALUE": null
  }
  5 => {#1182
     "THREAD_ID": "2314"
     "VARIABLE_NAME": "const_length"
     "VARIABLE_VALUE": "350"
  }
  6 => {#1183
     "THREAD_ID": "2314"
     "VARIABLE_NAME": "games_length"
     "VARIABLE_VALUE": "250"
  }
  7 => {#1184
     "THREAD_ID": "2314"
     "VARIABLE_NAME": "parse"
     "VARIABLE_VALUE": "{"game27718": 0,"game27398": 0,"game26592": 0,"game24042": 0,"game23957": 0}"
  }
  8 => {#1177
     "THREAD_ID": "2314"
     "VARIABLE_NAME": "row_number"
     "VARIABLE_VALUE": "{"game23957": 250.0, "game24042": 250.0, "game26592": 250.0, "game27398": 250.0, "game27718": 250.0}"
  }
]

row_number不同的thread_id中至少有兩個變數。不太確定如何處理現有變數。可以清除performance_schema.user_variables_by_thread嗎？它會破壞mysql嗎？

更新 2

在查詢消失之前，我已經關閉了 TablePlus 與資料庫和變數的連接。查詢完成后重復變數消失：

array:4 [
  0 => {#1184
     "THREAD_ID": "2422"
     "VARIABLE_NAME": "extracted_counter"
     "VARIABLE_VALUE": null
  }
  1 => {#1183
     "THREAD_ID": "2422"
     "VARIABLE_NAME": "const_length"
     "VARIABLE_VALUE": "350"
  }
  2 => {#1182
     "THREAD_ID": "2422"
     "VARIABLE_NAME": "games_length"
     "VARIABLE_VALUE": "250"
  }
  3 => {#1181
     "THREAD_ID": "2422"
     "VARIABLE_NAME": "row_number"
     "VARIABLE_VALUE": "{"game23957": 3, "game24042": 3, "game26592": 3, "game27398": 3, "game27718": 3}"
  }
]

不太確定為什么，但extracted_counter它null應該有價值。

uj5u.com熱心網友回復：

我不太確定如何處理打開的帖子，以及是否有人最終會遇到同樣的問題，但我發現問題出在哪里。

由于某些原因，型別自動轉換在 TablePlus 中有效，但不適用于 PDO，因此：

CAST(@extracted_counter:=CAST(JSON_EXTRACT(@row_number, CONCAT('$.game', sao.game_id)) AS UNSIGNED) AS UNSIGNED) AS extracted_rank

和這個

@extracted_counter:=CAST(JSON_EXTRACT(@row_number, CONCAT('$.game', sao.game_id)) AS UNSIGNED) AS extracted_rank

不一樣。

第一個會產生整數，第二個會給你一個字串。

順便說一句，接下來的兩個陳述句也不相同：

@extracted_counter < @games_length

@extracted_counter < CAST(@games_length AS UNSIGNED)

即使@games_length宣告如下：

(SELECT @games_length := 250) AS games_length,

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/533699.html

標籤：PHPlaravel-5mysql-5.7表加

上一篇：MailgunTransport.php第67行中的FatalThrowableError：未定義的類常量“VERSION”

下一篇：Laravel等待特定排隊的偵聽器完成然后回傳