This topic describes the data types supported by AnalyticDB for MySQL and their differences from MySQL data types. For more information about the data types that AnalyticDB for MySQL does not support, see Unsupported data types and operators.

Data types supported by AnalyticDB for MySQL

Type Data type Description Comparison with MySQL data types
Numeric type BOOLEAN Valid values: 0 and 1. A BOOLEAN value is 1 bit in size.
  • 0: indicates false.
  • 1: indicates true.
No difference.
TINYINT Valid values: -128 to 127. A TINYINT value is 1 byte in size. No difference.
SMALLINT Valid values: -32768 to 32767. A SMALLINT value is 2 bytes in size. No difference.
INT or INTEGER Valid values: -2147483648 to 2147483647. An INT value is 4 bytes in size. The INT type in AnalyticDB for MySQL corresponds to the INT or MEDIUMINT type in MySQL.
BIGINT Valid values: -9223372036854775808 to 9223372036854775807. A BIGINT value is 8 bytes in size. No difference.
FLOAT Valid values: -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. The FLOAT type follows the IEEE standard. A FLOAT value is 4 bytes in size. No difference.
DOUBLE Valid values: -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, 2.2250738585072014E-308 to 1.7976931348623157E+308. The DOUBLE type follows the IEEE standard. A DOUBLE value is 8 bytes in size. No difference.
DECIMAL(M,D) or NUMERIC M is the maximum precision, and its value range is 1 to 1000. D is the decimal scale. The value of D must be less than or equal to that of M.
  • MySQL supports a maximum precision of 65.
  • AnalyticDB for MySQL supports a maximum precision of 1000.
String type VARCHAR A VARCHAR value can be up to 16 MB in size. You do not need to specify the size when you use VARCHAR. The VARCHAR type in AnalyticDB for MySQL corresponds to the CHAR, VARCHAR, TEXT, MEDIUMTEXT, or LONGTEXT type in MySQL.
BINARY The length of storage characters. The BINARY type in AnalyticDB for MySQL corresponds to the BINARY, VARBINARY, or BLOB type in MySQL.
Time type DATE Valid values: '0001-01-01' to '9999-12-31'. A DATE value is in the 'YYYY-MM-DD' format and is 4 bytes in size.
  • MySQL supports 0000-00-00.
  • AnalyticDB for MySQL checks the validity of values of time types. If you enable the ILLEGAL_DATE_CONVERT_TO_NULL_ENABLE=true parameter, AnalyticDB for MySQL automatically converts unreasonable numeric values such as 0000-00-00 into NULL. Make sure that the date and time written are meaningful.
TIME Valid values: '00:00:00' to '23:59:59'. A TIME value is in the 'HH:MM:SS' format and is 8 bytes in size.
  • MySQL supports custom precision.
  • AnalyticDB for MySQL supports the precision of milliseconds, which is a maximum of three decimal places.
DATETIME Valid values: '0001-01-01 00:00:00.000' to '9999-12-31 23:59:59.999'. A DATETIME value is in the 'YYYY-MM-DD HH:MM:SS' format. It is 8 bytes in size and in UTC.
Note By default, DATETIME uses UTC time. You cannot change the time zone for DATETIME values.
  • MySQL supports 0000-00-00 and custom precision.
  • AnalyticDB for MySQL checks the validity of values of time types. If you enable the ILLEGAL_DATE_CONVERT_TO_NULL_ENABLE=true parameter, AnalyticDB for MySQL automatically converts unreasonable numeric values such as 0000-00-00 into NULL. Make sure that the date and time written are meaningful.
TIMESTAMP Valid values: '0001-01-01 00:00:00.000' to '9999-12-31 23:59:59.999'. A TIMESTAMP value is in the 'YYYY-MM-DD HH:MM:SS' format. It is 4 bytes in size and in UTC.
Note By default, TIMESTAMP uses the time zone of the database system. You can specify the time zone for each session.
  • MySQL supports custom precision.
  • AnalyticDB for MySQL supports the precision of milliseconds, which is a maximum of three decimal places.
Spatial data type point The geographic coordinates. No difference.
json For more information, see JSON. No difference.