Data Types Overview

MatrixOne Data types conforms with MySQL Data types definition.

Reference: https://dev.mysql.com/doc/refman/8.0/en/data-types.html

Integer Numbers

Data TypeSizeMin ValueMax Value
TINYINT1 byte-128127
SMALLINT2 bytes-3276832767
INT4 bytes-21474836482147483647
BIGINT8 bytes-92233720368547758089223372036854775807
TINYINT UNSIGNED1 byte0255
SMALLINT UNSIGNED2 bytes065535
INT UNSIGNED4 bytes04294967295
BIGINT UNSIGNED8 bytes018446744073709551615

Examples

  • TINYINT and TINYINT UNSIGNED
  1. //Create a table named "inttable" with 2 attributes of a "tinyint", a "tinyint unsigned",
  2. create table inttable ( a tinyint not null default 1, tinyint8 tinyint unsigned primary key);
  3. insert into inttable (tinyint8) values (0),(255), (0xFE), (253);
  4. mysql> select * from inttable order by 2 asc;
  5. +------+----------+
  6. | a | tinyint8 |
  7. +------+----------+
  8. | 1 | 0 |
  9. | 1 | 253 |
  10. | 1 | 254 |
  11. | 1 | 255 |
  12. +------+----------+
  13. 4 rows in set (0.03 sec)
  • SMALLINT and SMALLINT UNSIGNED
  1. ////Create a table named "inttable" with 2 attributes of a "smallint", a "smallint unsigned",
  2. drop table inttable;
  3. create table inttable ( a smallint not null default 1, smallint16 smallint unsigned);
  4. insert into inttable (smallint16) values (0),(65535), (0xFFFE), (65534), (65533);
  5. mysql> select * from inttable;
  6. +------+------------+
  7. | a | smallint16 |
  8. +------+------------+
  9. | 1 | 0 |
  10. | 1 | 65535 |
  11. | 1 | 65534 |
  12. | 1 | 65534 |
  13. | 1 | 65533 |
  14. +------+------------+
  15. 5 rows in set (0.01 sec)
  • INT and INT UNSIGNED
  1. //Create a table named "inttable" with 2 attributes of a "int", a "int unsigned",
  2. drop table inttable;
  3. create table inttable ( a int not null default 1, int32 int unsigned primary key);
  4. insert into inttable (int32) values (0),(4294967295), (0xFFFFFFFE), (4294967293), (4294967291);
  5. mysql> select * from inttable order by a desc, 2 asc;
  6. +------+------------+
  7. | a | int32 |
  8. +------+------------+
  9. | 1 | 0 |
  10. | 1 | 4294967291 |
  11. | 1 | 4294967293 |
  12. | 1 | 4294967294 |
  13. | 1 | 4294967295 |
  14. +------+------------+
  15. 5 rows in set (0.01 sec)
  • BIGINT and BIGINT UNSIGNED
  1. //Create a table named "inttable" with 2 attributes of a "bigint", a "bigint unsigned",
  2. drop table inttable;
  3. create table inttable ( a bigint, big bigint primary key );
  4. insert into inttable values (122345515, 0xFFFFFFFFFFFFE), (1234567, 0xFFFFFFFFFFFF0);
  5. mysql> select * from inttable;
  6. +-----------+------------------+
  7. | a | big |
  8. +-----------+------------------+
  9. | 122345515 | 4503599627370494 |
  10. | 1234567 | 4503599627370480 |
  11. +-----------+------------------+
  12. 2 rows in set (0.01 sec)

Real Numbers

Data TypeSizePrecisionSyntax
FLOAT324 bytes23 bitsFLOAT
FLOAT648 bytes53 bitsDOUBLE

Examples

  1. //Create a table named "floattable" with 1 attributes of a "float"
  2. create table floattable ( a float not null default 1, big float(20,5) primary key);
  3. insert into floattable (big) values (-1),(12345678.901234567),(92233720368547.75807);
  4. mysql> select * from floattable order by a desc, big asc;
  5. +------+----------------+
  6. | a | big |
  7. +------+----------------+
  8. | 1 | -1 |
  9. | 1 | 12345679 |
  10. | 1 | 92233720000000 |
  11. +------+----------------+
  12. 3 rows in set (0.01 sec)
  13. mysql> select min(big),max(big),max(big)-1 from floattable;
  14. +----------+----------------+----------------+
  15. | min(big) | max(big) | max(big) - 1 |
  16. +----------+----------------+----------------+
  17. | -1 | 92233720000000 | 92233718038527 |
  18. +----------+----------------+----------------+
  19. 1 row in set (0.01 sec)

