This topic describes the column types that are supported for creating different constraints in a MySQL database and a MySQL tenant of OceanBase Database.
Supported types for a PRIMARY KEY column
The following table describes the column types that you can use to create a PRIMARY KEY column by executing the CREATE TABLE DDL statement in a MySQL database and a MySQL tenant of OceanBase Database.
Column type in a MySQL database | Supported | Column type in a MySQL tenant of OceanBase Database | Supported |
INT | Yes | INTEGER | Yes |
TINYINT | Yes | TINYINT | Yes |
SMALLINT | Yes | SMALLINT | Yes |
MEDIUMINT | Yes | MEDIUMINT | Yes |
BIGINT | Yes | BIGINT | Yes |
BOOL | Yes | BOOLEAN | Yes |
DECIMAL | Yes | DECIMAL | Yes |
NUMERIC | Yes | NUMERIC | Yes |
REAL | Yes | FLOAT | Yes |
DOUBLE | Yes | DOUBLE | Yes |
FLOAT | Yes | FLOAT | Yes |
BIT | Yes | BIT | Yes |
CHAR | Yes | CHAR | Yes |
NCHAR | Yes | CHAR | Yes |
VARCHAR | Yes | VARCHAR | Yes |
BINARY | Yes | BINARY | Yes |
VARBINARY | Yes | VARBINARY | Yes |
LONG VARBINARY | Yes (You must specify the column length.) | BLOB | No |
YEAR | Yes | YEAR | Yes |
DATE | Yes | DATE | Yes |
TIME | Yes | TIME | Yes |
TIMESTAMP | Yes | TIMESTAMP | Yes |
DATETIME | Yes | DATETIME | Yes |
TINYBLOB | Yes (You must specify the column length.) | TINYBLOB | No |
MEDIUMBLOB | Yes (You must specify the column length.) | MEDIUMBLOB | No |
BLOB | Yes (You must specify the column length.) | BLOB | No |
LONGBLOB | Yes (You must specify the column length.) | LONGBLOB | No |
TINYTEXT | Yes (You must specify the column length.) | TINYTEXT | No |
MEDIUMTEXT | Yes (You must specify the column length.) | MEDIUMTEXT | No |
TEXT | Yes (You must specify the column length.) | TEXT | No |
LONGTEXT | Yes (You must specify the column length.) | LONGTEXT | No |
ENUM | Yes | ENUM | Yes |
SET | Yes | SET | Yes |
JSON | Yes | JSON | No |
Supported types for a UNIQUE KEY column
The column types that you can use to create a UNIQUE KEY column by executing the CREATE TABLE DDL statement in a MySQL database and a MySQL tenant of OceanBase Database are the same as those that you can use to create a PRIMARY KEY column.
Supported types for a KEY column
The column types that you can use to create a KEY column by executing the CREATE TABLE DDL statement in a MySQL database and a MySQL tenant of OceanBase Database are the same as those that you can use to create a PRIMARY KEY column.
Supported types for a FOREIGN KEY column
The column types that you can use to create a FOREIGN KEY column by executing the CREATE TABLE DDL statement in a MySQL database and a MySQL tenant of OceanBase Database are the same as those that you can use to create a PRIMARY KEY column.
Supported types for a FULLTEXT indexed column
The following table describes the column types that you can use to create a FULLTEXT indexed column by executing the CREATE TABLE DDL statement in a MySQL database and a MySQL tenant of OceanBase Database.
Column type in a MySQL database | Supported | Column type in a MySQL tenant of OceanBase Database | Supported |
INT | No | INTEGER | No |
TINYINT | No | TINYINT | No |
SMALLINT | No | SMALLINT | No |
MEDIUMINT | No | MEDIUMINT | No |
BIGINT | No | BIGINT | No |
BOOL | No | BOOLEAN | No |
DECIMAL | No | DECIMAL | No |
NUMERIC | No | NUMERIC | No |
REAL | No | FLOAT | No |
DOUBLE | No | DOUBLE | No |
FLOAT | No | FLOAT | No |
BIT | No | BIT | No |
CHAR | Yes | CHAR | Yes |
NCHAR | Yes | CHAR | Yes |
VARCHAR | Yes | VARCHAR | Yes |
BINARY | No | BINARY | Yes |
VARBINARY | No | VARBINARY | Yes |
LONG VARBINARY | No | BLOB | No |
YEAR | No | YEAR | No |
DATE | No | DATE | No |
TIME | No | TIME | No |
TIMESTAMP | No | TIMESTAMP | No |
DATETIME | No | DATETIME | No |
TINYBLOB | No | TINYBLOB | No |
MEDIUMBLOB | No | MEDIUMBLOB | No |
BLOB | No | BLOB | No |
LONGBLOB | No | LONGBLOB | No |
TINYTEXT | Yes | TINYTEXT | Yes |
MEDIUMTEXT | Yes | MEDIUMTEXT | Yes |
TEXT | Yes | TEXT | Yes |
LONGTEXT | Yes | LONGTEXT | Yes |
ENUM | No | ENUM | No |
SET | No | SET | No |
JSON | Yes | JSON | No |