•When assigning column values for INSERT  or UPDATE  using a string literal: 

SET NAMES latin1; INSERT INTO t1 (utf8_bin_column) VALUES (’string-in-latin1’); 

•When sending results from the server to a client: 

SET NAMES latin1; SELECT utf8_bin_column FROM t2; 

For binary string columns, no conversion occurs. For cases similar to those preceding, the string value is copied byte-wise. 

## Lettercase Conversion 

Collations for nonbinary character sets provide information about lettercase of characters, so characters in a nonbinary string can be converted from one lettercase to another, even for _bin  collations that ignore lettercase for ordering: 

mysql> SET NAMES utf8mb4 COLLATE utf8mb4_bin; mysql> SELECT LOWER(’aA’), UPPER(’zZ’); +-------------+-------------+ | LOWER(’aA’) | UPPER(’zZ’) | +-------------+-------------+ | aa | ZZ | +-------------+-------------+ 

The concept of lettercase does not apply to bytes in a binary string. To perform lettercase conversion, the string must first be converted to a nonbinary string using a character set appropriate for the data stored in the string: 

## Trailing Space Handling in Comparisons 

mysql> SET NAMES binary; mysql> SELECT LOWER(’aA’), LOWER(CONVERT(’aA’ USING utf8mb4)); +-------------+------------------------------------+ | LOWER(’aA’) | LOWER(CONVERT(’aA’ USING utf8mb4)) | +-------------+------------------------------------+ | aA | aa | +-------------+------------------------------------+ 

MySQL collations have a pad attribute, which has a value of PAD SPACE  or NO PAD : 

•Most MySQL collations have a pad attribute of PAD SPACE . 

•The Unicode collations based on UCA 9.0.0 and higher have a pad attribute of NO PAD ; see Section 10.10.1, “Unicode Character Sets” . 

For nonbinary strings ( CHAR , VARCHAR , and TEXT  values), the string collation pad attribute determines treatment in comparisons of trailing spaces at the end of strings: 

•For PAD SPACE  collations, trailing spaces are insignificant in comparisons; strings are compared without regard to trailing spaces. 

• NO PAD  collations treat trailing spaces as significant in comparisons, like any other character. 

The differing behaviors can be demonstrated using the two utf8mb4  binary collations, one of which is PAD SPACE , the other of which is NO PAD . The example also shows how to use the INFORMATION_SCHEMA COLLATIONS  table to determine the pad attribute for collations. 

mysql> SELECT COLLATION_NAME, PAD_ATTRIBUTE FROM INFORMATION_SCHEMA.COLLATIONS 

