AnalyticDB for MySQL data types - AnalyticDB - Alibaba Cloud Documentation Center

AnalyticDB for MySQL supports a core set of SQL data types across numeric, string, time, and spatial categories. This page describes each type's storage size, value range, and differences from standard MySQL, followed by implicit type conversion rules.

Supported data types

Numeric types

Type	Aliases	Value range	Size	Notes
`BOOLEAN`	—	`0` (false), `1` (true)	1 bit	No difference from MySQL.
`TINYINT`	—	`-128` to `127`	1 byte	No difference from MySQL.
`SMALLINT`	—	`-32768` to `32767`	2 bytes	No difference from MySQL.
`INT` or `INTEGER`	—	`-2147483648` to `2147483647`	4 bytes	Corresponds to MySQL `INT` or `MEDIUMINT`.
`BIGINT`	—	`-9223372036854775808` to `9223372036854775807`	8 bytes	No difference from MySQL.
`FLOAT`	—	`-3.402823466E+38` to `-1.175494351E-38`, `0`, `1.175494351E-38` to `3.402823466E+38`	4 bytes	Follows the IEEE standard. No difference from MySQL.
`DOUBLE`	—	`-1.7976931348623157E+308` to `-2.2250738585072014E-308`, `0`, `2.2250738585072014E-308` to `1.7976931348623157E+308`	8 bytes	Follows the IEEE standard. No difference from MySQL.
`DECIMAL(m,d)` or `NUMERIC`	—	`m`: max precision `1`–`38`; `d`: decimal scale, must be ≤ `m`	—	MySQL supports max precision `65`; AnalyticDB for MySQL supports up to `38`. If the DECIMAL precision exceeds `38`, the `CREATE TABLE` statement fails and returns error code `5030` with the message `decimal precision must be less than or equal to 38`. In earlier versions, the default maximum precision was `1000`. It is reduced to `38` in the current version. If your workload requires a precision greater than `38`, use one of the following methods: Reduce the precision: set the DECIMAL precision to `38` or less. Raise the limit on the backend: contact Alibaba Cloud Technical Support to set the `DECIMAL_MAX_PRECISION` parameter to `1000`, and then restart the cluster for the change to take effect. This is a backend parameter that cannot be modified in the console cluster settings or by calling an API operation.

String types

Type	Description	MySQL equivalent
`VARCHAR`	Up to 16 MB. A length specifier such as `VARCHAR(255)` is accepted for syntactic compatibility — the actual maximum is determined by data size (up to 16 MB), not the declared number. Do not rely on the declared length for validation.	`CHAR`, `VARCHAR`, `TEXT`, `MEDIUMTEXT`, or `LONGTEXT`
`BINARY`	The length of storage characters.	`BINARY`, `VARBINARY`, or `BLOB`

Time types

Type	Value range	Format	Size	Notes
`DATE`	`'0001-01-01'` to `'9999-12-31'`	`YYYY-MM-DD`	4 bytes	MySQL supports `0000-00-00`; AnalyticDB for MySQL validates time values and converts `0000-00-00` to `NULL`. Use actual dates.
`TIME`	`'00:00:00'` to `'23:59:59'`	`HH:MM:SS`	8 bytes	MySQL supports custom precision; AnalyticDB for MySQL supports millisecond-level precision (up to three decimal places).
`DATETIME`	`'0001-01-01 00:00:00.000'` to `'9999-12-31 23:59:59.999'`	`YYYY-MM-DD HH:MM:SS`	8 bytes	Stored in UTC. The time zone cannot be changed. MySQL supports `0000-00-00` and custom precision; AnalyticDB for MySQL converts `0000-00-00` to `NULL`.
`TIMESTAMP`	`'0100-01-01 00:00:00.000'` to `'9999-12-31 23:59:59.999'`	`YYYY-MM-DD HH:MM:SS`	8 bytes	Uses the system time zone by default; you can specify a different time zone per session. MySQL supports custom precision; AnalyticDB for MySQL supports millisecond-level precision (up to three decimal places).

Important

DATETIME always uses UTC. Unlike TIMESTAMP, the time zone for DATETIME values cannot be overridden per session.

Spatial types

Type	Description	Notes
`POINT`	Geographic coordinates x and y.	No difference from MySQL.

Implicit type conversion

AnalyticDB for MySQL performs implicit type conversion automatically based on context when you run SQL queries. The conversion rules differ between SELECT projections and WHERE filters.

