Documentation Home
MySQL NDB Cluster API Developer Guide
Download this Manual

MySQL NDB Cluster API Developer Guide  /  ...  /  Mapping MySQL Database Object Names and Types to NDB

2.1.3 Mapping MySQL Database Object Names and Types to NDB


This section discusses NDB naming and other conventions with regard to database objects.

Databases and Schemas.  Databases and schemas are not represented by objects as such in the NDB API. Instead, they are modelled as attributes of Table and Index objects. The value of the database attribute of one of these objects is always the same as the name of the MySQL database to which the table or index belongs. The value of the schema attribute of a Table or Index object is always 'def' (for default).

Tables.  MySQL table names are directly mapped to NDB table names without modification. Table names starting with 'NDB$' are reserved for internal use, as is the SYSTAB_0 table in the sys database.

Indexes.  There are two different type of NDB indexes:

  • Hash indexes are unique, but not ordered.

  • B-tree indexes are ordered, but permit duplicate values.

Names of unique indexes and primary keys are handled as follows:

  • For a MySQL UNIQUE index, both a B-tree and a hash index are created. The B-tree index uses the MySQL name for the index; the name for the hash index is generated by appending '$unique' to the index name.

  • For a MySQL primary key only a B-tree index is created. This index is given the name PRIMARY. There is no extra hash; however, the uniqueness of the primary key is guaranteed by making the MySQL key the internal primary key of the NDB table.

Column Names and Values.  NDB column names are the same as their MySQL names.

Data Types.  MySQL data types are stored in NDB columns as follows:

  • 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.

  • 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.11, “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.

  • 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 stored in a hidden table. The rows in this second table are always 2000 bytes long. This means that record of size bytes in a TEXT or BLOB column requires

    • 256 bytes, if size <= 256

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