String Types

Data TypeSizeLengthSyntaxDescription
char24 bytes0 ~ 4294967295CHARFixed length string
varchar24 bytes0 ~ 4294967295VARCHARVariable length string
text1 GBother types mappingTEXTLong text data, TINY TEXT, MEDIUM TEXT, and LONG TEXT are not distinguished
blob1 GBother types mappingBLOBLong text data in binary form, TINY BLOB, MEDIUM BLOB, and LONG BLOB are not distinguished

Examples

  • CHAR and VARCHAR
  1. //Create a table named "names" with 2 attributes of a "varchar" and a "char"
  2. create table names(name varchar(255),age char(255));
  3. insert into names(name, age) values('Abby', '24');
  4. insert into names(name, age) values("Bob", '25');
  5. insert into names(name, age) values('Carol', "23");
  6. insert into names(name, age) values("Dora", "29");
  7. mysql> select name,age from names;
  8. +-------+------+
  9. | name | age |
  10. +-------+------+
  11. | Abby | 24 |
  12. | Bob | 25 |
  13. | Carol | 23 |
  14. | Dora | 29 |
  15. +-------+------+
  16. 4 rows in set (0.00 sec)
  • TEXT
  1. //Create a table named "texttest" with 1 attribute of a "text"
  2. create table texttest (a text);
  3. insert into texttest values('abcdef');
  4. insert into texttest values('_bcdef');
  5. insert into texttest values('a_cdef');
  6. insert into texttest values('ab_def');
  7. insert into texttest values('abc_ef');
  8. insert into texttest values('abcd_f');
  9. insert into texttest values('abcde_');
  10. mysql> select * from texttest where a like 'ab\_def' order by 1 asc;
  11. +--------+
  12. | a |
  13. +--------+
  14. | ab_def |
  15. +--------+
  16. 1 row in set (0.01 sec)
  • BLOB
  1. // Create a table named "blobtest" with 1 attribute of a "blob"
  2. create table blobtest (a blob);
  3. insert into blobtest values('abcdef');
  4. insert into blobtest values('_bcdef');
  5. insert into blobtest values('a_cdef');
  6. insert into blobtest values('ab_def');
  7. insert into blobtest values('abc_ef');
  8. insert into blobtest values('abcd_f');
  9. insert into blobtest values('abcde_');
  10. mysql> select * from blobtest where a like 'ab\_def' order by 1 asc;
  11. +----------------+
  12. | a |
  13. +----------------+
  14. | 0x61625F646566 |
  15. +----------------+
  16. 1 row in set (0.01 sec)

JSON Types

JSON Data TypeSyntax
ObjectObject is enclosed by {}, separated by commas between key-value pairs, and separated by colons : between keys and values.
The value/key can be String, Number, Bool, Time and date.
ArrayArray is enclosed by [], separated by commas between key-value pairs, and separated by colons : between keys and values.
The value can be String, Number, Bool, Time and date.

Examples

  1. //Create a table named "jsontest" with 1 attribute of a "json"
  2. create table jsontest (a json,b int);
  3. insert into jsontest values ('{"t1":"a"}',1),('{"t1":"b"}',2);
  4. mysql> select * from jsontest;
  5. +-------------+------+
  6. | a | b |
  7. +-------------+------+
  8. | {"t1": "a"} | 1 |
  9. | {"t1": "b"} | 2 |
  10. +-------------+------+
  11. 2 rows in set (0.01 sec)

Time and Date Types

Data TypeSizeResolutionMin ValueMax ValuePrecision
Time8 bytesmicrosecond-2562047787:59:59.9999992562047787:59:59.999999hh:mm:ss.ssssss
Date4 bytesday0001-01-019999-12-31YYYY-MM-DD/YYYYMMDD
DateTime8 bytesmicrosecond0001-01-01 00:00:00.0000009999-12-31 23:59:59.999999YYYY-MM-DD hh:mi:ssssss
TIMESTAMP8 bytesmicrosecond0001-01-01 00:00:00.0000009999-12-31 23:59:59.999999YYYYMMDD hh:mi:ss.ssssss

