Can't insert Chinese character into MySQL

Can't insert Chinese character into MySQL

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Can't insert Chinese character into MySQL

The error stems from a mismatch between how your application encodes data and how MySQL expects to receive it. In your specific case, the reason1 field is failing because the cookie is encoded using Big5, a traditional character set primarily designed for Traditional Chinese. When you attempt to insert this raw string directly into a database that likely defaults to UTF-8 or Latin1, the byte sequences representing Chinese characters become corrupted. This corruption typically manifests as "mojibake"—those unreadable symbols like ⸙ or ¿½¿½—or triggers a character set conversion error entirely.

To resolve this, you must ensure that the data is normalized to a single, consistent encoding before it ever reaches the database driver. The standard solution is UTF-8, as it supports both Traditional and Simplified Chinese characters alongside English text, making it the universal language for modern web applications.

Inspecting the Current State

Before modifying your code, you need to verify the character set configuration of your target table. Run the following command against your MySQL instance:

SHOW CREATE TABLE attendance_count;

Look closely at the output near the bottom where it says Character set: utf8. If it reports latin1 or gbk, that is the root cause of the insertion failure. Additionally, check the session variable:

SELECT @@character_set_client, @@character_set_connection;

If these return a value other than utf8 (or utf8mb4), MySQL will attempt to convert incoming data on the fly, which often results in silent data loss for non-ASCII characters.

Fixing the Application Layer

The most robust fix involves sanitizing the input on the server side before executing the query. Since you are using Big5 specifically from a cookie, you should explicitly decode and re-encode the string to UTF-8 before passing it to MySQL.

In your PHP script, replace the direct insertion logic with this approach:

// Ensure the incoming cookie is treated as UTF-8 regardless of its origin
$reason1 = mb_convert_encoding($_COOKIE["reason"], "UTF-8", "BIG5");

// It is highly recommended to use prepared statements to prevent SQL injection
// and allow the database driver to handle character set binding correctly.
$stmt = $mysqli->prepare("INSERT INTO attendance_count (username, date1, count_time, appendix) VALUES (?, ?, 0, ?)");
$stmt->bind_param("ssi", $username, $date1, $reason1);
$stmt->execute();

Note the use of mb_convert_encoding. This function handles the byte-level translation from Big5 to UTF-8. Without this step, the database receives raw Big5 bytes. If your table is configured as UTF-8, MySQL's server-side conversion logic might fail if the sequence is invalid in that specific charset context, or it might truncate the string.

Database Schema Configuration

Even after fixing the PHP code, you must ensure the database schema is ready to accept the data. Run the following SQL commands to enforce UTF-8 on the table and columns:

ALTER TABLE attendance_count CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER COLUMN appendix TYPE VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

I recommend using utf8mb4 over the legacy utf8. While standard utf8 in MySQL is compatible with UTF-8, it only supports up to 3-byte characters. The emoji character sequence requires 4 bytes, and some rare Chinese characters can also exceed 3 bytes. Using utf8mb4 ensures full compliance with the Unicode standard and prevents future issues if you decide to store emojis or special symbols in that field.

Trade-offs and Pitfalls

A common pitfall is relying solely on database configuration without fixing the application input. If your application sends Big5 bytes but the database expects UTF-8, MySQL will attempt a conversion. However, this conversion is not always lossless. If a character cannot be mapped perfectly during conversion, data integrity is compromised. Always normalize at the application boundary.

Furthermore, avoid using mysql_query() as shown in your source snippet. That extension is deprecated and removed in newer PHP versions. It also lacks support for prepared statements, which are essential for safely handling user input like $reason1. Switching to PDO or mysqli with prepared statements not only secures your application against SQL injection but also allows you to explicitly bind parameters with specific character sets, ensuring the driver handles the encoding correctly during transmission.

By aligning your cookie decoding logic with a UTF-8 compatible database schema and adopting modern PHP practices, you eliminate the character set mismatch that is currently blocking your data insertion.

Our network