MemSQL supports the following data types. The sizes given are for in-memory rowstore tables. Columnstore (on-disk) tables are stored differently, and often compressed.

Integer Numbers

Note:

• BOOL and BOOLEAN are synonymous with TINYINT. A value of 0 is considered FALSE, non-zero values are considered TRUE.

Real Numbers

Time and Date

Inserting or Updating a Column With the Current Timestamp

To allow inserts or updates of a TIMESTAMP or TIMESTAMP(6) column with the current timestamp, define the column using the DEFAULT CURRENT_TIMESTAMP clause or ON UPDATE CURRENT_TIMESTAMP clause. Use the former clause to allow inserts of the current timestamp and the latter clause to allow updates of the current timestamp.

The following example demonstrates the use of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP in column definitions:

memsql> CREATE TABLE s(id INT, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
ts2 TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
ts3 TIMESTAMP);
memsql> INSERT INTO s(id) VALUES(1);
memsql> INSERT INTO s(id) VALUES(2);
memsql> SELECT * FROM s;
+------+---------------------+----------------------------+------+
| id   | ts                  | ts2                        | ts3  |
+------+---------------------+----------------------------+------+
|    1 | 2020-02-20 11:18:02 | 2020-02-20 11:18:02.822309 | NULL |
|    2 | 2020-02-20 11:18:13 | 2020-02-20 11:18:13.767739 | NULL |
+------+---------------------+----------------------------+------+

memsql> CREATE TABLE r(d DATETIME(6), ts TIMESTAMP(6), id INT);

memsql> INSERT r(d, id) VALUES("2017-07-01", 1);

memsql> SELECT * FROM r;
+----------------------------+----------------------------+------+
| d                          | ts                         | id   |
+----------------------------+----------------------------+------+
| 2017-07-01 00:00:00.000000 | NULL                       |    1 |
+----------------------------+----------------------------+------+

memsql> UPDATE r SET id = 2 WHERE id = 1;

memsql> SELECT * FROM r;
+----------------------------+----------------------------+------+
| d                          | ts                         | id   |
+----------------------------+----------------------------+------+
| 2017-07-01 00:00:00.000000 | NULL                       |    2 |
+----------------------------+----------------------------+------+

String Types

Fixed-Length String

There are three fixed-size string types in MemSQL. The space consumed is directly proportional to their length definitions. Values of type CHAR require 3 x LENGTH bytes of memory, as MemSQL supports only the UTF8 character set. Values of type BINARY require LENGTH bytes of memory.

A column declared as CHAR() stores values as per the defined length. When CHAR values are stored, they are right-padded with spaces to the specified length but when the CHAR values are retrieved, trailing spaces are removed.

CREATE TABLE t1 (c1 CHAR(3));

INSERT INTO t1 VALUES ('  ');
INSERT INTO t1 VALUES ('ab ');
INSERT INTO t1 VALUES ('abcd');

SELECT c1, LENGTH (c1) FROM t1;
****
+------+-------------+
| c1   | LENGTH (c1) |
+------+-------------+
| abc  |           3 |
| ab   |           2 |
|      |           0 |
+------+-------------+

Variable-Length Strings

There are several variable-length string types. The data for these variable types are stored in a memory allocation designed for them, and pointers to their locations in memory are stored in the main table structure. This means that the space consumed by a particular value is the byte length of the string, plus an 8-byte overhead for the pointer, and a 4-byte overhead for storing the length. (There is an additional 4-byte overhead if the field is nullable.) One exception is values less than 7 bytes long. Those small values are stored directly in the pointer area.

The three main types are VARCHAR, VARBINARY, and LONGTEXT. While they all operate similarly for most English-language alphanumeric strings, there are several important differences in their behavior. The length limit of the VARCHAR is measured in Unicode characters, not in bytes. VARBINARY fields store data verbatim, regardless of whether they are valid Unicode strings. Sorting and comparing a VARBINARY uses the exact binary representation.

JSON Type

The JSON type can contain any valid Javascript Object Notation data, including maps, arrays, and nested types. For more information see the JSON Guide .

Geospatial Types

Geospatial types contain information about points, paths, and polygons on the surface of the Earth. For more information, see the Geospatial Guide.

MemSQL Procedural SQL (MPSQL) Types

Two data types are only supported in MemSQL Procedural SQL (MPSQL) scalar-valued functions and stored procedures: ARRAY and RECORD. The QUERY data type is only supported in stored procedures. For more information about these types, see the ARRAY, RECORD, and QUERY topics.

Other Types

Was this Article Helpful?

Thanks For Your Feedback!