Mysql – Error on updating a field with binary values

blobhexMySQLmysql-5.7varchar

I have a problem on emoji showing on webpage after migration to new DB Server. But I have a workaround. I will get the binary values of a certain field of TABLE1 on our OLD DB Server. Then update the TABLE1 on the new server using the script below:

UPDATE old_table t1 INNER JOIN new_table t2 ON t1.id = t2.id
SET t2.message = CAST(CONCAT("0x",HEX(t1.message)) AS BINARY);

But after executing this my message field on the new table becomes "0x1E395565"

If I do this script below, it will show the real message. But doing this for a million of records will take too long to execute hence the UPDATE INNER JOIN:

UPDATE new_table SET message = 0x1E395565 where id =1;

How to update this correctly? I concat "0x" on the HEX(t1.message) because when I do a mysqldump –hex-blob and charset=BINARY. There are 0x on the values.

My data type for message field is VARCHAR(1000) I know this might be wrong but is there any workaround without altering the table.

Best Answer

Simply use UNHEX("1E395565").

There could be a lot of other issues going on. So please provide SHOW CREATE TABLE for the old and new tables. Also provide a sample of what is in the old table, but use

SELECT col, HEX(col) FROM ... WHERE ...

so we can see the value in an unambiguous way. (That is, not mangled or "fixed" by the client.)

Where does 1E395565 come from? It is not anything meaningful (not utf8, etc) that I recognize.

Related Solutions

Mysql – Not able to create a new table new_table from t1 by comparing primary key values of another table t2 in MYSQL

I'd have to assume that no matching values exist in the tables' respective primary key columns.

mysql> create database testing;
Query OK, 1 row affected (0.02 sec)

mysql> use testing;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> create table foo (id int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table bar (id int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table baz (id int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into foo values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into bar values (4), (5), (6);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into baz select a.* from foo a join bar b on a.id = b.id;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into bar values (1), (2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into baz select a.* from foo a join bar b on a.id = b.id;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>

Sql-server – Selecting with varbinary(max) criteria (in the where clause)

Just do

SELECT ItemId
FROM   MyTable
WHERE  Value = 0xAAFF

You are converting the varchar representation to varbinary(max) which is not correct.

SELECT CONVERT(VARBINARY(MAX), '0xAAFF') returns 0x307841414646 for me for example. It will give you a result based on the character codes in that string representation in your default collation's code page.

Best Answer

Related Solutions

Mysql – Not able to create a new table new_table from t1 by comparing primary key values of another table t2 in MYSQL

Sql-server – Selecting with varbinary(max) criteria (in the where clause)

Related Question