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. To adjust the maximum precision, contact Technical Support. |
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). |
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.
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.