CODEWITHSUNDEEP
mysqlmysql-workbench
Greg
Greg

Reputation: 7922

Editing row data not possible with binary primary key?

I'm using binary uuids for keys. Is there any way to edit table data with Mysql Workbench for this kind of schema? I end up with:

UPDATE `db`.`table` SET `foo`='bar' WHERE `uuid`=?;

Which obviously returns an error:

ERROR 0: Value not set for all parameters

I have no other way to reference the rows I want to edit.

PhpMyAdmin fails at this as well by corrupting all binary data.

edit - to clarify, the actual keys' data type is BINARY(16)

edit 2 - To clarify even more, this question is specifically about MySQL Workbench. I understand prepared statements.

edit 3 - I'm putting a bounty on this in hopes that someone knows a workaround or solution.

Upvotes: 3

Views: 1362

Answers (1)

stewe
stewe

Reputation: 42654

You could show your table using:

SELECT *,HEX(uuid) FROM `db`.`table`;

example output:

uuid               foo      HEX(uuid)
---------------------------------------------------------------
E��|M_jE��|M_j     test     45ABFA057C4D5F6A45ABFA057C4D5F6A
.
.

then you could update it using:

UPDATE `db`.`table` SET `foo`='bar' WHERE `uuid`=UNHEX('45ABFA057C4D5F6A45ABFA057C4D5F6A');

or

UPDATE `db`.`table` SET `foo`='bar' WHERE `uuid`=CAST(0x45ABFA057C4D5F6A45ABFA057C4D5F6A AS BINARY);

Upvotes: 8

Related Questions