Como comparar um valor mysql

Anterior: Mysql conexões uso

Seguinte: Expressões Regulares do MySQL

Nós já sabemos MySQL usando o comando SQL SELECT com a cláusula WHERE para ler os dados na tabela de dados, mas os campos de critérios de consulta fornecido quando NULL, o comando pode não funcionar corretamente quando.

Para lidar com este caso, MySQL fornece três principais operadores:

• IS NULL: Quando o valor da coluna é NULL, o operador retorna verdadeiro.
• IS NOT NULL: Quando o valor da coluna não é NULL, o operador retorna verdadeiro.
• <=>: Operadores de comparação (= diferente do operador) e retorna verdadeiro quando se comparam os dois valor é NULL.

operação de comparação condição Sobre NULL é especial. Você não pode usar = NULL ou! = NULL localizar valores nulos na coluna.

No MySQL, valores NULL comparar com qualquer outro valor (mesmo NULL) sempre retorna false, ou seja, NULL = NULL retorna false.

MySQL o tratamento de NULL usando o IS NULL e IS NOT NULL operador.

valores nulos em prompt de comando

O exemplo a seguir pressupõe que o banco de dados w3big tabelas tcount_tbl contendo dois w3big_author e w3big_count, w3big_count estabelecidos valores nulos de inserção.

Exemplos

Tente os seguintes exemplos:

O exemplo a seguir você pode ver = e operador = não funciona !:

Encontrar a tabela de dados w3big_count se a coluna é NULL, você deve usar o IS NULL e IS NOT NULL, os seguintes exemplos:

Use PHP valor NULL processamento do script

scripts PHP você pode se ... else de lidar se a variável é vazia, e gera a declaração condicional correspondente.

Os exemplos a seguir definido $ variável w3big_count PHP, em seguida, usar a variável com a tabela de dados w3big_count campos comparação:

Anterior: Mysql conexões uso

Seguinte: Expressões Regulares do MySQL

12.4.2 Comparison Functions and Operators

Table 12.4 Comparison Operators

Comparison operations result in a value of 1 (TRUE), 0 (FALSE), or NULL. These operations work for both numbers and strings. Strings are automatically converted to numbers and numbers to strings as necessary.

The following relational comparison operators can be used to compare not only scalar operands, but row operands:

The descriptions for those operators later in this section detail how they work with row operands. For additional examples of row comparisons in the context of row subqueries, see Section 13.2.11.5, “Row Subqueries”.

Some of the functions in this section return values other than 1 (TRUE), 0 (FALSE), or NULL. LEAST() and GREATEST() are examples of such functions; Section 12.3, “Type Conversion in Expression Evaluation”, describes the rules for comparison operations performed by these and similar functions for determining their return values.

Note

In previous versions of MySQL, when evaluating an expression containing LEAST() or GREATEST(), the server attempted to guess the context in which the function was used, and to coerce the function's arguments to the data type of the expression as a whole. For example, the arguments to LEAST("11", "45", "2") are evaluated and sorted as strings, so that this expression returns "11". In MySQL 8.0.3 and earlier, when evaluating the expression LEAST("11", "45", "2") + 0, the server converted the arguments to integers (anticipating the addition of integer 0 to the result) before sorting them, thus returning 2.

