Data Type¶

The data type of MatrixOne is consistent with the definition of MySQL data type, please refer to: https://dev.mysql.com/doc/refman/8.0/en/data-types.html

Integer Type¶

Data Type	Storage Space	Min	Maximum
TINYINT	1 byte	-128	127
SMALLINT	2 byte	-32768	32767
INT	4 byte	-2147483648	2147483647
BIGINT	8 byte	-9223372036854775808	9223372036854775807
TINYINT UNSIGNED	1 byte	0	255
SMALLINT UNSIGNED	2 byte	0	65535
INT UNSIGNED	4 byte	0	4294967295
BIGINT UNSIGNED	8 byte	0	18446744073709551615

Example¶

TINYINT and TINYINT UNSIGNED

-- Create a table named "inttable" with 2 attributes of a "tinyint", a "tinyint unsigned",
create table inttable ( a tinyint not null default 1, tinyint8 tinyint unsigned primary key);
insert into inttable (tinyint8) values ​​(0),(255), (0xFE), (253);

mysql> select * from inttable order by 2 asc;
+------+--------------+
| a | tinyint8 |
+------+--------------+
| 1 | 0 |
| 1 | 253 |
| 1 | 254 |
| 1 | 255 |
+------+--------------+
4 rows in set (0.03 sec)

SMALLINT and SMALLINT UNSIGNED

-- Create a table named "inttable" with 2 attributes of a "smalllint", a "smalllint unsigned",
drop table inttable;
create table inttable ( a smallint not null default 1, smallint16 smallint unsigned);
insert into inttable (smalllint16) values ​​(0),(65535), (0xFFFE), (65534), (65533);

mysql> select * from inttable;
+-------+-----------------+
| a | smallint16 |
+-------+-----------------+
| 1 | 0 |
| 1 | 65535 |
| 1 | 65534 |
| 1 | 65534 |
| 1 | 65533 |
+-------+-----------------+
5 rows in set (0.01 sec)

INT and INT UNSIGNED

-- Create a table named "inttable" with 2 attributes of a "int", a "int unsigned",
drop table inttable;
create table inttable ( a int not null default 1, int32 int unsigned primary key);
insert into inttable (int32) values ​​(0),(4294967295), (0xFFFFFFE), (4294967293), (4294967291);

mysql> select * from inttable order by a desc, 2 asc;
+-------+-----------------+
| a | int32 |
+-------+-----------------+
| 1 | 0 |
| 1 | 4294967291 |
| 1 | 4294967293 |
| 1 | 4294967294 |
| 1 | 4294967295 |
+-------+-----------------+
5 rows in set (0.01 sec)

BIGINT and BIGINT UNSIGNED

-- Create a table named "inttable" with 2 attributes of a "bigint", a "bigint unsigned",
drop table inttable;
create table inttable ( a bigint, big bigint unsigned primary key );
insert into inttable values ​​(122345515, 0xFFFFFFFFFFFE), (1234567, 0xFFFFFFFFF0);

mysql> select * from inttable;
+----------------------------------------+
| a | big |
+----------------------------------------+
| 122345515 | 4503599627370494 |
| 1234567 | 4503599627370480 |
+----------------------------------------+
2 rows in set (0.01 sec)

Floating point type¶

Data Type	Storage Space	Accuracy	Min	Maximum	Syntax Representation
FLOAT32	4 bytes	23 bits	-3.40282e+038	3.40282e+038	FLOAT(M, D) M represents the maximum length, and D represents the number of decimal places displayed. The value range of M is (1=< M <=255). The value range of D is (1=< D <=30), and M >= D. Floating point numbers with precision show the number of bits that require accuracy. When the number of bits is insufficient, the end will be filled with 0.
FLOAT64	8 bytes	53 bits	-1.79769e+308	1.79769e+308	DOUBLE(M, D) M represents the maximum length, and D represents the number of decimal places displayed. The value range of M is (1=< M <=255). The value range of D is (1=< D <=30), and M >= D. Floating point numbers with precision show the number of bits that require accuracy. When the number of bits is insufficient, the end will be filled with 0.