Note

Implicit type conversion applies to basic data types only. Complex data types do not support implicit type conversion. For cases where the default rules do not meet your needs, use explicit type conversion.

Projection conversion rules

When a column in a SELECT statement is compared with a constant, the result is implicitly converted to the type shown in the following table. If conversion is not supported, an error occurs.

Column \ Constant	VARCHAR	BOOLEAN	TINYINT	SMALLINT	INTEGER	BIGINT	DECIMAL	DOUBLE	TIME	DATE	TIMESTAMP	DATETIME
VARCHAR	VARCHAR	BOOLEAN	BIGINT	BIGINT	BIGINT	BIGINT	DECIMAL	DOUBLE	TIME	DATE	TIMESTAMP	DATETIME
BOOLEAN	BOOLEAN	BOOLEAN	TINYINT	SMALLINT	INTEGER	BIGINT	Not supported	DOUBLE	Not supported	Not supported	Not supported	Not supported
TINYINT	BIGINT	TINYINT	TINYINT	SMALLINT	INTEGER	BIGINT	DECIMAL	DOUBLE	Not supported	Not supported	Not supported	Not supported
SMALLINT	BIGINT	SMALLINT	SMALLINT	SMALLINT	INTEGER	BIGINT	DECIMAL	DOUBLE	Not supported	Not supported	Not supported	Not supported
INTEGER	BIGINT	INTEGER	INTEGER	INTEGER	INTEGER	BIGINT	DECIMAL	DOUBLE	BIGINT	BIGINT	BIGINT	BIGINT
BIGINT	BIGINT	BIGINT	BIGINT	BIGINT	BIGINT	BIGINT	DECIMAL	DOUBLE	BIGINT	BIGINT	BIGINT	BIGINT
DECIMAL	DECIMAL	Not supported	DECIMAL	DECIMAL	DECIMAL	DECIMAL	DECIMAL	DOUBLE	Not supported	Not supported	Not supported	Not supported
DOUBLE	DOUBLE	DOUBLE	DOUBLE	DOUBLE	DOUBLE	DOUBLE	DOUBLE	DOUBLE	DOUBLE	DOUBLE	DOUBLE	DOUBLE
TIME	TIME	Not supported	Not supported	Not supported	BIGINT	BIGINT	Not supported	DOUBLE	TIME	BIGINT	BIGINT	DATETIME
DATE	DATE	Not supported	Not supported	Not supported	BIGINT	BIGINT	Not supported	DOUBLE	BIGINT	TIMESTAMP	TIMESTAMP	DATETIME
TIMESTAMP	TIMESTAMP	Not supported	Not supported	Not supported	BIGINT	BIGINT	Not supported	DOUBLE	BIGINT	TIMESTAMP	TIMESTAMP	DATETIME
DATETIME	DATETIME	Not supported	Not supported	Not supported	BIGINT	BIGINT	Not supported	DOUBLE	DATETIME	DATETIME	DATETIME	DATETIME

Filter conversion rules

When a column in a WHERE clause is compared with a constant of a different type, the result type is implicitly converted. Mismatched types can prevent pushdown — the optimization that pushes filter conditions to the storage layer for index-based filtering. When pushdown fails, the database falls back to a full table scan, which significantly reduces query performance.

The table below shows the result type and pushdown status for each column–constant type pair. ✓ = pushdown succeeds; ✗ = pushdown fails (full table scan).

