Inserting binary data results in "Data too long for column..."-CodePudding

I have a MySQL table created with the following code:

CREATE TABLE `uuid` (
    `id` BINARY(16) NOT NULL,
    PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

I'm trying to insert a binary value to the id record.

If I do SELECT UUID_TO_BIN('7be7daf5-73ef-4f73-a784-46ea44403293',1); it shows me Ossï{çÚõ§„FêD@2“

Now if I try to insert that output with this query INSERT INTO uuid VALUES ('Ossï{çÚõ§„FêD@2“'); I get SQL Error (1406): Data too long for column 'id' at row 1.

This question might seems wired but that is how I receive my binary data from the back-end and I'm trying to troubleshoot here. Any help why I'm getting that error please?

Thanks.

CodePudding user response：

The reason for the length discrepancy is that some of the weird characters produced by your binary output are being encoded as UTF-8 characters, which take more than one byte per character.

For example, look at the first five characters, "Ossï{"

mysql> select hex(UUID_TO_BIN('7be7daf5-73ef-4f73-a784-46ea44403293',1)) as hex;
 ---------------------------------- 
| hex                              |
 ---------------------------------- 
| 4F7373EF7BE7DAF5A78446EA44403293 |
 ---------------------------------- 
  O s s ï {

The byte EF is the Latin1 encoding for ï, so the nearest printable character to that byte is output as ï.

But then you copy & paste the string, and it gets converted to UTF-8 encoding, where ï has a multibyte sequence.

mysql> select hex('Ossï{çÚõ§„FêD@2“') as hex;
 ------------------------------------------------------ 
| hex                                                  |
 ------------------------------------------------------ 
| 4F7373C3AF7BC3A7C39AC3B5C2A7E2809E46C3AA444032E2809C |
 ------------------------------------------------------ 
  O s s ï   {

You can see that ï is encoded in UTF-8 as C3AF (two bytes), followed by the { as 7B.

There are several other multibyte characters in this string; I'm only calling attention to the first one.

When you copy & paste a string with funny non-ASCII characters, it's not as binary bytes, it's a string of UTF-8 characters, some of which are multibyte.

If you avoid copying & pasting the string, you can get the expected length of binary bytes:

mysql> SELECT LENGTH(UUID_TO_BIN('7be7daf5-73ef-4f73-a784-46ea44403293',1)) as len;
 ------ 
| len  |
 ------ 
|   16 |
 ------

So use that when you insert into your table:

mysql> insert into uuid set id = 'Ossï{çÚõ§„FêD@2“';
ERROR 1406 (22001): Data too long for column 'id' at row 1

mysql> insert into uuid set id = UUID_TO_BIN('7be7daf5-73ef-4f73-a784-46ea44403293',1);
Query OK, 1 row affected (0.00 sec)

CodePudding user response：

If you perform those two queries on the command line, character set conversion might be a problem. You can insert the value directly:

INSERT INTO `uuid` VALUES (UUID_TO_BIN('7be7daf5-73ef-4f73-a784-46ea444032
93',1));

or, if you want to have separate queries, convert it using e.g. hex and unhex:

SELECT hex(UUID_TO_BIN('7be7daf5-73ef-4f73-a784-46ea44403293',1));

results in 4F7373EF7BE7DAF5A78446EA44403293.

INSERT INTO `uuid` VALUES (unhex('4F7373EF7BE7DAF5A78446EA44403293'));

CodePudding user response：

there is 17 character if the last character ** Ossï{çÚõ§„FêD@2“ ** is double-quotation and 18 character if its double single-quotation.

So its 17 or 18 B and you only just- defined it as 16B.

Just check how length the ID is.