Beginning with MySQL 8.0.4, the server no longer attempts to infer context in this fashion. Instead, the function is executed using the arguments as provided, performing data type conversions to one or more of the arguments if and only if they are not all of the same type. Any type coercion mandated by an expression that makes use of the return value is now performed following function execution. This means that, in MySQl 8.0.4 and later, LEAST("11", "45", "2") + 0 evaluates to "11" + 0 and thus to integer 11. (Bug #83895, Bug #25123839)

To convert a value to a specific type for comparison purposes, you can use the CAST() function. String values can be converted to a different character set using CONVERT(). See Section 12.11, “Cast Functions and Operators”.

By default, string comparisons are not case-sensitive and use the current character set. The default is utf8mb4.

• =

  Equal:

  mysql> SELECT 1 = 0; -> 0 mysql> SELECT '0' = 0; -> 1 mysql> SELECT '0.0' = 0; -> 1 mysql> SELECT '0.01' = 0; -> 0 mysql> SELECT '.01' = 0.01; -> 1

  For row comparisons, (a, b) = (x, y) is equivalent to:

  (a = x) AND (b = y)

• <=>

  NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.

  The <=> operator is equivalent to the standard SQL IS NOT DISTINCT FROM operator.

  mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL; -> 1, 1, 0 mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL; -> 1, NULL, NULL

  For row comparisons, (a, b) <=> (x, y) is equivalent to:

  (a <=> x) AND (b <=> y)

• <>, !=

  Not equal:

  mysql> SELECT '.01' <> '0.01'; -> 1 mysql> SELECT .01 <> '0.01'; -> 0 mysql> SELECT 'zapp' <> 'zappp'; -> 1

  For row comparisons, (a, b) <> (x, y) and (a, b) != (x, y) are equivalent to:

  (a <> x) OR (b <> y)

• <=

  Less than or equal:

  mysql> SELECT 0.1 <= 2; -> 1

  For row comparisons, (a, b) <= (x, y) is equivalent to:

  (a < x) OR ((a = x) AND (b <= y))

• <

  Less than:

  mysql> SELECT 2 < 2; -> 0

  For row comparisons, (a, b) < (x, y) is equivalent to:

  (a < x) OR ((a = x) AND (b < y))

• >=

  Greater than or equal:

  mysql> SELECT 2 >= 2; -> 1

  For row comparisons, (a, b) >= (x, y) is equivalent to:

  (a > x) OR ((a = x) AND (b >= y))

• >

  Greater than:

  mysql> SELECT 2 > 2; -> 0

  For row comparisons, (a, b) > (x, y) is equivalent to:

  (a > x) OR ((a = x) AND (b > y))

• expr BETWEEN min AND max

  If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type. Otherwise type conversion takes place according to the rules described in Section 12.3, “Type Conversion in Expression Evaluation”, but applied to all the three arguments.

  mysql> SELECT 2 BETWEEN 1 AND 3, 2 BETWEEN 3 and 1; -> 1, 0 mysql> SELECT 1 BETWEEN 2 AND 3; -> 0 mysql> SELECT 'b' BETWEEN 'a' AND 'c'; -> 1 mysql> SELECT 2 BETWEEN 2 AND '3'; -> 1 mysql> SELECT 2 BETWEEN 2 AND 'x-3'; -> 0

  For best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type. Examples: If you compare a DATETIME to two DATE values, convert the DATE values to DATETIME values. If you use a string constant such as '2001-1-1' in a comparison to a DATE, cast the string to a DATE.

• expr NOT BETWEEN min AND max

  This is the same as NOT (expr BETWEEN min AND max).

• COALESCE(value,...)

  Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

  The return type of COALESCE() is the aggregated type of the argument types.

  mysql> SELECT COALESCE(NULL,1); -> 1 mysql> SELECT COALESCE(NULL,NULL,NULL); -> NULL

• GREATEST(value1,value2,...)

  With two or more arguments, returns the largest (maximum-valued) argument. The arguments are compared using the same rules as for LEAST().

  mysql> SELECT GREATEST(2,0); -> 2 mysql> SELECT GREATEST(34.0,3.0,5.0,767.0); -> 767.0 mysql> SELECT GREATEST('B','A','C'); -> 'C'

  GREATEST() returns NULL if any argument is NULL.

• expr IN (value,...)

  Returns 1 (true) if expr is equal to any of the values in the IN() list, else returns 0 (false).

  Type conversion takes place according to the rules described in Section 12.3, “Type Conversion in Expression Evaluation”, applied to all the arguments. If no type conversion is needed for the values in the IN() list, they are all non-JSON constants of the same type, and expr can be compared to each of them as a value of the same type (possibly after type conversion), an optimization takes place. The values the list are sorted and the search for expr is done using a binary search, which makes the IN() operation very quick.

  mysql> SELECT 2 IN (0,3,5,7); -> 0 mysql> SELECT 'wefwf' IN ('wee','wefwf','weg'); -> 1

  IN() can be used to compare row constructors:

  mysql> SELECT (3,4) IN ((1,2), (3,4)); -> 1 mysql> SELECT (3,4) IN ((1,2), (3,5)); -> 0

  You should never mix quoted and unquoted values in an IN() list because the comparison rules for quoted values (such as strings) and unquoted values (such as numbers) differ. Mixing types may therefore lead to inconsistent results. For example, do not write an IN() expression like this:

  SELECT val1 FROM tbl1 WHERE val1 IN (1,2,'a');

  Instead, write it like this:

  SELECT val1 FROM tbl1 WHERE val1 IN ('1','2','a');

  Implicit type conversion may produce nonintuitive results:

  mysql> SELECT 'a' IN (0), 0 IN ('b'); -> 1, 1

  In both cases, the comparison values are converted to floating-point values, yielding 0.0 in each case, and a comparison result of 1 (true).

  The number of values in the IN() list is only limited by the max_allowed_packet value.

  To comply with the SQL standard, IN() returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL.

  IN() syntax can also be used to write certain types of subqueries. See Section 13.2.11.3, “Subqueries with ANY, IN, or SOME”.

• expr NOT IN (value,...)

  This is the same as NOT (expr IN (value,...)).

• INTERVAL(N,N1,N2,N3,...)

  Returns 0 if N < N1, 1 if N < N2 and so on or -1 if N is NULL. All arguments are treated as integers. It is required that N1 < N2 < N3 < ... < Nn for this function to work correctly. This is because a binary search is used (very fast).

  mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200); -> 3 mysql> SELECT INTERVAL(10, 1, 10, 100, 1000); -> 2 mysql> SELECT INTERVAL(22, 23, 30, 44, 200); -> 0

