Mysql – MariaDB CONNECT engine BIN table – file record layout, storage of a date data type, padding

datefilesmariadbMySQL

In mariadb-10.4.5, running on Windows 10, I created this table (in the local database):

`CREATE TABLE IF NOT EXISTS `testbalNoFieldFormat` (
  `fig` int(4) NOT NULL,
  `name` char(10) NOT NULL,
  `birth` date NOT NULL,
  `id` char(5) NOT NULL,
  `salary` double(9,2) NOT NULL DEFAULT 0.00,
  `dept` int(4) NOT NULL
) ENGINE=CONNECT DEFAULT CHARSET=utf8 `table_type`=BIN `block_size`=5 `file_name`='TestbalNoFieldFormat.dat'

INSERT INTO testbalNoFieldFormat VALUES(1,"1234567890",'2009-01-20','abcde',100000.12,1234)

I inspected the file in Binary Viewer from https://www.proxoft.com/BinaryViewer.aspx.

In Binary Viewer (enclosed in double quotes so you can see the start and end):

As individual bytes viewed as unsigned integers, not octals:

"   1   0   0   0   49  50  51  52  53  54  55  56  57  48  32  32
    32  32  32  32  32  32  32  32  32  32  32  32  32  32  32  32
    32  32  48  93  116 73  97  98  99  100 101 32  32  32  32  32
    32  32  32  32  32  184 30  133 235 1   106 248 64  210 4   0
    0"

As ASCII, it is:

"   .   .   .   .   1   2   3   4   5   6   7   8   9   0        

            0   ]   t   I   a   b   c   d   e                    
                        ?   .   ?   ?   .   j   ?   @   ?   .   .
    ."

Grouped into 4-byte integers (Little Endian), the bytes are:

"   1           875770417   943142453   538980409
    538976288   538976288   538976288   538976288
    1563435040  1650542964  543515747   538976288
    538976288   2233382944  4167696875  315968
    538976256"

I can see the first int value that I inserted (first field, value 1) but not the second int value that I inserted (last field, value 1234). Possibly there is some padding affecting things but 65 bytes seems an odd number to pad to – I was thinking it would pad to a multiple of 8 bytes, or 4 bytes.

The file length is 65 bytes, vs 46 bytes for the previous table (testbal).

Can anyone interpret the byte structure for me? In particular, which bytes represent fields 3 (birth), 5 (salary), 6 (int)?

Which bytes represent padding?

How can I extract the year, month and day of month from the bytes representing the value of the date field 'birth'?

Best Answer

1   0   0   0  -- 1; note "little-endian"
49  50  51  52  53  54  55  56  57  48  -- ascii/utf8 for "1234567890"
32  32  32  32  32  32  32  32  32  32
32  32  32  32  32  32  32  32  32  32 -- lots of spaces for padding
48  93  116 73   -- must be the date?
97  98  99  100 101   -- ascii/utf8 for 'abcde'
32  32  32  32  32
32  32  32  32  32   -- lots of spaces for padding
184 30  133 235 1   106 248 64  -- 100000.12 --> hex: 40 f8 6a 01 eb 85 1e b8
210 4   0   0  -- 1234 = 210 + 4*2^8 + 0 * 2^16 + 0 * 2*24

On INT(4), the (4) has no meaning (unless you also use ZEROFILL). INT is always 4 byte, but not because of (4).

DOUBLE is always 8 bytes; (m,n) should not be used.

The spaces are padding CHAR(n) CHARACTER SET utf8 out to 3 bytes per character. This is an argument for almost always using VARCHAR, not CHAR.

With utf8mb4 there would be even more spacing.

Related Question