Examples

  • TIME
  1. //Create a table named "timetest" with 1 attributes of a "time"
  2. create table time_02(t1 time);
  3. insert into time_02 values(200);
  4. insert into time_02 values("");
  5. mysql> select * from time_02;
  6. +----------+
  7. | t1 |
  8. +----------+
  9. | 00:02:00 |
  10. | NULL |
  11. +----------+
  12. 2 rows in set (0.00 sec)
  • DATE
  1. //Create a table named "datetest" with 1 attributes of a "date"
  2. create table datetest (a date not null, primary key(a));
  3. insert into datetest values ('2022-01-01'), ('20220102'),('2022-01-03'),('20220104');
  4. mysql> select * from datetest order by a asc;
  5. +------------+
  6. | a |
  7. +------------+
  8. | 2022-01-01 |
  9. | 2022-01-02 |
  10. | 2022-01-03 |
  11. | 2022-01-04 |
  12. +------------+
  • DATETIME
  1. //Create a table named "datetimetest" with 1 attributes of a "datetime"
  2. create table datetimetest (a datetime(0) not null, primary key(a));
  3. insert into datetimetest values ('20200101000000'), ('2022-01-02'), ('2022-01-02 00:00:01'), ('2022-01-02 00:00:01.512345');
  4. mysql> select * from datetimetest order by a asc;
  5. +---------------------+
  6. | a |
  7. +---------------------+
  8. | 2020-01-01 00:00:00 |
  9. | 2022-01-02 00:00:00 |
  10. | 2022-01-02 00:00:01 |
  11. | 2022-01-02 00:00:02 |
  12. +---------------------+
  13. 4 rows in set (0.02 sec)
  • TIMESTAMP
  1. //Create a table named "timestamptest" with 1 attribute of a "timestamp"
  2. create table timestamptest (a timestamp(0) not null, primary key(a));
  3. insert into timestamptest values ('20200101000000'), ('2022-01-02'), ('2022-01-02 00:00:01'), ('2022-01-02 00:00:01.512345');
  4. mysql> select * from timestamptest;
  5. +---------------------+
  6. | a |
  7. +---------------------+
  8. | 2020-01-01 00:00:00 |
  9. | 2022-01-02 00:00:00 |
  10. | 2022-01-02 00:00:01 |
  11. | 2022-01-02 00:00:02 |
  12. +---------------------+

Bool

Data TypeSize
True1 byte
False1 byte

Examples

  1. //Create a table named "booltest" with 2 attribute of a "boolean" and b "bool"
  2. create table booltest (a boolean,b bool);
  3. insert into booltest values (0,1),(true,false),(true,1),(0,false),(NULL,NULL);
  4. mysql> select * from booltest;
  5. +-------+-------+
  6. | a | b |
  7. +-------+-------+
  8. | false | true |
  9. | true | false |
  10. | true | true |
  11. | false | false |
  12. | NULL | NULL |
  13. +-------+-------+
  14. 5 rows in set (0.00 sec)

Decimal Types(Beta)

Data TypeSizePrecisionSyntax
Decimal8 bytes19 digitsDecimal(N,S)
N is the total number of digits, the range is(1 ~ 18). The decimal point and (for negative numbers) the - sign are not counted in N.
S is the number of digits after the decimal point (the scale), the range is(0 ~ N)
If S is 0, values have no decimal point or fractional part. If S is omitted, the default is 0. If N is omitted, the default is 1.
For example, Decimal(10,8) represents a number with a total length of 10 and a decimal place of 8.
Decimal16 bytes38 digitsDecimal(N,S)
N is the total number of digits, the range is(18 ~ 38). The decimal point and (for negative numbers) the - sign are not counted in N.
S is the number of digits after the decimal point (the scale), the range is(0 ~ N)
If S is 0, values have no decimal point or fractional part. If S is omitted, the default is 0. If N is omitted, the default is 18.
For example, Decimal(20,9) represents a number with a total length of 20 and a decimal place of 9.

Examples

  1. //Create a table named "decimalTest" with 2 attribute of a "decimal" and b "decimal"
  2. create table decimalTest(a decimal(6,3), b decimal(24,18));
  3. insert into decimalTest values(123.4567, 123456.1234567891411241355);
  4. mysql> select * from decimalTest;
  5. +---------+---------------------------+
  6. | a | b |
  7. +---------+---------------------------+
  8. | 123.457 | 123456.123456789141124136 |
  9. +---------+---------------------------+
  10. 1 row in set (0.01 sec)