2

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 ?

Olivier De Meulder
  • 2,493
  • 3
  • 25
  • 30
Oggu
  • 323
  • 1
  • 6
  • 18
  • 2
    `CHAR_LENGTH(00101)` is `CHAR_LENGTH(101)` which is 3. `CHAR_LENGTH('00101')` is 5. Try: `SELECT 00101`. – tadman Apr 27 '16 at 18:00
  • ThnQ, it works. Any idea on How to declare a varbinary column and set a default binary value ! – Oggu Apr 27 '16 at 18:59

1 Answers1

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
tadman
  • 208,517
  • 23
  • 234
  • 262
  • 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 ? – Oggu Apr 29 '16 at 14:10
  • What does `00101` even mean in this context? – tadman Apr 29 '16 at 15:28
  • Its a binary format'00101', that i want to add to 0x(result varibale) which i initialized – Oggu May 02 '16 at 14:26
  • 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. – tadman May 02 '16 at 16:53