Example¶

-- Create a table named "floatt1" with precision, a trailing zero is added when the number of bits falls short
create table float1(a float(5, 2));
insert into floatt1 values(1), (2.5), (3.56), (4.678);

mysql> select * from floatt1;
+------+
| a |
+------+
| 1.00 |
| 2.50 |
| 3.56 |
| 4.68 |
+------+
4 rows in set (0.00 sec)

-- Create a table named "floattable" with 1 attributes of a "float"
create table floattable ( a float not null default 1, big float(20,5) primary key);
insert into floattable (big) values ​​(-1),(12345678.901234567),(92233720368547.75807);

mysql> select * from floattable order by a desc, big asc;
+---------------------------------------+
| a | big |
+---------------------------------------+
| 1 | -1.00000 |
| 1 | 12345679.00000 |
| 1 | 92233718038528.00000 |
+---------------------------------------+
3 rows in set (0.01 sec)

mysql> select min(big),max(big),max(big)-1 from floattable;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| min(big) | max(big) | max(big) - 1 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| -1.00000 | 92233718038528.00000 | 92233718038527 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.05 sec)

Binary type¶

Data type	Storage space	Minimum value	Maximum value	Syntax	Description
BIT	1bytes	0	18446744073709551615	BIT(M)	Data type for storing bit data, M supports a range of 1 to 64. M defaults to 1. If the stored data is less than M bits, 0 is added to the left to make up the length

Example¶

create table t1 (a bit);
mysql> desc t1;--bit(M) Default M is 1
+--------+------+------+--------+----------+-------------+
| Field | Type | Null | Key | Default | Extra | Comment |
+--------+------+------+--------+----------+-------------+
| a | BIT(1) | YES | | NULL | | | |
+--------+------+------+--------+----------+-------------+
1 row in set (0.01 sec)

create table t2 (a bit(8));

-- Assign values ​​using bit-value literal syntax
insert into t2 values ​​(0b1);
insert into t2 values ​​(b'1');
mysql> select * from t2;
+----------------+
| a |
+----------------+
| 0x01 |
| 0x01 |
+----------------+
2 rows in set (0.00 sec)

truncate table t2;

-- Assign values ​​using hex-value literal syntax
insert into t2 values ​​(0x10);
insert into t2 values ​​(x'10');
mysql> select * from t2;
+----------------+
| a |
+----------------+
| 0x10 |
| 0x10 |
+----------------+
2 rows in set (0.00 sec)

truncate table t2;

--Supports assignment with int type, but the binary representation of int cannot exceed the length of bit type
insert into t2 values ​​(255);--a = b'1111111'
mysql> insert into t2 values ​​(256);--256's binary representation length exceeds 8
ERROR 20301 (HY000): invalid input: data too long, type width = 8, val = 100000000

mysql> select * from t2;
+----------------+
| a |
+----------------+
| 0xFF |
+----------------+
1 row in set (0.00 sec)

truncate table t2;

--Floating point data, the floating point type will be rounded to int type first, and then assigned values ​​according to int type
insert into t2 values ​​(2.1);--a = b'00000010'
mysql> select * from t2;
+----------------+
| a |
+----------------+
| 0x02 |
+----------------+
1 row in set (0.00 sec)

truncate table t2;

-- Character data stores its encoded value, and the total encoding length converted from the entire string cannot exceed the bit type
insert into t2 values ​​('a');--a = b'01100001'
mysql> insert into t2 values ​​('ah');--utf8('ah') = 0xe5958a;
ERROR 20301 (HY000): invalid input: data too long, type width = 8, val = 111001011001010110001010

mysql> select * from t2;
+----------------+
| a |
+----------------+
| 0x61 |
+----------------+
1 row in set (0.00 sec)

String type¶