• IS boolean_value

  Tests a value against a boolean value, where boolean_value can be TRUE, FALSE, or UNKNOWN.

  mysql> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN; -> 1, 1, 1

• IS NOT boolean_value

  Tests a value against a boolean value, where boolean_value can be TRUE, FALSE, or UNKNOWN.

  mysql> SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN; -> 1, 1, 0

• IS NULL

  Tests whether a value is NULL.

  mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL; -> 0, 0, 1

  To work well with ODBC programs, MySQL supports the following extra features when using IS NULL:

  • If sql_auto_is_null variable is set to 1, then after a statement that successfully inserts an automatically generated AUTO_INCREMENT value, you can find that value by issuing a statement of the following form:

    SELECT * FROM tbl_name WHERE auto_col IS NULL

    If the statement returns a row, the value returned is the same as if you invoked the LAST_INSERT_ID() function. For details, including the return value after a multiple-row insert, see Section 12.16, “Information Functions”. If no AUTO_INCREMENT value was successfully inserted, the SELECT statement returns no row.

    The behavior of retrieving an AUTO_INCREMENT value by using an IS NULL comparison can be disabled by setting sql_auto_is_null = 0. See Section 5.1.8, “Server System Variables”.

    The default value of sql_auto_is_null is 0.

  • For DATE and DATETIME columns that are declared as NOT NULL, you can find the special date '0000-00-00' by using a statement like this:

    SELECT * FROM tbl_name WHERE date_column IS NULL

    This is needed to get some ODBC applications to work because ODBC does not support a '0000-00-00' date value.

    See Obtaining Auto-Increment Values, and the description for the FLAG_AUTO_IS_NULL option at Connector/ODBC Connection Parameters.

• IS NOT NULL

  Tests whether a value is not NULL.

  mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL; -> 1, 1, 0

• ISNULL(expr)

  If expr is NULL, ISNULL() returns 1, otherwise it returns 0.

  mysql> SELECT ISNULL(1+1); -> 0 mysql> SELECT ISNULL(1/0); -> 1

  ISNULL() can be used instead of = to test whether a value is NULL. (Comparing a value to NULL using = always yields NULL.)

  The ISNULL() function shares some special behaviors with the IS NULL comparison operator. See the description of IS NULL.

