How to convert a character string into a given character set using the CONVERT() function?

CONVERT(str USING charset) is a MySQL built-in function that converts a character string into a given character set. For example:

SET @str = 'FYICenter';

SELECT @str, LENGTH(@str);
  -- +-----------+--------------+
  -- | @str      | LENGTH(@str) |
  -- +-----------+--------------+
  -- | FYICenter |            9 |
  -- +-----------+--------------+

SELECT CONVERT(@str USING utf8mb4), LENGTH(CONVERT(@str USING utf8mb4));
  -- +-----------------------------+-------------------------------------+
  -- | CONVERT(@str USING utf8mb4) | LENGTH(CONVERT(@str USING utf8mb4)) |
  -- +-----------------------------+-------------------------------------+
  -- | FYICenter                   |                                   9 |
  -- +-----------------------------+-------------------------------------+

Note that the CONVERT() function has another syntax version CONVERT(exp, type) to perform data type conversion, which is equivalent to the CAST() function. For example,

SET @exp = '-1.99 USD';

SELECT @exp, CONVERT(@exp, SIGNED INTEGER), CONVERT(@exp, UNSIGNED INTEGER);
  -- +-----------+-------------------------------+---------------------------------+
  -- | @exp      | CONVERT(@exp, SIGNED INTEGER) | CONVERT(@exp, UNSIGNED INTEGER) |
  -- +-----------+-------------------------------+---------------------------------+
  -- | -1.00 USD |                            -1 |            18446744073709551615 |
  -- +-----------+-------------------------------+---------------------------------+

SELECT @exp, CAST(@exp AS SIGNED INTEGER), CAST(@exp AS UNSIGNED INTEGER);
  -- +-----------+------------------------------+--------------------------------+
  -- | @exp      | CAST(@exp AS SIGNED INTEGER) | CAST(@exp AS UNSIGNED INTEGER) |
  -- +-----------+------------------------------+--------------------------------+
  -- | -1.99 USD |                           -1 |           18446744073709551615 |
  -- +-----------+------------------------------+--------------------------------+

Reference information of the CONVERT() function:

CONVERT(str USING charset): newstr
  Converts a character string into a given character set. 

Arguments, return value and availability:
  str: Required. The character string to be converted.
  charset: Required. The character set to be converted to.
  newstr: Return value. The converted character string.
  Available since MySQL 4.0.

CONVERT(exp, type): val
  Casts an expression to a value of the given data type. 

Arguments, return value and availability:
  exp: Required. The expression to be converted.
  type: Required. The data type to be converted to.
  val: Return value. The converted value of the given type.
  Available since MySQL 4.0.

Related MySQL functions:

• CAST() - Casting Expression to Value
• CONVERT() - Character Set Conversion

⇒ DEFAULT() - Table Column Default Value

⇐ COALESCE() - Finding First Non-NULL Value

⇑ MySQL Functions for Miscellaneous Purposes

⇑⇑ MySQL Function References