Data type	Storage space	Length	Syntax	Description
char	24 bytes	0 ~ 4294967295	CHAR	Fixed-length string
varchar	24 bytes	0 ~ 4294967295	VARCHAR	Variable-length string
binary	255 bytes	0 ~ 65535	BINARY(M)	Similar to CHAR, binary string
varbinary	255 bytes	0 ~ 65535	VARBINARY(M)	Similar to VARCHAR, binary string
text	1 GB	other types mapping	TEXT	Long text data, not distinguishing between TINY TEXT, MEDIUM TEXT, and LONG TEXT
blob	1 GB	other types mapping	BLOB	Long binary text data, not distinguishing between TINY BLOB, MEDIUM BLOB, and LONGBLOB
enum	1 byte or 2 bytes	0 ~ 65535	enum	An enumeration type. It is a string object that can only select one value from a list of values such as `value1`, `value2`, or `NULL` or the special ‘’ error value. Enumeration values are represented internally as integers.

Example¶

CHAR and VARCHAR

-- Create a table named "names" with 2 attributes of a "varchar" and a "char"
create table names(name varchar(255),age char(255));
insert into names(name, age) values('Abby', '24');
insert into names(name, age) values("Bob", '25');
insert into names(name, age) values('Carol', "23");
insert into names(name, age) values("Dora", "29");

mysql> select name,age from names;
+--------+------+
| name | age |
+--------+------+
| Abby | 24 |
| Bob | 25 |
| Carol | 23 |
| Dora | 29 |
+--------+------+
4 rows in set (0.00 sec)

BINARY and VARBINARY

-- Create a table named "names" with 2 attributes of a "VARBINARY" and a "BINARY"
create table names(name varbinary(255),age binary(255));
insert into names(name, age) values('Abby', '24');
insert into names(name, age) values("Bob", '25');
insert into names(name, age) values('Carol', "23");
insert into names(name, age) values("Dora", "29");

mysql> select name,age from names;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| name | age |+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 0x41626279   | 0x323400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 |
| 0x426F62     | 0x323500000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 |
| 0x4361726F6C | 0x323300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 |
| 0x446F7261   | 0x323900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.01 sec)

TEXT

-- Create a table named "texttest" with 1 attribute of a "text"
create table texttest (a text);
insert into texttest values('abcdef');
insert into texttest values('_bcdef');
insert into texttest values('a_cdef');
insert into texttest values('ab_def');
insert into texttest values('abc_ef');
insert into texttest values('abcd_f');
insert into texttest values('abcde_');

mysql> select * from texttest where a like 'ab\_def' order by 1 asc;
+--------+
| a      |
+--------+
| ab_def |
+--------+
1 row in set (0.01 sec)

BLOB

-- Create a table named "blobtest" with 1 attribute of a "blob"
create table blobtest (a blob);
insert into blobtest values('abcdef');
insert into blobtest values('_bcdef');
insert into blobtest values('a_cdef');
insert into blobtest values('ab_def');
insert into blobtest values('abc_ef');
insert into blobtest values('abcd_f');
insert into blobtest values('abcde_');

mysql> select * from blobtest where a like 'ab\_def' order by 1 asc;
+----------------+
| a              |
+----------------+
| 0x61625F646566 |
+----------------+
1 row in set (0.01 sec)

ENUM

-- Create a table named "enumtest" with 1 attribute of a "enum"
CREATE TABLE enumtest (color ENUM('red', 'green', 'blue'));
INSERT INTO enumtest (color) VALUES ('red');
mysql> SELECT * FROM enumtest WHERE color = 'green';
+-------+
| color |
+-------+
| green |
+-------+
1 row in set (0.01 sec)

JSON data type¶

JSON data type	Explanation
Objects	Objects are enclosed with `{}` and elements are separated by `,`. The values/keys in JSON objects can be String, Number, Bool, and time.

Example¶

-- Create a table named "jsontest" with 1 attribute of a "json"
create table jsontest (a json,b int);
insert into jsontest values ​​('{"t1":"a"}',1),('{"t1":"b"}',2);

mysql> select * from jsontest;
+-----------------+
| a | b |
+-----------------+
| {"t1": "a"} | 1 |
| {"t1": "b"} | 2 |
+-----------------+
2 rows in set (0.01 sec)

Time and date type¶