• LEAST(value1,value2,...)

  With two or more arguments, returns the smallest (minimum-valued) argument. The arguments are compared using the following rules:

  • If any argument is NULL, the result is NULL. No comparison is needed.

  • If all arguments are integer-valued, they are compared as integers.

  • If at least one argument is double precision, they are compared as double-precision values. Otherwise, if at least one argument is a DECIMAL value, they are compared as DECIMAL values.

  • If the arguments comprise a mix of numbers and strings, they are compared as strings.

  • If any argument is a nonbinary (character) string, the arguments are compared as nonbinary strings.

  • In all other cases, the arguments are compared as binary strings.

  The return type of LEAST() is the aggregated type of the comparison argument types.

  mysql> SELECT LEAST(2,0); -> 0 mysql> SELECT LEAST(34.0,3.0,5.0,767.0); -> 3.0 mysql> SELECT LEAST('B','A','C'); -> 'A'

Page 2

Table 12.5 Logical Operators

In SQL, all logical operators evaluate to TRUE, FALSE, or NULL (UNKNOWN). In MySQL, these are implemented as 1 (TRUE), 0 (FALSE), and NULL. Most of this is common to different SQL database servers, although some servers may return any nonzero value for TRUE.

MySQL evaluates any nonzero, non-NULL value to TRUE. For example, the following statements all assess to TRUE:

• NOT, !

  Logical NOT. Evaluates to 1 if the operand is 0, to 0 if the operand is nonzero, and NOT NULL returns NULL.

  mysql> SELECT NOT 10; -> 0 mysql> SELECT NOT 0; -> 1 mysql> SELECT NOT NULL; -> NULL mysql> SELECT ! (1+1); -> 0 mysql> SELECT ! 1+1; -> 1

  The last example produces 1 because the expression evaluates the same way as (!1)+1.

  The !, operator is a nonstandard MySQL extension. As of MySQL 8.0.17, this operator is deprecated; expect it to be removed in a future version of MySQL. Applications should be adjusted to use the standard SQL NOT operator.

• AND, &&

  Logical AND. Evaluates to 1 if all operands are nonzero and not NULL, to 0 if one or more operands are 0, otherwise NULL is returned.

  mysql> SELECT 1 AND 1; -> 1 mysql> SELECT 1 AND 0; -> 0 mysql> SELECT 1 AND NULL; -> NULL mysql> SELECT 0 AND NULL; -> 0 mysql> SELECT NULL AND 0; -> 0

  The &&, operator is a nonstandard MySQL extension. As of MySQL 8.0.17, this operator is deprecated; expect support for it to be removed in a future version of MySQL. Applications should be adjusted to use the standard SQL AND operator.

• OR, ||

  Logical OR. When both operands are non-NULL, the result is 1 if any operand is nonzero, and 0 otherwise. With a NULL operand, the result is 1 if the other operand is nonzero, and NULL otherwise. If both operands are NULL, the result is NULL.

  mysql> SELECT 1 OR 1; -> 1 mysql> SELECT 1 OR 0; -> 1 mysql> SELECT 0 OR 0; -> 0 mysql> SELECT 0 OR NULL; -> NULL mysql> SELECT 1 OR NULL; -> 1

  If the PIPES_AS_CONCAT SQL mode is enabled, || signifies the SQL-standard string concatenation operator (like CONCAT()).

  The ||, operator is a nonstandard MySQL extension. As of MySQL 8.0.17, this operator is deprecated; expect support for it to be removed in a future version of MySQL. Applications should be adjusted to use the standard SQL OR operator. Exception: Deprecation does not apply if PIPES_AS_CONCAT is enabled because, in that case, || signifies string concatentation.

• XOR

  Logical XOR. Returns NULL if either operand is NULL. For non-NULL operands, evaluates to 1 if an odd number of operands is nonzero, otherwise 0 is returned.

  mysql> SELECT 1 XOR 1; -> 0 mysql> SELECT 1 XOR 0; -> 1 mysql> SELECT 1 XOR NULL; -> NULL mysql> SELECT 1 XOR 1 XOR 1; -> 1

  a XOR b is mathematically equal to (a AND (NOT b)) OR ((NOT a) and b).

