Numeric Expressions

When numbers are used in an arithmetic or comparison operation, the result may depend on whether it involves exact or approximate values. For example:

   SELECT 1.1+2.2=3.3 as 'exact', 1.1E0+2.2E0=3.3E0 as 'approx.';

The column 'exact' returns true (1), but 'approx' returns false (0).

String Expressions

Double quotes can't be used if the ANSI_QUOTES SQL mode is enabled.
The || operator can be used for string concatenation if the PIPES_AS_CONCAT SQL mode is enabled.

The default character set and collation for literal strings depend on the values of the character_set_connection (the default is latin1) and collation_connection (the default is latin1_swedish_ci) system variables.

A non-binary string can be treated as a binary string by preceding it with the BINARY keyword. For example:

   SELECT BINARY 'Hello' = 'hello'; (returns FALSE(0))

If ether string in a comparison is binary, both strings are treated as binary.
String comparison rules also apply to ORDER BY, GROUP BY and DISTINCT operations.

LENGTH() returns the length of a string in bytes, whereas CHAR_LENGTH() returns the length in characters.
The UPPER() and LOWER() functions perform case conversion only if the argument is a non-binary string. To make the two functions perform case conversion for a binary string, convert it to a non-binary string like below:


MySQL allows use of LIKE with non-string values by converting non-string values to strings before performing the pattern match.