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

This topic describes the data types supported by AnalyticDB for MySQL, their differences from MySQL data types, and implicit type conversion rules.

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`: false. `1`: 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 ranges from `1` to `38`. `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 `38`. To adjust the maximum precision, contact Technical Support.
String	`VARCHAR`	A VARCHAR value can be up to 16 MB in size. You do not need to specify the length when you use VARCHAR. Note If you specify the length, such as VARCHAR(255), the number in the parentheses is used only for syntactic compatibility. The actual length varies based on the data size (up to 16 MB) and may exceed 255 characters. Do not perform length validation by using the specified length.	The `VARCHAR` type in AnalyticDB for MySQL corresponds to the `CHAR`, `VARCHAR`, `TEXT`, `MEDIUMTEXT`, or `LONGTEXT` type in MySQL.
String	`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, and automatically converts meaningless numeric values such as `0000-00-00` into `NULL`. Make sure that the date and time correspond to actual values.
	`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 millisecond-level precision, which is up to 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, and automatically converts meaningless numeric values such as `0000-00-00` into `NULL`. Make sure that the date and time correspond to actual values.
	`TIMESTAMP`	Valid values: `'0100-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 millisecond-level precision, which is up to three decimal places.
Spatial	`POINT`	Geographic coordinates x and y.	No difference.

Implicit type conversion

Implicit type conversion refers to the automatic type conversion performed by AnalyticDB for MySQL based on the context and type conversion rules when you execute SQL query operations.

Note

The implicit type conversion rules of AnalyticDB for MySQL cover common basic data types. Complex data types do not support implicit type conversion.
If the default implicit type conversion rules do not meet your business requirements, you can use explicit type conversion.

Projection conversion rules

When columns in SELECT statements are compared with constants, the return types will be implicitly converted into the data types displayed in the following table. If implicit type 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

DECIMAL

DOUBLE

TIME

DATE

TIMESTAMP

DATETIME

BOOLEAN

BOOLEAN

TINYINT

SMALLINT

INTEGER

BIGINT

Not supported

DOUBLE

Not supported

TINYINT

BIGINT

TINYINT

SMALLINT

INTEGER

BIGINT

DECIMAL

DOUBLE

Not supported

SMALLINT

BIGINT

SMALLINT

INTEGER

BIGINT

DECIMAL

DOUBLE

Not supported

INTEGER

BIGINT

INTEGER

BIGINT

DECIMAL

DOUBLE

BIGINT

BIGINT

BIGINT

DECIMAL

DOUBLE

BIGINT

DECIMAL

DECIMAL

Not supported

DECIMAL

DOUBLE

Not supported

DOUBLE

DOUBLE

TIME

TIME

Not supported

BIGINT

Not supported

DOUBLE

TIME

BIGINT

DATETIME

DATE

DATE

Not supported

BIGINT

Not supported

DOUBLE

BIGINT

TIMESTAMP

DATETIME

TIMESTAMP

TIMESTAMP

Not supported

BIGINT

Not supported

DOUBLE

BIGINT

TIMESTAMP

DATETIME

DATETIME

DATETIME

Not supported

BIGINT

Not supported

DOUBLE

DATETIME

Filter conversion rules

When columns in WHERE clauses are compared with constants, the return types will be implicitly converted into the data types displayed in the following table. If implicit type conversion is not supported, an error occurs.

If the data types are different, implicit type conversion may result in pushdown failures, triggering a full table scan that significantly reduces query performance. Pushdown refers to pushing filter conditions to the storage layer for index-based filtering. This reduces data scanning loads when the filter degree is high, thereby improving query performance.

In the following table, 1 before the return data type indicates successful pushdown, and 0 indicates failed pushdown.

Column/Constant

VARCHAR

BOOLEAN

TINYINT

SMALLINT

INTEGER

BIGINT

DECIMAL

REAL

DOUBLE

TIME

DATE

TIMESTAMP

DATETIME

VARCHAR

1 VARCHAR

0 BOOLEAN

0 BIGINT

0 DECIMAL

0 REAL

0 DOUBLE

0 TIME

0 DATE

0 TIMESTAMP

0 DATETIME

BOOLEAN

1 BOOLEAN

0 TINYINT

0 SMALLINT

0 INTEGER

0 BIGINT

Not supported

0 REAL

0 DOUBLE

Not supported

TINYINT

1 TINYINT

0 DECIMAL

0 REAL

0 DOUBLE

Not supported

SMALLINT

1 SMALLINT

0 DECIMAL

0 REAL

0 DOUBLE

Not supported

INTEGER

1 INTEGER

0 DECIMAL

0 REAL

0 DOUBLE

1 INTEGER

INTEGER

BIGINT

1 BIGINT

0 DECIMAL

0 REAL

0 DOUBLE

1 BIGINT

BIGINT

DECIMAL

1 DECIMAL

0 REAL

0 DOUBLE

Not supported

REAL

1 REAL

0 DOUBLE

Not supported

DOUBLE

1 DOUBLE

TIME

1 TIME

Not supported

0 BIGINT

Not supported

0 DOUBLE

1 TIME

0 BIGINT

0 DATETIME

DATE

1 DATE

Not supported

0 BIGINT

Not supported

0 DOUBLE

0 BIGINT

1 DATE

0 TIMESTAMP

0 DATETIME

TIMESTAMP

1 TIMESTAMP

Not supported

0 BIGINT

Not supported

0 DOUBLE

1 TIMESTAMP

DATETIME

1 DATETIME

Not supported

0 BIGINT

Not supported

0 DOUBLE

1 DATETIME

Forced type conversion optimization (ENFORCE_UNWRAP_CAST)

Forced type conversion optimization is used in database systems to control type conversion. It addresses query performance degradation and result accuracy issues caused by implicit type conversion.

Configuration methods

Forced type conversion optimization supports two configuration methods. You can choose one based on your business requirements.

Global configuration
Enable optimization by setting a global parameter, which applies to all queries.
```
SET ADB_CONFIG ENFORCE_UNWRAP_CAST = true;
```
Hint configuration
Temporarily enable optimization for specific queries without affecting other queries.
```
/*+ ENFORCE_UNWRAP_CAST=true */
```

Example

The col column in the test table is of the VARCHAR type and contains the '1', '1a', and 'abc' values. Execute the following statement:

SELECT col FROM test WHERE col = 1;

The result varies based on the configuration status of the enforce_unwrap_cast parameter.

Default disabled status (ENFORCE_UNWRAP_CAST=false): The system implicitly converts the VARCHAR column to the BIGINT type for comparison. In this case, '1' and '1a' are truncated, and the non-numeric part is converted to the numeric value 1 for comparison. Two matching results, '1' and '1a', are returned. Because implicit type conversion is performed on column values, pushdown optimization fails to be triggered, and the system needs to scan the entire table. In this case, query performance decreases.
Enabled status (ENFORCE_UNWRAP_CAST=true): The system converts the INTEGER constant to the VARCHAR type for string comparison. In this case, only '1' matches the constant 1, and '1a' is filtered out due to string mismatch. Only one matching result, '1', is returned. This optimization policy can push down the comparison logic to the storage layer, which effectively reduces the amount of data scanned and improves query performance.