Why Am I Still Getting 'Incorrect String Value' Errors in My UTF-8 MySQL Database?

Fixing "Incorrect String Value" Errors in MySQL

Background:

Despite configuring text columns with UTF-8 character set and collation, certain email addresses continue to trigger "Incorrect string value" errors. This article explores the underlying cause of these errors and provides solutions.

Cause:

The error message suggests that the affected email addresses contain characters not supported by the current character set configuration. Even though UTF-8 is generally permissive, some character sequences may exceed its limits.

Solution:

1. Verify Data Encoding: Ensure that the source data (email addresses) is truly encoded in UTF-8.

2. Check Database Connection: After establishing a MySQL connection, execute the following SQL statements:

   SET NAMES 'utf8mb4';
   SET CHARACTER SET utf8mb4;

   Copy after login

   This sets the character set and collation for the current connection to UTF-8 with four bytes per character (utf8mb4).

3. Verify Table Character Set: For the tables containing the affected email addresses, run the following query to check the character set:

   SELECT
     `tables`.`TABLE_NAME`,
     `collations`.`character_set_name`
   FROM
     `information_schema`.`TABLES` AS `tables`,
     `information_schema`.`COLLATION_CHARACTER_SET_APPLICABILITY` AS `collations`
   WHERE
     `tables`.`table_schema` = DATABASE()
     AND `collations`.`collation_name` = `tables`.`table_collation`
     ;

   Copy after login

   Ensure that the character set is set to utf8mb4.

4. Check Database Settings: Run the following MySQL statements to verify the global character set and collation settings:

   mysql> show variables like '%colla%';
   mysql> show variables like '%charac%';

   Copy after login

   Ensure that both character set and collation are set to utf8mb4.

Effects of Fix:

Using utf8mb4 increases the supported character range and eliminates the "Incorrect string value" errors for email addresses with extended characters. It may also improve the compatibility of the database with international data and future Unicode standards.

The above is the detailed content of Why Am I Still Getting 'Incorrect String Value' Errors in My UTF-8 MySQL Database?. For more information, please follow other related articles on the PHP Chinese website!