Why can't I insert certain characters into a MySQL table?

Question

I am trying to do a MySQL insert and I am getting an error message like this:

Incorrect string value: '\xA0' for column 'foo' at row 1

This happens with both \xA0 and \x96.

I am using PHP and I am passing this query to mysqli_query():

INSERT INTO ttest1 (foo) VALUES("test\xA0")

Here's the create table:

CREATE TABLE ttest1 (
    test_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    foo VARCHAR(32))

Here is the character set and collation info for MySQL:

character_set_client        utf8mb4
character_set_connection    utf8mb4
character_set_database      utf8
character_set_filesystem    binary
character_set_results       utf8mb4
character_set_server        utf8
character_set_system        utf8

collation_connection        utf8mb4_unicode_ci
collation_database          utf8_general_ci
collation_server            utf8_general_ci

The collation for the foo column is uft8_general_ci.

So why am I getting that error with this setup and what is the best fix?

Michael - sqlbot Michael - sqlbot · Accepted Answer · 2017-04-13T23:07:38

There are no valid utf8 encodings between \x80 and \xFF, so the error is correct.

Code point 160 (0xA0) (also known as   or  ), in utf8, is a 2-byte character, encoded as 0xC2 0xA0.

Similarly, code point 150 (0x96) is encoded as 0xC2 0x96.

Encode your string with valid utf8 is the correct solution.

I'm a MySQL DBA, not a PHP expert (my bronze PHP tag badge notwithstanding) but apparently PHP 7 introduced unicode codepoint escape literals so you can apparently use "\u{a0}".

Why can't I insert certain characters into a MySQL table?

1 Answers