Page 3

12.4.4 Assignment Operators

Table 12.6 Assignment Operators

• :=

  Assignment operator. Causes the user variable on the left hand side of the operator to take on the value to its right. The value on the right hand side may be a literal value, another variable storing a value, or any legal expression that yields a scalar value, including the result of a query (provided that this value is a scalar value). You can perform multiple assignments in the same SET statement. You can perform multiple assignments in the same statement.

  Unlike =, the := operator is never interpreted as a comparison operator. This means you can use := in any valid SQL statement (not just in SET statements) to assign a value to a variable.

  mysql> SELECT @var1, @var2; -> NULL, NULL mysql> SELECT @var1 := 1, @var2; -> 1, NULL mysql> SELECT @var1, @var2; -> 1, NULL mysql> SELECT @var1, @var2 := @var1; -> 1, 1 mysql> SELECT @var1, @var2; -> 1, 1 mysql> SELECT @var1:=COUNT(*) FROM t1; -> 4 mysql> SELECT @var1; -> 4

  You can make value assignments using := in other statements besides SELECT, such as UPDATE, as shown here:

  mysql> SELECT @var1; -> 4 mysql> SELECT * FROM t1; -> 1, 3, 5, 7 mysql> UPDATE t1 SET c1 = 2 WHERE c1 = @var1:= 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT @var1; -> 1 mysql> SELECT * FROM t1; -> 2, 3, 5, 7

  While it is also possible both to set and to read the value of the same variable in a single SQL statement using the := operator, this is not recommended. Section 9.4, “User-Defined Variables”, explains why you should avoid doing this.

• =

  This operator is used to perform value assignments in two cases, described in the next two paragraphs.

  Within a SET statement, = is treated as an assignment operator that causes the user variable on the left hand side of the operator to take on the value to its right. (In other words, when used in a SET statement, = is treated identically to :=.) The value on the right hand side may be a literal value, another variable storing a value, or any legal expression that yields a scalar value, including the result of a query (provided that this value is a scalar value). You can perform multiple assignments in the same SET statement.

  In the SET clause of an UPDATE statement, = also acts as an assignment operator; in this case, however, it causes the column named on the left hand side of the operator to assume the value given to the right, provided any WHERE conditions that are part of the UPDATE are met. You can make multiple assignments in the same SET clause of an UPDATE statement.

  In any other context, = is treated as a comparison operator.

  mysql> SELECT @var1, @var2; -> NULL, NULL mysql> SELECT @var1 := 1, @var2; -> 1, NULL mysql> SELECT @var1, @var2; -> 1, NULL mysql> SELECT @var1, @var2 := @var1; -> 1, 1 mysql> SELECT @var1, @var2; -> 1, 1

  For more information, see Section 13.7.6.1, “SET Syntax for Variable Assignment”, Section 13.2.13, “UPDATE Statement”, and Section 13.2.11, “Subqueries”.

Page 4

12.5 Flow Control Functions

Table 12.7 Flow Control Operators

