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 byte-3276832767
INT4 byte-21474836482147483647
BIGINT8 byte-92233720368547758089223372036854775807
TINYINT UNSIGNED1 byte0255
SMALLINT UNSIGNED2 byte065535
INT UNSIGNED4 byte04294967295
BIGINT UNSIGNED8 byte018446744073709551615

Real Numbers

Data TypeSizePrecisionSyntax
FLOAT324 byte23 bitsFLOAT
FLOAT648 byte53 bitsDOUBLE

String Types

Data TypeSizeSyntax
String24 byteCHAR, VARCHAR

Time and Date Types

Data TypeSizeResolutionMin ValueMax ValuePrecision
Date4 byteday1000-01-019999-12-31YYYY-MM-DD/YYYYMMDD
DateTime8 bytesecond0001-01-01 00:00:00.0000009999-12-31 23:59:59.999999YYYY-MM-DD hh:mi:ssssss
TIMESTAMP8 bytesecond1970-01-01 00:00:01.0000002038-01-19 03:14:07.999999YYYYMMDD hh:mi:ss.ssssss

Bool

Data TypeSize
True1 byte
False1 byte

Decimal Types(Beta)

Data TypeSizePrecisionSyntax
Decimal648 byte19 digitsDecimal(N,S), N range(1,18), S range(0,N)
Decimal12816 byte38 digitsDecimal(N,S), N range(19,38), S range(0,N)

Examples

  1. //Create a table named "numtable" with 3 attributes of an "int", a "float" and a "double"
  2. > create table numtable(id int,fl float, dl double);
  3. //Insert a dataset of int, float and double into table "numtable"
  4. > insert into numtable values(3,1.234567,1.2345678912345678912);
  5. // Create a table named "numtable" with 2 attributes of an "int" and a "float" up to 5 digits in total, of which 3 digits may be after the decimal point.
  6. > create table numtable(id int,fl float(5,3));
  7. //Insert a dataset of int, float into table "numtable"
  8. > insert into numtable values(3,99.123);
  9. //Create a table named "numtable" with 2 attributes of an "int" and a "float" up to 23 digits in total.
  10. > create table numtable(id int,fl float(23));
  11. //Insert a dataset of int, float into table "numtable"
  12. > insert into numtable values(1,1.2345678901234567890123456789);
  13. //Create a table named "numtable" with 4 attributes of an "unsigned tinyint", an "unsigned smallint", an "unsigned int" and an "unsigned bigint"
  14. > create table numtable(a tinyint unsigned, b smallint unsigned, c int unsigned, d bigint unsigned);
  15. //Insert a dataset of unsigned (tinyint, smallint, int and bigint) into table "numtable"
  16. > insert into numtable values(255,65535,4294967295,18446744073709551615);
  17. //Create a table named "names" with 2 attributes of a "varchar" and a "char"
  18. > create table names(name varchar(255),age char(255));
  19. //Insert a data of "varchar" and "char" into table "names"
  20. > insert into names(name, age) values('Abby', '24');
  21. //Create a table named "calendar" with 2 attributes of a "date" and a "datetime"
  22. > create table calendar(a date, b datetime);
  23. //Insert a data of "date" and "datetime" into table "calendar"
  24. > insert into calendar(a, b) values('20220202, '2022-02-02 00:10:30');
  25. > insert into calendar(a, b) values('2022-02-02, '2022-02-02 00:10:30');
  26. //Create a table named "decimalTest" with 2 attribute of a "decimal" and b "decimal"
  27. > create table decimalTest(a decimal(6,3), b decimal(24,18));
  28. > insert into decimalTest values(123.4567, 123456.1234567891411241355);
  29. > select * from decimalTest;
  30. +---------+---------------------------+
  31. | a | b |
  32. +---------+---------------------------+
  33. | 123.456 | 123456.123456789141124135 |
  34. +---------+---------------------------+
  35. //Create a table named "booltest" with 2 attribute of a "boolean" and b "bool"
  36. > create table booltest (a boolean,b bool);
  37. > insert into booltest values (0,1),(true,false),(true,1),(0,false),(NULL,NULL);
  38. > select * from booltest;
  39. +-------+-------+
  40. | a | b |
  41. +-------+-------+
  42. | false | true |
  43. | true | false |
  44. | true | true |
  45. | false | false |
  46. | NULL | NULL |
  47. +-------+-------+
  48. 5 rows in set (0.00 sec)
  49. //Create a table named "timestamptest" with 1 attribute of a "timestamp"
  50. > create table timestamptest (a timestamp(0) not null, primary key(a));
  51. > insert into timestamptest values ('20200101000000'), ('2022-01-02'), ('2022-01-02 00:00:01'), ('2022-01-02 00:00:01.512345');
  52. > select * from timestamptest;
  53. +---------------------+
  54. | a |
  55. +---------------------+
  56. | 2020-01-01 00:00:00 |
  57. | 2022-01-02 00:00:00 |
  58. | 2022-01-02 00:00:01 |
  59. | 2022-01-02 00:00:02 |
  60. +---------------------+