When changing a column type, you must first consider the table-level rules for column type changes, particularly the scenarios where certain data types are prohibited. Next, consider the conversion rules between the source and target data types during the change.
Rules for changing column types at the table level
Disabled rules
Foreign key constraints
For both online and offline DDL, the type of a foreign key column cannot be modified in any scenario except for the following special case (note that the type modification only changes the display precision, not the storage structure).
If the column's data type is
FLOAT(m,n), you can change the precision, but conversion betweenSIGNEDandUNSIGNEDis not supported.If the data type of a column is
DOUBLE(m,n), you can change its precision, but you cannot convert it betweenSIGNEDandUNSIGNED.If the column's data type is
VARCHAR(m), increasing the precision is supported, decreasing the precision is not supported, and changing it to theCHARtype is not supported.
CHECKconstraintFor both online and offline DDL operations, columns with a
CHECKconstraint cannot have their data type modified, except for those of the integer type (INTEGER).Trigger constraints
This restriction applies only to offline DDL operations. If a column has triggers other than
DISABLE, the column type cannot be modified. If all triggers areDISABLE, the DDL operation is allowed, but the new table must include theseDISABLEtriggers.
Change rules
When the length of a data type increases for the same data type, for example, changing the data type of a column from
VARCHAR(8)toVARCHAR(11), this operation can be performed as an online DDL without rewriting the data.- If you modify the length of a column using online DDL and the column is part of an index table (for example, changing the length of a
VARCHARtype column), you must also modify the schemas of related dependent objects, such as the index table, during the online DDL operation. In other words, "the data expected by users remains unchanged, but the schemas in dependent objects must be updated accordingly". - For columns of the
INTEGERorDecimalInttype, if they are index columns or primary key columns, operations that increase the column type can be performed using online DDL.
- If you modify the length of a column using online DDL and the column is part of an index table (for example, changing the length of a
In OceanBase AI Database, for the growth of
LOBdata, Online DDL operations support the following scenarios:- Modification of regular columns.
- Modifications as generated columns (Store/Virtual)
- Modifications that depend on generated columns (Store/Virtual)
- Added
NOT NULLandCHECKconstraints. - Modified the original
NOT NULLconstraint.
For numeric data types, if a
SIGNEDtoUNSIGNEDconversion occurs and type change is allowed, an offline operation is required. For character data types, if a change in the character set or collation occurs and column type change is allowed, an offline operation is required.When a column is referenced by a generated column, changes to its data type are allowed.
Column-level conversion rules
In OceanBase AI Database, all column data types are divided into three main categories: numeric data types, character data types, and time data types. The conversion rules within these three categories are similar, as are the change rules between each category, although there are some special cases.
The following tables list the conversions within and between each major category.
Conversions within numeric data types
Numeric data types include:
Integer:
TINYINT,SMALLINT,MEDIUMINT,INT,BIGINTFloating-point:
FLOAT,DOUBLEFixed-point:
NUMBERBinary:
BIT
The following table describes the conversions within the numeric data types.
Data type |
To TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT |
To FLOAT, DOUBLE |
To NUMBER |
To BIT |
|---|---|---|---|---|
| Integer | Supported | Supported | Supported | Supported |
| float | Supported | Supported | Supported | Supported |
| Fixed-point | Supported | Supported | Supported | Supported |
| Binary | Supported | Supported | Supported | Supported |
Conversions within character data types
Character data types include:
CHAR,VARCHARBINARY,VARBINARYTEXTlarge types:TINYTEXT,MEDIUMTEXT,TEXT, andLONGTEXTBLOBdata types:TINYBLOB,MEDIUMBLOB,BLOB, andLONGBLOBENUM,SET
The following table describes the conversion rules within the character data type category.
Data type |
To CHAR |
To BINARY |
To VARCHAR\ VARBINARY |
To TEXT\ BLOB |
To ENUM\ SET |
|---|---|---|---|---|---|
CHAR |
Supported | Supported | Supported | Supported | Supported |
BINARY |
Supported | Supported | Supported | Supported | Supported |
VARCHAR, VARBINARY |
Supported | Supported | Supported | Supported | Supported |
TEXT Large types, BLOB Large Types |
Supported | Supported | Supported | Supported | Supported |
ENUM, SET |
Supported | Supported | Supported | Supported | Supported |
Conversions within time types
Time data types include:
DATETIMESTAMPDATETIMETIMEYEAR
The following table describes the conversions within the time data type category.
Data type |
To DATE |
To TIMESTAMP |
DATETIME |
TIME |
YEAR |
|---|---|---|---|---|---|
DATE |
Supported | Supported | Supported | Supported | Supported |
TIMESTAMP |
Supported | Supported | Supported | Supported | Supported |
DATETIME |
Supported | Supported | Supported | Supported | Supported |
TIME |
Supported | Supported | Supported | Supported | Supported |
YEAR |
Supported | Supported | Supported | Supported | Supported |
Conversion between numeric and character data types
The conversion between numeric and character data types is shown in the following table.
Data type |
To Integer, Floating-point, Fixed-point |
To BIT type |
To CHAR, BINARY, VARCHAR, VARBINARY, TEXT, and BLOB data types |
To ENUM, SET |
|---|---|---|---|---|
| Integer, floating-point, and fixed-point | See the conversion rules within each category. | See the conversion rules within each category. | Supported | Supported |
BIT Type |
See the conversion rules within each category. | See the conversion rules within each category. | Supported | Supported |
CHAR, BINARY, VARCHAR, VARBINARY, TEXT Category, BLOB Category |
Supported | Supported | See the conversion rules within each category. | Supported |
ENUM, SET |
Supported | Supported | See the conversion rules within each category. | See the conversion rules within each category. |
Conversion between numeric and temporal data types
The following table describes the conversion between numeric and temporal data types.
Data type |
To Integer, Floating-point, Fixed-point, BIT |
To DATE |
To DATETIME |
To TIMESTAMP |
To TIME |
To YEAR |
|---|---|---|---|---|---|---|
Integer, floating-point, fixed-point, BIT |
See the conversion rules within each category. | Supported | Supported | Supported | Supported | Supported |
DATE |
Supported | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. |
DATETIME |
Supported | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. |
TIMESTAMP |
Supported | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. |
TIME |
Supported | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. |
YEAR |
Supported | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. |
Conversion between character and time data types
The conversion between character types and time types is shown in the following table.
Data type |
To CHAR, BINARY, VARCHAR, VARBINARY, TEXT, and BLOB data types |
To ENUM, SET |
To DATE |
To DATETIME |
To TIMESTAMP |
To TIME |
To YEAR |
|---|---|---|---|---|---|---|---|
CHAR, BINARY, VARCHAR, VARBINARY, TEXT Category, BLOB Category |
See the conversion rules within each category. | See the conversion rules within each category. | Supported | Supported | Supported | Supported | Supported |
ENUM, SET |
See the conversion rules within each category. | See the conversion rules within each category. | Supported | Supported | Supported | Supported | Supported |
DATE |
Supported | Supported | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. |
DATETIME |
Supported | Supported | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. |
TIMESTAMP |
Supported | Supported | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. |
TIME |
Supported | Supported | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. |
YEAR |
Supported | Supported | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. | See the conversion rules within each category. |
Online DDL column type upcasting scenarios
Integer conversion
Data type |
To SMALLINT |
To MEDIUMINT |
To INT |
To BIGINT |
|---|---|---|---|---|
| TINYINT | Supported | Supported | Supported | Supported |
| SMALLINT | N/A | Supported | Supported | Supported |
| MEDIUMINT | Not supported | N/A | Supported | Supported |
| INT | Not supported | Not supported | N/A | Supported |
Data type |
To USMALLINT |
To UMEDIUMINT |
To UINT |
To UBIGINT |
|---|---|---|---|---|
| UTINYINT | Supported | Supported | Supported | Supported |
| USMALLINT | N/A | Supported | Supported | Supported |
| UMEDIUMINT | Not supported | N/A | Supported | Supported |
| UINT | Not supported | Not supported | N/A | Supported |
DECIMAL/DECIMALINT
DECIMAL(precision, scale) indicates a decimal number type, where precision represents the total length of the number, and scale represents the number of digits in the fractional part. The precision range for precision during online DDL operations is [1, 9], [10, 18], [19, 38], and [39, 65].
Notice
- The maximum value of
precisionis 65.
When the precision length is within the same range and scala remains unchanged, the underlying physical storage does not change and can be modified using online DDL. For example, changing from DECIMAL(10, 2) to DECIMAL(12, 2) can be done via online DDL.
For changes to the first parameter
precision, if increasing it (i.e., extending the length ofprecision), modifications can be made using Online DDL. For example, changing fromDECIMAL(10, 2)toDECIMAL(12, 2)can be done via Online DDL. However, if decreasing it (i.e., shortening the length ofprecision), Online DDL cannot be used; instead, Offline DDL must be employed. Modifications that cross precision intervals also require Offline DDL.For the second parameter
scala, any change in its length, whether increasing or decreasing, requires the use of offline DDL for modification; online DDL cannot be used. For example, changing fromDECIMAL(10, 2)toDECIMAL(10, 4)or fromDECIMAL(10, 2)toDECIMAL(10, 1)requires the use of offline DDL for modification.
VARCHAR variable length
To convert VARCHAR(x) to VARCHAR(y), you only need to ensure that y is greater than or equal to x. You can then use online DDL to upgrade the column type. For example, to convert VARCHAR(10) to VARCHAR(20), you can use online DDL to modify the column type.
VARBINARY variable length
To convert VARBINARY(x) to VARBINARY(y), you only need to ensure that the length of y is greater than or equal to the length of x. You can then use online DDL to modify the column type. For example, to convert VARBINARY(10) to VARBINARY(20), you can use online DDL to modify the column type.
Large data types
Notice
LOB columns cannot be used as indexes or primary keys.
Data type |
To MEDIUMBLOB |
To LONGBLOB |
|---|---|---|
| BLOB | Supported | Supported |
| MEDIUMBLOB | N/A | Supported |
Data type |
To MEDIUMTEXT |
To LONGTEXT |
|---|---|---|
| TEXT | Supported | Supported |
| MEDIUMTEXT | N/A | Supported |
Convert between VARCHAR and TINYTEXT
Data type |
To VARCHAR(x),x>=255 |
To TINYTEXT |
|---|---|---|
| TINYTEXT | Supported | N/A |
| VARCHAR(x),x<=255 | N/A | Supported |
Convert between VARBINARY and TINYBLOB
Data type |
To VARBINARY(x),x>=255 |
To TINYBLOB |
|---|---|---|
| TINYBLOB | Supported | N/A |
| VARBINARY(x), x <= 255 | N/A | Yes |