This topic describes the data types supported by AnalyticDB for MySQL and their differences from MySQL data types.

Basic data types supported by AnalyticDB for MySQL

Category Data type Description Comparison with MySQL data types
Numeric 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 or INTEGER 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, and 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 indicates the maximum precision, and its value is in the range of 1 to 1000. D indicates 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 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 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 set the ILLEGAL_DATE_CONVERT_TO_NULL_ENABLE parameter to true, 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.
Important 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 set the ILLEGAL_DATE_CONVERT_TO_NULL_ENABLE parameter to true, 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 8 bytes in size and in UTC.
Note By default, TIMESTAMP uses the time zone of the 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 point Geographic coordinates x and y. No difference.

Complex data types supported by AnalyticDB for MySQL

Data types not supported by AnalyticDB for MySQL

For more information about the data types not supported by AnalyticDB for MySQL, see Unsupported data types and operators.