Data Type	Storage Space	Accuracy	Min	Maximum	Syntax Representation
Time	8 byte	microsecond	-2562047787:59:59.999999	2562047787:59:59.999999	hh:mm:ss.sssssss
Date	4 byte	day	0001-01-01	9999-12-31	YYYY-MM-DD/YYYYMMDD
DateTime	8 byte	microsecond	0001-01-01 00:00:00.000000	9999-12-31 23:59:59.999999	YYYY-MM-DD hh:mi:sssssss
TIMESTAMP	8 byte	microsecond	0001-01-01 00:00:00.0000000	9999-12-31 23:59:59.9999999	YYYYMMDD hh:mi:ss.ssssssss

The time and date partial types support adding the following hint values when inserting data:

Time: {t ‘xx’}, {time ‘xx’}
Date: {d ‘xx’}, {date ‘xx’}
TIMESTAMP: {ts ‘xx’}, {timestamp ‘xx’}

Example¶

TIME

-- Create a table named "timetest" with 1 attributes of a "time"
create table time_02(t1 time);
insert into time_02 values(200),(time'23:29:30'),({t'12:11:12'}),('');

mysql> select * from time_02;
+-------------+
| t1 |
+-------------+
| 00:02:00 |
| 23:29:30 |
| 12:11:12 |
| NULL |
+-------------+
4 rows in set (0.01 sec)

DATE

-- Create a table named "datetest" with 1 attributes of a "date"
create table datetest (a date not null, primary key(a));
insert into datetest values ​​({d'2022-01-01'}), ('20220102'),(date'2022-01-03'),({d now()});
mysql> select * from datetest;
+----------------+
| a |
+----------------+
| 2022-01-01 |
| 2022-01-02 |
| 2022-01-03 |
| 2024-03-19 |
+----------------+
4 rows in set (0.00 sec)

DATETIME

-- Create a table named "datetimetest" with 1 attributes of a "datetime"
create table datetimetest (a datetime(0) not null, primary key(a));
insert into datetimetest values ​​('20200101000000'), ('2022-01-02'), ('2022-01-02 00:00:01'), ('2022-01-02 00:00:01.512345');

mysql> select * from datetimetest order by a asc;
+--------------------------+
| a |
+--------------------------+
| 2020-01-01 00:00:00 |
| 2022-01-02 00:00:00 |
| 2022-01-02 00:00:01 |
| 2022-01-02 00:00:02 |
+--------------------------+
4 rows in set (0.02 sec)

TIMESTAMP

-- Create a table named "timestamptest" with 1 attribute of a "timestamp"
create table timestamptest (a timestamp(0) not null, primary key(a));
insert into timestamptest values ​​('20200101000000'), (timestamp'2022-01-02 11:30:40'), ({ts'2022-01-02 00:00:01'}), ({ts current_timestamp});

mysql> select * from timestamptest;
+--------------------------+
| a |
+--------------------------+
| 2020-01-01 00:00:00 |
| 2022-01-02 11:30:40 |
| 2022-01-02 00:00:01 |
| 2024-03-19 17:22:08 |
+--------------------------+
4 rows in set (0.00 sec)

Bool Type¶

Data Type	Storage Space
True	1 byte
False	1 byte

Example¶

-- Create a table named "booltest" with 2 attribute of a "boolean" and b "bool"
create table booltest (a boolean,b bool);
insert into booltest values ​​(0,1),(true,false),(true,1),(0,false),(NULL,NULL);

mysql> select * from booltest;
+--------+--------+
| a | b |
+--------+--------+
| false | true |
| true | false |
| true | true |
| false | false |
| NULL | NULL |
+--------+--------+
5 rows in set (0.00 sec)

Fixed-point type Decimal¶