Column \ Constant	VARCHAR	BOOLEAN	TINYINT	SMALLINT	INTEGER	BIGINT	DECIMAL	REAL	DOUBLE	TIME	DATE	TIMESTAMP	DATETIME
VARCHAR	VARCHAR ✓	BOOLEAN ✗	BIGINT ✗	BIGINT ✗	BIGINT ✗	BIGINT ✗	DECIMAL ✗	REAL ✗	DOUBLE ✗	TIME ✗	DATE ✗	TIMESTAMP ✗	DATETIME ✗
BOOLEAN	BOOLEAN ✓	BOOLEAN ✓	TINYINT ✗	SMALLINT ✗	INTEGER ✗	BIGINT ✗	Not supported	REAL ✗	DOUBLE ✗	Not supported	Not supported	Not supported	Not supported
TINYINT	TINYINT ✓	TINYINT ✓	TINYINT ✓	TINYINT ✓	TINYINT ✓	TINYINT ✓	DECIMAL ✗	REAL ✗	DOUBLE ✗	Not supported	Not supported	Not supported	Not supported
SMALLINT	SMALLINT ✓	SMALLINT ✓	SMALLINT ✓	SMALLINT ✓	SMALLINT ✓	SMALLINT ✓	DECIMAL ✗	REAL ✗	DOUBLE ✗	Not supported	Not supported	Not supported	Not supported
INTEGER	INTEGER ✓	INTEGER ✓	INTEGER ✓	INTEGER ✓	INTEGER ✓	INTEGER ✓	DECIMAL ✗	REAL ✗	DOUBLE ✗	INTEGER ✓	INTEGER ✓	INTEGER	INTEGER
BIGINT	BIGINT ✓	BIGINT ✓	BIGINT ✓	BIGINT ✓	BIGINT ✓	BIGINT ✓	DECIMAL ✗	REAL ✗	DOUBLE ✗	BIGINT ✓	BIGINT ✓	BIGINT	BIGINT
DECIMAL	DECIMAL ✓	DECIMAL ✓	DECIMAL ✓	DECIMAL ✓	DECIMAL ✓	DECIMAL ✓	DECIMAL ✓	REAL ✗	DOUBLE ✗	Not supported	Not supported	Not supported	Not supported
REAL	REAL ✓	REAL ✓	REAL ✓	REAL ✓	REAL ✓	REAL ✓	REAL ✓	REAL ✓	DOUBLE ✗	Not supported	Not supported	Not supported	Not supported
DOUBLE	DOUBLE ✓	DOUBLE ✓	DOUBLE ✓	DOUBLE ✓	DOUBLE ✓	DOUBLE ✓	DOUBLE ✓	DOUBLE ✓	DOUBLE ✓	DOUBLE ✓	DOUBLE ✓	DOUBLE ✓	DOUBLE ✓
TIME	TIME ✓	Not supported	Not supported	Not supported	BIGINT ✗	BIGINT ✗	Not supported	Not supported	DOUBLE ✗	TIME ✓	BIGINT ✗	BIGINT ✗	DATETIME ✗
DATE	DATE ✓	Not supported	Not supported	Not supported	BIGINT ✗	BIGINT ✗	Not supported	Not supported	DOUBLE ✗	BIGINT ✗	DATE ✓	TIMESTAMP ✗	DATETIME ✗
TIMESTAMP	TIMESTAMP ✓	Not supported	Not supported	Not supported	BIGINT ✗	BIGINT ✗	Not supported	Not supported	DOUBLE ✗	TIMESTAMP ✓	TIMESTAMP ✓	TIMESTAMP ✓	TIMESTAMP ✓
DATETIME	DATETIME ✓	Not supported	Not supported	Not supported	BIGINT ✗	BIGINT ✗	Not supported	Not supported	DOUBLE ✗	DATETIME ✓	DATETIME ✓	DATETIME ✓	DATETIME ✓

Forced type conversion optimization (ENFORCE_UNWRAP_CAST)

ENFORCE_UNWRAP_CAST controls how AnalyticDB for MySQL handles implicit type conversion in filter conditions. When enabled, the system converts the constant to match the column type instead of the other way around — preserving pushdown optimization and preventing unexpected results from value truncation.

Configure ENFORCE_UNWRAP_CAST

Two configuration methods are available:

Global — applies to all queries in the cluster:
```
SET ADB_CONFIG ENFORCE_UNWRAP_CAST = true;
```
Query hint — applies to a single query without affecting others:
```
/*+ ENFORCE_UNWRAP_CAST=true */
```

Example

The col column in the test table is of type VARCHAR and contains the values '1', '1a', and 'abc'. Running the following query produces different results depending on whether ENFORCE_UNWRAP_CAST is enabled:

SELECT col FROM test WHERE col = 1;

Setting	Behavior	Matching rows	Pushdown
`ENFORCE_UNWRAP_CAST=false` (default)	Converts the VARCHAR column to BIGINT. `'1'` and `'1a'` are both truncated to `1` and match.	`'1'`, `'1a'`	Fails — full table scan
`ENFORCE_UNWRAP_CAST=true`	Converts the INTEGER constant `1` to VARCHAR `'1'`. Only an exact string match qualifies.	`'1'` only	Succeeds — storage-layer pushdown

Enable ENFORCE_UNWRAP_CAST when columns are declared as string types but queried with numeric constants, to get accurate results and avoid full table scans.