mysql incorrect string value for column
Jul 15, 2025 am 02:40 AMMySQL error "incorrect string value for column" is usually because the field character set does not support four-byte characters such as emoji. 1. Cause of error: MySQL's utf8 character set only supports three-byte characters and cannot store four-byte emoji; 2. Solution: Change the database, table, fields and connections to utf8mb4 character set; 3. Also check whether the configuration files, temporary tables, application layer encoding and client drivers all support utf8mb4; 4. Alternative solution: If you do not need to support four-byte characters, you can filter special characters such as emoji at the application layer.
MySQL error "incorrect string value for column" usually occurs when the character set of the data you are trying to insert or update does not match the field. The most common situation is that four-byte characters are inserted into the utf8-encoded field (such as some emoji emojis), while MySQL's utf8 character set only supports up to three bytes.

1. Why did this error be reported?
MySQL's utf8
character set is not completely compatible with standard UTF-8, it only supports three bytes of characters at most. And emojis like ?, ??,? are four byte characters and need to be stored normally with utf8mb4
.
If your database, table, or field uses utf8
instead of utf8mb4
, then when inserting these characters, it will be reported:

Incorrect string value: '\xF0\x9F\x98\x82' for column 'xxx'
2. How to solve this problem?
To completely solve this problem, we need to change the database, table, field to the connection settings to support a four-byte character set.
The modification steps are as follows:
Modify database character set
ALTER DATABASE your_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Modify the data table character set
ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Modify the character set of a field individually
ALTER TABLE your_table MODIFY column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Modify the connection character set (set in the program)
For example, when connecting MySQL in PHP, add:
mysqli_set_charset($conn, "utf8mb4");
Or add:
?characterEncoding=utf8mb4&connectionCollation=utf8mb4_unicode_ci
3. What other places need to be checked?
Sometimes even if the database is changed to utf8mb4, if a link in the middle does not keep up, there will still be problems.
Common places that are easy to ignore:
- The default character set is not set to utf8mb4 in the database configuration file (my.cnf or my.ini).
- The character set of table joins, temporary tables, or function returns values may still be utf8.
- The application layer code is not transcoded correctly when processing input and output.
- Use an older version of MySQL client driver and does not support utf8mb4.
It is recommended to check your entire link: front-end → back-end → database connection → database storage. Each link must confirm whether utf8mb4 is used.
4. What should I do if I really don’t want to change?
If your application does not need to support emoji or other four-byte characters for the time being, you can consider preprocessing at the application layer to filter out these special characters. Although this is not a fundamental solution, it can be used as a temporary emergency response.
For example, in PHP, you can filter out emojis with regular expressions:
$text = preg_replace('/[\x{10000}-\x{10FFFF}]/u', '', $text);
Or similar processing can be done in Python:
import re text = re.sub(r'[^\u0000-\uFFFF]', '', text)
However, this method will lose some user input content and is not recommended for long-term use.
Basically that's it. This type of problem seems simple, but it is easy to miss a certain link when troubleshooting, especially when relocating historical projects, you should pay more attention to details.
The above is the detailed content of mysql incorrect string value for column. For more information, please follow other related articles on the PHP Chinese website!

Hot AI Tools

Undress AI Tool
Undress images for free

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

mysqldump is a common tool for performing logical backups of MySQL databases. It generates SQL files containing CREATE and INSERT statements to rebuild the database. 1. It does not back up the original file, but converts the database structure and content into portable SQL commands; 2. It is suitable for small databases or selective recovery, and is not suitable for fast recovery of TB-level data; 3. Common options include --single-transaction, --databases, --all-databases, --routines, etc.; 4. Use mysql command to import during recovery, and can turn off foreign key checks to improve speed; 5. It is recommended to test backup regularly, use compression, and automatic adjustment.

When handling NULL values ??in MySQL, please note: 1. When designing the table, the key fields are set to NOTNULL, and optional fields are allowed NULL; 2. ISNULL or ISNOTNULL must be used with = or !=; 3. IFNULL or COALESCE functions can be used to replace the display default values; 4. Be cautious when using NULL values ??directly when inserting or updating, and pay attention to the data source and ORM framework processing methods. NULL represents an unknown value and does not equal any value, including itself. Therefore, be careful when querying, counting, and connecting tables to avoid missing data or logical errors. Rational use of functions and constraints can effectively reduce interference caused by NULL.

GROUPBY is used to group data by field and perform aggregation operations, and HAVING is used to filter the results after grouping. For example, using GROUPBYcustomer_id can calculate the total consumption amount of each customer; using HAVING can filter out customers with a total consumption of more than 1,000. The non-aggregated fields after SELECT must appear in GROUPBY, and HAVING can be conditionally filtered using an alias or original expressions. Common techniques include counting the number of each group, grouping multiple fields, and filtering with multiple conditions.

MySQL paging is commonly implemented using LIMIT and OFFSET, but its performance is poor under large data volume. 1. LIMIT controls the number of each page, OFFSET controls the starting position, and the syntax is LIMITNOFFSETM; 2. Performance problems are caused by excessive records and discarding OFFSET scans, resulting in low efficiency; 3. Optimization suggestions include using cursor paging, index acceleration, and lazy loading; 4. Cursor paging locates the starting point of the next page through the unique value of the last record of the previous page, avoiding OFFSET, which is suitable for "next page" operation, and is not suitable for random jumps.

To view the size of the MySQL database and table, you can query the information_schema directly or use the command line tool. 1. Check the entire database size: Execute the SQL statement SELECTtable_schemaAS'Database',SUM(data_length index_length)/1024/1024AS'Size(MB)'FROMinformation_schema.tablesGROUPBYtable_schema; you can get the total size of all databases, or add WHERE conditions to limit the specific database; 2. Check the single table size: use SELECTta

To set up asynchronous master-slave replication for MySQL, follow these steps: 1. Prepare the master server, enable binary logs and set a unique server-id, create a replication user and record the current log location; 2. Use mysqldump to back up the master library data and import it to the slave server; 3. Configure the server-id and relay-log of the slave server, use the CHANGEMASTER command to connect to the master library and start the replication thread; 4. Check for common problems, such as network, permissions, data consistency and self-increase conflicts, and monitor replication delays. Follow the steps above to ensure that the configuration is completed correctly.

MySQL supports transaction processing, and uses the InnoDB storage engine to ensure data consistency and integrity. 1. Transactions are a set of SQL operations, either all succeed or all fail to roll back; 2. ACID attributes include atomicity, consistency, isolation and persistence; 3. The statements that manually control transactions are STARTTRANSACTION, COMMIT and ROLLBACK; 4. The four isolation levels include read not committed, read submitted, repeatable read and serialization; 5. Use transactions correctly to avoid long-term operation, turn off automatic commits, and reasonably handle locks and exceptions. Through these mechanisms, MySQL can achieve high reliability and concurrent control.

Character set and sorting rules issues are common when cross-platform migration or multi-person development, resulting in garbled code or inconsistent query. There are three core solutions: First, check and unify the character set of database, table, and fields to utf8mb4, view through SHOWCREATEDATABASE/TABLE, and modify it with ALTER statement; second, specify the utf8mb4 character set when the client connects, and set it in connection parameters or execute SETNAMES; third, select the sorting rules reasonably, and recommend using utf8mb4_unicode_ci to ensure the accuracy of comparison and sorting, and specify or modify it through ALTER when building the library and table.