Data Type	Storage Space	Precision	Syntax Representation
Decimal64	8 byte	18 bits	Decimal(N,S) N represents the total number of digits, with the range (1 ~ 18), and the decimal and - (negative) symbols are not included in N. If N is omitted, the default value should be the maximum, that is, the value 18. S represents the number of digits following the decimal point (scale), with the range (0 ~ N) If S is 0, the value has no decimal point or fractional part. If S is omitted, the default is 0, for example, Decimal(10), which is equivalent to Decimal(10, 0) For example, Decimal(10,8), which means that the total length of the number is 10 and the decimal is 8.
Decimal128	16 byte	38 bits	Decimal(N,S) N represents the total number of digits, with the range (18 ~ 38), and the decimal and - (negative) symbols are not included in N. If N is omitted, the default value should be the maximum, that is, the value 38. S represents the number of digits following the decimal point (scale), with the range (0 ~ N) If S is 0, the value has no decimal point or fractional part. If S is omitted, the default is 0, for example, Decimal(20), which is equivalent to Decimal(20, 0). For example, Decimal(20,19), means that the total length of the number is 20 and the decimal digit is 19.

Example¶

-- Create a table named "decimalTest" with 2 attribute of a "decimal" and b "decimal"
create table decimalTest(a decimal(6,3), b decimal(24,18));
insert into decimalTest values(123.4567, 123456.1234567891411241355);

mysql> select * from decimalTest;
+----------------------------------------------------+
| a | b |
+----------------------------------------------------+
| 123.457 | 123456.123456789141124136 |
+----------------------------------------------------+
1 row in set (0.01 sec)

UUID Type¶

|UUID Type | Explanation | |—|–||UUID | UUID value consisting of 32 hexadecimal digits and 4 hyphen ‘-’, in the form 8-4-4-4-12. Standard UUID example: a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11. |

Example¶

-- Create a new table named 't1' and set the 'a' column to the UUID type, and set the 'a' column as the primary key
create table t1(a uuid primary key);

-- Insert a new UUID value into the 'a' column of the 't1' table
insert into t1 values ​​(uuid());

-- Query the length of the 'a' column in the 't1' table after the value of the 'a' column is converted to a string
mysql> select length(cast(a as varchar)) from t1;
+----------------------------------+
| length(cast(a as varchar)) |
+----------------------------------+
| 36 |
+----------------------------------+
1 row in set (0.01 sec)

-- Query all records in the t1 table, whose value is a UUID
mysql> select * from t1;
+---------------------------------------------+
| a |
+---------------------------------------------+
| 948d8e4e-1b00-11ee-b656-5ad2460dea50 |
+---------------------------------------------+
1 row in set (0.00 sec)

Vector data type¶

Example¶

create table t1(n1 vecf32(3), n2 vecf64(2));
insert into t1 values("[1,2,3]",'[4,5]');

mysql> select * from t1;
+---------------------+
| n1 | n2 |
+---------------------+
| [1, 2, 3] | [4, 5] |
+---------------------+
1 row in set (0.00 sec)

Datalink Data Type¶

Type	Explanation
datalink	Special data class used to store links to documents (such as satge) or files

Example¶

drop table test01;
create table test01 (col1 int, col2 datalink);
create stage stage01 url='file:///Users/admin/case/';
insert into test01 values ​​(1, 'file:///Users/admin/case/t1.csv');
insert into test01 values ​​(2, 'file:///Users/admin/case/t1.csv?size=2');
insert into test01 values ​​(3, 'file:///Users/admin/case/t1.csv?offset=4');
insert into test01 values ​​(4, 'file:///Users/admin/case/t1.csv?offset=4&size=2');
insert into test01 values ​​(5, 'stage://stage01/t1.csv');
insert into test01 values ​​(6, 'stage://stage01/t1.csv?size=2');
insert into test01 values ​​(7, 'stage://stage01/t1.csv?offset=4');
insert into test01 values ​​(8, 'stage://stage01/t1.csv?offset=4&size=2');

mysql> select * from test01;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| col1 | col2 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | file:///Users/admin/case/t1.csv |
| 2 | file:///Users/admin/case/t1.csv?size=2 |
| 3 | file:///Users/admin/case/t1.csv?offset=4 |
| 4 | file:///Users/admin/case/t1.csv?offset=4&size=2 |
| 5 | stage://stage01/t1.csv |
| 6 | stage://stage01/t1.csv?size=2 |
| 7 | stage://stage01/t1.csv?offset=4 |
| 8 | stage://stage01/t1.csv?offset=4&size=2 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
8 rows in set (0.01 sec)