What does VARBINARY(MAX) mean?

Question

I'm trying to port a MSSQL database over to MariaDB and I've encountered a table creation using varbinary(max):

    `definition` VARBINARY(max) NULL DEFAULT NULL

What would this actually do and is there an equivalent type definition in MariaDB that I could use?

[The BINARY and VARBINARY Types](https://dev.mysql.com/doc/refman/5.0/en/binary-varbinary.html) — Lukasz Szozda, Oct 21 '15 at 18:38
The first hit on google with the search terms `varbinary(max)` goes to [this page](https://msdn.microsoft.com/en-us/library/ms188362.aspx?f=255&MSPPError=-2147217396). From there: *Variable-length binary data. n can be a value from 1 through 8,000. **max indicates that the maximum storage size is 2^31-1 bytes*** — Lamak, Oct 21 '15 at 18:39

score 6 · Accepted Answer · edited Jun 11 '20 at 18:16

6

As others have stated in the comments, VARBINARY(max) in MSSQL refers to:

Variable-length binary data.

max indicates that the maximum storage size is 2^31-1 bytes.

From what I found in MariaDB's documentation, the only way of getting a similar storage size in MariaDB is to use the LONGBLOB data type:

LONGBLOB

A BLOB column with a maximum length of 4,294,967,295 bytes or 4GB (2^32 - 1).

Useful links:

edited Jun 11 '20 at 18:16

answered Oct 21 '15 at 18:50

eugenioy

Well.. i found the VarBinary type in MySql workbench tool (When i create table) too. MySql is very similar with maria db so, i guess that using long blob is not the only way in maria(or mysql). – HelloWorld Nov 20 '18 at 07:59
When dealing with strings, what encoding type does MSSQL use for `varbinar(max)`? Is it `utf16le`? I'm running SQL Server 2016. – NamedArray Aug 12 '21 at 16:13

score 3 · Answer 2 · answered Oct 21 '15 at 18:49

3

In SQL Server, VARBINARY is variable length binary data, the MAX scale value means it will store up to the maximum 2^31-1 bytes.

I think the closest equivalent MariaDB data type will be LONGBLOB, which can store up to 2^32-1 bytes.

answered Oct 21 '15 at 18:49

Nathan Griffiths

2 Answers2