What does coercivity mean? MySQL Custom Variables

Question

All user variables have an implicit coercivity value.

what does it mean? is this something related to ...

  mysql> SET @a = 1; 
 mysql> SET @A = @a; 
 mysql> SELECT @a, @A;
 mysql> SELECT @a, @A;
 + ------ + ------ +
 |  @a |  @A |
 + ------ + ------ +
 |  1 |  1 |
 + ------ + ------ +
 mysql> SET @a = 2;
 mysql> SELECT @a, @A;
 mysql> SELECT @a, @A;
 + ------ + ------ +
 |  @a |  @A |
 + ------ + ------ +
 |  2 |  2 |
 + ------ + ------ +

where is @A assigned 2, possibly because it refers to @a?

Answer 1

SET @test = 'test'; SELECT COERCIBILITY(@test), COERCIBILITY('test'); --- --- 2 4 

From the documentation :

COERCIBILITY(str)

The returned values ​​have the values ​​shown in the following table. Lower values ​​have higher priority.

 Coercibility Meaning Example 0 Explicit collation Value with COLLATE clause 1 No collation Concatenation of strings with different collations 2 Implicit collation Column value 3 System constant USER() return value 4 Coercible Literal string 5 Ignorable NULL or an expression derived from NULL 

Coercivity determines what will be converted to that in the event of a sort conflict.

An expression with a higher coercivity will be converted to matching an expression with a lower coercivity.

This feature is useful for troubleshooting sorting problems. For example, these two queries return results in a different order.

This:

 SELECT col FROM ( SELECT DATABASE() AS col UNION ALL SELECT 'X' ) q ORDER BY col; ---- 'test' 'X' 

And this one:

 SET @t := 'X' COLLATE UTF8_BIN; SELECT col FROM ( SELECT DATABASE() AS col UNION ALL SELECT @t ) q ORDER BY col; ---- 'X' 'test' 

Why is that?

DATABASE() is a system function whose return values ​​have a coercivity of 3 and the default database sort is UTF8_GENERAL_CI .

'X' in the first query is a string literal with a capacity of 4 .

The result of UNION will always have the least coercivity of all values ​​(i.e. 3 ) and matching the expression with the least ability:

 SELECT col, COERCIBILITY(col), COLLATION(col) FROM ( SELECT DATABASE() AS col UNION ALL SELECT 'X' ) q ORDER BY col; -------- 'test', 3, 'utf8_general_ci' 'X', 3, 'utf8_general_ci' 

In the second query, @t is a variable that contains a string value with UTF8_BIN sorting. Since its coercivity is lower than that of a system function, this is a sorting variable that is used in the result set.

The coercivity of variable 2 , so the coercivity of the result is a variable, as well as a comparison:

 SET @t := 'X' COLLATE UTF8_BIN; SELECT col, COERCIBILITY(col), COLLATION(col) FROM ( SELECT DATABASE() AS col UNION ALL SELECT @t ) q ORDER BY col; -------- 'X', 2, 'utf8_bin' 'test', 2, 'utf8_bin'