• CASE value WHEN compare_value THEN result [WHEN compare_value THEN result ...] [ELSE result] END

  CASE WHEN condition THEN result [WHEN condition THEN result ...] [ELSE result] END

  The first CASE syntax returns the result for the first value=compare_value comparison that is true. The second syntax returns the result for the first condition that is true. If no comparison or condition is true, the result after ELSE is returned, or NULL if there is no ELSE part.

  The syntax of the CASE operator described here differs slightly from that of the SQL CASE statement described in Section 13.6.5.1, “CASE Statement”, for use inside stored programs. The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END.

  The return type of a CASE expression result is the aggregated type of all result values:

  • If all types are numeric, the aggregated type is also numeric:

    • If at least one argument is double precision, the result is double precision.

    • Otherwise, if at least one argument is DECIMAL, the result is DECIMAL.

    • Otherwise, the result is an integer type (with one exception):

      • If all integer types are all signed or all unsigned, the result is the same sign and the precision is the highest of all specified integer types (that is, TINYINT, SMALLINT, MEDIUMINT, INT, or BIGINT).

      • If there is a combination of signed and unsigned integer types, the result is signed and the precision may be higher. For example, if the types are signed INT and unsigned INT, the result is signed BIGINT.

      • The exception is unsigned BIGINT combined with any signed integer type. The result is DECIMAL with sufficient precision and scale 0.

  • If all types are BIT, the result is BIT. Otherwise, BIT arguments are treated similar to BIGINT.

  • If all types are YEAR, the result is YEAR. Otherwise, YEAR arguments are treated similar to INT.

  • If all types are character string (CHAR or VARCHAR), the result is VARCHAR with maximum length determined by the longest character length of the operands.

  • If all types are character or binary string, the result is VARBINARY.

  • SET and ENUM are treated similar to VARCHAR; the result is VARCHAR.

  • If all types are JSON, the result is JSON.

  • If all types are temporal, the result is temporal:

  • If all types are GEOMETRY, the result is GEOMETRY.

  • If any type is BLOB, the result is BLOB.

  • For all other type combinations, the result is VARCHAR.

  • Literal NULL operands are ignored for type aggregation.

  mysql> SELECT CASE 1 WHEN 1 THEN 'one' -> WHEN 2 THEN 'two' ELSE 'more' END; -> 'one' mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END; -> 'true' mysql> SELECT CASE BINARY 'B' -> WHEN 'a' THEN 1 WHEN 'b' THEN 2 END; -> NULL

• IF(expr1,expr2,expr3)

  If expr1 is TRUE (expr1 <> 0 and expr1 IS NOT NULL), IF() returns expr2. Otherwise, it returns expr3.

  If only one of expr2 or expr3 is explicitly NULL, the result type of the IF() function is the type of the non-NULL expression.

  The default return type of IF() (which may matter when it is stored into a temporary table) is calculated as follows:

  • If expr2 or expr3 produce a string, the result is a string.

    If expr2 and expr3 are both strings, the result is case-sensitive if either string is case-sensitive.

  • If expr2 or expr3 produce a floating-point value, the result is a floating-point value.

  • If expr2 or expr3 produce an integer, the result is an integer.

  mysql> SELECT IF(1>2,2,3); -> 3 mysql> SELECT IF(1<2,'yes','no'); -> 'yes' mysql> SELECT IF(STRCMP('test','test1'),'no','yes'); -> 'no'

• IFNULL(expr1,expr2)

  If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.

  mysql> SELECT IFNULL(1,0); -> 1 mysql> SELECT IFNULL(NULL,10); -> 10 mysql> SELECT IFNULL(1/0,10); -> 10 mysql> SELECT IFNULL(1/0,'yes'); -> 'yes'

  The default return type of IFNULL(expr1,expr2) is the more “general” of the two expressions, in the order STRING, REAL, or INTEGER. Consider the case of a table based on expressions or where MySQL must internally store a value returned by IFNULL() in a temporary table:

  mysql> CREATE TABLE tmp SELECT IFNULL(1,'test') AS test; mysql> DESCRIBE tmp; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | test | varbinary(4) | NO | | | | +-------+--------------+------+-----+---------+-------+

  In this example, the type of the test column is VARBINARY(4) (a string type).

• NULLIF(expr1,expr2)

  Returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.

  The return value has the same type as the first argument.

  mysql> SELECT NULLIF(1,1); -> NULL mysql> SELECT NULLIF(1,2); -> 1

  MySQL evaluates expr1 twice if the arguments are not equal.

The handling of system variable values by these functions changed in MySQL 8.0.22. For each of these functions, if the first argument contains only characters present in the character set and collation used by the second argument (and it is constant), the latter character set and collation is used to make the comparison. In MySQL 8.0.22 and later, system variable values are handled as column values of the same character and collation. Some queries using these functions with system variables that were previously successful may subsequently be rejected with Illegal mix of collations. In such cases, you should cast the system variable to the correct character set and collation.

Page 5