Varbinary to char/varchar in MySQL

lukinhaspm's profile image lukinhaspm posted 8 years ago in General Permalink

Hello,

It's possible to display in the grid, columns Varbinary with cast to char? And accept the char value and after convert to Varbinary when we save the row?

If I use PHP to call the row I haven't this "problem", I think the PHP auto transform the fields.

Sample:

Table structure CREATE TABLE usuarios ( usuarios_id SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Id', usuarios_login VARBINARY(255) NULL DEFAULT NULL COMMENT 'Login', PRIMARY KEY (usuarios_id) ) COMMENT='Usuários' COLLATE='latin1_swedish_ci' ;

What its good to show:

select usuarios_login, convert(usuarios_login, char(255)) from usuarios

Print Varbinary -> Char:

kalvaro's profile image kalvaro posted 8 years ago Permalink

What's the use case of storing plain text in a binary column?

Even if we make the assumption that the column is using the table encoding (as such, a binary column doesn't have any encoding because it isn't text) it feels like a data-loss waiting to happen.

lukinhaspm's profile image lukinhaspm posted 8 years ago Permalink

hello kalvaro,

Maybe I'm not use correctly the Varbinary type.

In my sample, I'm using to make the column case sensitive without change the table/column collate, I was make a research and I saw I can use the sintax "binary ´column´ = 'condition' " and my problem is solved.

I try to find plan texts in varbinary columns with where, and MySQL accepted the search. When I make the select in my table using mysql comand line, the column with varbinary comes with plan text.

Anyway, I will change my column to varchar and transform when I need to call into my login page.

I think Heidi could display plan text in this case, or make it a option... only ideas

Thanks

kalvaro's profile image kalvaro posted 8 years ago Permalink

Whatever, in your precise example I think you already have what you want: just click the "View binary data as text" in main toolbar (sorry, I overlooked that).

lukinhaspm's profile image lukinhaspm posted 8 years ago Permalink

WOW!

It's great! I will use it!

Thanks a lot!

Please login to leave a reply, or register at first.