Initialize Varbinary datatype in MySQL

Question

In MySQL, I want to declare a default value to a varbinary column. How do I achieve it ?

DECLARE result varbinary(8000);
SET result   = 0x;

I used select CHAR_LENGTH(00101) and it gives me a result 3. I am expecting my result to be 5 (number of characters in string). To measure the length of a varbinary string, how do I do it ?

CHAR_LENGTH(00101) is CHAR_LENGTH(101) which is 3. CHAR_LENGTH('00101') is 5. Try: SELECT 00101. — tadman
– tadman, Commented Apr 27, 2016 at 18:00
ThnQ, it works. Any idea on How to declare a varbinary column and set a default binary value ! — PK_Ask
– PK_Ask, Commented Apr 27, 2016 at 18:59

tadman · Accepted Answer · 2016-04-27 19:38:49Z

3

When you create your table you can specify a default. For binary data you should probably express it as a hex string, 0x... style:

CREATE TABLE binary_default (
  id INT PRIMARY KEY AUTO_INCREMENT,
  binary_data VARBINARY(8000) DEFAULT 0x010203
);

You can test this works with:

INSERT INTO binary_default VALUES ()

Then fetch, but as it's binary, you might want a hex view:

SELECT id, HEX(binary_data) FROM binary_default

answered Apr 27, 2016 at 19:38

tadman

212k23 gold badges237 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

PK_Ask Over a year ago

Creation of a Table with varbinary is not the intention. I am trying to write a function. so i want to declare and initialize a variable with varbinary. After that i want to perform some computation on it. For Example: CREATE FUNCTION test DECLARE result varbinary(8000); SET result = 0x; begin select result = result + 00101; return result; end; MySQL is throwing a error at initilizing part, how do i overcome it and perform the computation ?

tadman Over a year ago

What does 00101 even mean in this context?

PK_Ask Over a year ago

Its a binary format'00101', that i want to add to 0x(result varibale) which i initialized

tadman Over a year ago

I know what you're trying to do, but SELECT 00101 proves that MySQL has no idea what you're talking about. You need to use a notation that MySQL understands or this will never work. That's why I used the 0x notation.

Collectives™ on Stack Overflow

Initialize Varbinary datatype in MySQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related