2.1.3.2 NDB API Handling of MySQL Data Types

This section provides information about the way in which MySQL data types are represented in NDBCLUSTER table columns and how these values can be accessed in NDB API applications.

Numeric data types.  The MySQL TINYINT, SMALLINT, INT, and BIGINT data types map to NDB types having the same names and storage requirements as their MySQL counterparts.

The MySQL FLOAT and DOUBLE data types are mapped to NDB types having the same names and storage requirements.

Data types used for character data.  The storage space required for a MySQL CHAR column is determined by the maximum number of characters and the column's character set. For most (but not all) character sets, each character takes one byte of storage. When using utf8, each character requires three bytes; utfmb4 uses up to four bytes per character. You can find the maximum number of bytes needed per character in a given character set by checking the Maxlen column in the output of SHOW CHARACTER SET.

An NDB VARCHAR column value maps to a MySQL VARCHAR, except that the first two bytes of the NDB VARCHAR are reserved for the length of the string. A utility function like that shown here can make a VARCHAR value ready for use in an NDB API application:

void make_ndb_varchar(char *buffer, char *str)
{
  int len = strlen(str);
  int hlen = (len > 255) ? 2 : 1;
  buffer[0] = len & 0xff;
  if( len > 255 )
    buffer[1] = (len / 256);
  strcpy(buffer+hlen, str);
}

You can use this function as shown here:

char myVal[128+1]; // Size of myVal (+1 for length)
...
make_ndb_varchar(myVal, "NDB is way cool!!");
myOperation->setValue("myVal", myVal);

See Section 2.5.12, “NDB API Simple Array Example”, for a complete example program that writes and reads VARCHAR and VARBINARY values to and from a table using the NDB API.

MySQL storage requirements for a VARCHAR or VARBINARY column depend on whether the column is stored in memory or on disk:

• For in-memory columns, the NDB storage engine supports variable-width columns with 4-byte alignment. This means that (for example) a the string 'abcde' stored in a VARCHAR(50) column using the latin1 character set requires 12 bytes—in this case, 2 bytes times 5 characters is 10, rounded up to the next even multiple of 4 yields 12.

• For Disk Data columns, VARCHAR and VARBINARY are stored as fixed-width columns. This means that each of these types requires the same amount of storage as a CHAR of the same size.

Note

We refer throughout this Guide to a column of any of MySQL's TEXT or BLOB types as a “blob column”, and its type as “blob”. NDB 7.5 and later also treats MySQL JSON columns as blob columns.

Each row in an NDB Cluster BLOB or TEXT column is made up of two separate parts. One of these is of fixed size (256 bytes), and is actually stored in the original table. The other consists of any data in excess of 256 bytes, which is stored in a hidden blobs table whose rows are always 2000 bytes long. This means that a record of size bytes in a TEXT or BLOB column requires

• 256 bytes, if size <= 256

• 256 + 2000 * ((size – 256) \ 2000) + 1) bytes otherwise

Temporal data types.  Storage of temporal types in the NDB API depends on whether MySQL's “old” types without fractional seconds or “new” types with fractional second support are used. Support for fractional seconds was introduced in MySQL 5.6 as well as the NDB Cluster versions based on it—that is, NDB 7.3 and NDB 7.4. These and later versions of the MySQL Server and NDB Cluster use the new temporal types by default, can read and write data using the old temporal types, but cannot create tables that use the old types. See Fractional Seconds in Time Values, for more information.

Because support for the old temporal types is expected be removed in a future release, you are encouraged to migrate any tables using the old temporal types to the new versions of these types. You can do this by executing a copying ALTER TABLE operation on any table that uses the old temporals, or by backing up and restoring any such tables.

You can see whether a given table uses the old or new temporal types by checking the output of the ndb_desc utility supplied with the NDB Cluster distribution. Consider a table created in a database named test, using the following statement, on a mysqld started without the --create-old-temporals option:

CREATE TABLE t1 (
    c1 DATETIME,
    c2 DATE,
    c3 TIME,
    c4 TIMESTAMP,
    c5 YEAR) ENGINE=NDB;

The relevant portion (the Attributes block) of the output of ndb_desc looks like this:

$> ndb_desc -dtest t1
...
-- Attributes --
c1 Datetime2(0) NULL AT=FIXED ST=MEMORY
c2 Date NULL AT=FIXED ST=MEMORY
c3 Time2(0) NULL AT=FIXED ST=MEMORY
c4 Timestamp2(0) NOT NULL AT=FIXED ST=MEMORY DEFAULT 0
c5 Year NULL AT=FIXED ST=MEMORY

