1

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'?

  • (Displaying in HEX would have made analysis easier.) – Rick James Jul 6 at 20:25
0
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.

  • I don't get why padding would work this way - the characters at the start, effectively unpadded, followed by the spaces. I thought padding would be character, space, space, character, space, space, etc? – Dreldragon Jul 9 at 9:30
  • Can you elaborate on why char behaves this way and varchar doesn't? I appreciate the former is fixed-length and the latter is variable, but I don't get how this influences per-character padding. – Dreldragon Jul 9 at 9:33
  • Rick I approved your answer and it says it is recorded but because I have less than 15 reputation it won't display my approval. Sorry about that :-( – Dreldragon Jul 9 at 9:35
  • @Dreldragon - As for VARCHAR, give it a try. I am interpreting the bytes that you displayed; I know nothing about Connect. VARCHAR would need one of (1) turn into CHAR to be a predictable length, (2) have a length field up front (which is what happens internally in MyISAM and InnoDB), or (3) have a terminator (but this would fail for VARBINARY). – Rick James Jul 9 at 14:52
  • @Dreldragon - It seems that Connect chose to turn everything into fixed length for transmission. Given that, it had to choose what to do with utf8 characters that were shorter than 3 bytes. Letter+pad+pad would be alien to all utf8 formulations, so that would be unwise. (Note: utf16 is sort of letter+zerobyte.) The padding choice of spaces (32) instead of zerobytes seems arbitrary. – Rick James Jul 9 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.