Why is the integer output quoted in MySQL custom variable-CodePudding

SELECT score ,TRIM(BOTH "" FROM  ink) as   `rank` FROM (
SELECT
    score,
     @r :=
IF (@p = score, @r, @r   1) AS ink,
 @p := score
FROM
    scores,
    (SELECT @r := 0, @p := NULL) init
ORDER BY
    score DESC)dep
  

print{"headers": ["score", "rank"], "values": [[4.00, "1"], [4.00, "1"], [3.85, "2"], [3.65, "3"], [3.65, "3"], [3.50, "4"]]}

79/5000 I made a custom integer variable in MySQL. After I output, there were more quotes than the title, and then I used a function to remove the quotes, but the output result was still there. This situation makes me confused, I hope someone can answer it. Thank you.

CodePudding user response：

TRIM() output datatype is string. So double quote chars are not a part of the value. They are added by PHP while dumping the value for to mark that the datatype is string.
If you want to obtain the output column of numeric datatype (and in this case PHP will not add dquotes) use ink 0 or CAST(ink AS UNSIGNED).

DEMO fiddle. Pay attention to the value in column adjustment - strings are left-justified, numbers are right-justified.

PS. If you alter initial value for @p from NULL to some value which cannot be present in score (for example, -1, '' and so on) then @r := IF(@p = score, @r, @r 1) may be simplified to @r := @r (@p = score).

I was wondering why I defined @r=0, shouldn't it be an integer, but the output value is in quotes – johnson

This is user-defined variables processing feature.

Server cannot predict the value of what datatype will be assigned to the variable in future (in your query - while next row will be processing). So it uses the most common datatype for user-defined variable, the datatype which can accept the value of absolutely any datatype. And this the most common datatype is binary string (more precisely - LONGBLOB).

Additionally - all user-defined variables values are stored in PERFORMANCE_SCHEMA.user_variables_by_thread table. And according column datatype is LONGBLOB.