The names of the new MySQL temporal types are suffixed with 2 (for example, Datetime2) to set them apart from the old versions of these types. Assume that we restart mysqld with --create-old-temporals=ON and then create a table t2, also in the test database, using this statement:

CREATE TABLE t2 (
    c1 DATETIME,
    c2 DATE,
    c3 TIME,
    c4 TIMESTAMP,
    c5 YEAR) ENGINE=NDB;

The output from executing ndb_desc on this table as shown includes the Attributes block shown here:

$> ndb_desc -dtest t2
...
-- Attributes --
c1 Datetime NULL AT=FIXED ST=MEMORY
c2 Date NULL AT=FIXED ST=MEMORY
c3 Time NULL AT=FIXED ST=MEMORY
c4 Timestamp NOT NULL AT=FIXED ST=MEMORY DEFAULT 0
c5 Year NULL AT=FIXED ST=MEMORY

The affected MySQL types are TIME, DATETIME, and TIMESTAMP. The “new” versions of these types are reflected in the NDB API as Time2, Datetime2, and Timestamp2, respectively, each supporting fractional seconds with up to 6 digits of precision. The new variants use big-endian encoding of integer values which are then processed to determine the components of each temporal type.

For the fractional second part of each of these types, the precision affects the number of bytes needed, as shown in the following table:

Table 2.2 Precision of NDB API new temporal types

Precision	Bytes required	Range
0	0	—
1	1	0-9
2	1	0-99
3	2	0-999
4	2	0-9999
5	3	0-99999
6	3	0-999999

The fractional part for each of the new temporal types is stored in big-endian format—that is, with the highest order byte at the lowest address—using the necessary number of bytes.

The binary layouts of both the old and new versions of these types are described in the next few paragraphs.

Time: The “old” version of this type is stored as a 24-bit signed int value stored in little-endian format (lowest order byte in lowest order address). Byte 0 (bits 0-7) corresponds to hours, byte 2 (bits 8-15) to minutes, and byte 2 (bits 16-23) to seconds according to this formula:

value =   10000 * hour
        + 100 * minute
        + second

Bit 23 serves as the sign bit; if this bit is set, the time value is considered negative.

Time2: This is the “new” TIME type, and is stored as a 3-byte big-endian encoded value plus 0 to 3 bytes for the fractional part. The integer part is encoded as shown in the following table:

Table 2.3 Time2 encoding

Bits	Meaning	Range
23	Sign bit	0-1
22	Interval	0-1
22-13	Hour	1-1023
12-7	Minute	0-63
6-0	Second	0-63

Any fractional bytes in addition to this are handled as described previously.

Date: The representation for the MySQL DATE type is unchanged across NDB versions, and uses a 3-byte unsigned integer stored in little-endian order. The encoding is as shown here:

Table 2.4 Date encoding

Bits	Meaning	Range
23-9	Year	0-32767
8-5	Month	0-15
4-0	Day	0-31

Datetime: The “old” MySQL DATETIME type is represented by a 64-bit unsigned value stored in host byte order, encoded using the following formula:

value   = second
        + minute  * 102
        + hour    * 104
        + day     * 106
        + month   * 108
        + year    * 1010

DateTime2: The “new” DATETIME is encoded as a 5-byte big-endian with an optional fractional part of 0 to 3 bytes, the fractional portion being handled as described previously. The high 5 bytes are encoded as shown here:

Table 2.5 DateTime2 encoding

Bits	Meaning	Range
23	Sign bit	0-1
22	Interval	0-1
22-13	Hour	1-1023
12-7	Minute	0-63
6-0	Second	0-63

The YearMonth bits are encoded as Year = YearMonth / 13 and Month = YearMonth % 13.

Timestamp: The “old” version of this type uses a 32-bit unsigned value representing seconds elapsed since the Unix epoch, stored in host byte order.

Timestamp2: This is the “new” version of TIMESTAMP, and uses 4 bytes with big-endian eoncoding for the integer potion (unsigned). The optional 3-byte fractional portion is encoded as exaplined earlier in this section.

Additional information.  More information about and examples uding data types as expressed in the NDB API can be found in ndb/src/common/util/NdbSqlUtil.cpp. In addition, see Section 2.5.14, “Timestamp2 Example”, which provides an example of a simple NDB API application that makes use of the Timestamp2 data type.