Column type change rules

When changing the column type in a table in OceanBase Database in MySQL mode, you must first consider the column type change rules at the table level, especially for some disabled data types. Then, consider the change rules for the source and target data types during the column type change.

Column type change rules at the table level

Disabled rules

• Foreign key constraints

  For Online DDL and Offline DDL, column type changes are not supported in the following scenarios (note that the type modifications only change the display precision and do not affect the storage structure):

  • If the column's data type is FLOAT(m,n), precision changes are supported, but conversion between SIGNED and UNSIGNED is not.

  • If the column's data type is DOUBLE(m,n), precision changes are supported, but conversion between SIGNED and UNSIGNED is not.

  • If the column's data type is VARCHAR(m), increasing the precision is supported, but decreasing it is not, and changing it to the CHAR type is also not supported.

• CHECK constraints

  For Online DDL and Offline DDL, column type changes are not supported for columns with CHECK constraints, except for columns of the INTEGER type.

• Trigger constraints

  For Offline DDL, column type changes are not supported if the column has a non-DISABLE trigger constraint. If all triggers are DISABLE, the DDL operation is not affected, and the new table must include the DISABLE trigger.

Change rules

• When the data type remains the same but the length increases, for example, changing a column's data type from VARCHAR(8) to VARCHAR(11), this operation can be performed as an Online DDL without requiring data rewrites.

  • If you modify the column length using Online DDL and the column has an index table (e.g., increasing the length of a VARCHAR column), you must also update the schema of the index table and other dependent objects during the Online DDL operation. This means that the user's data should remain unchanged, but the schema of dependent objects must also be updated.
  • For columns of the INTEGER or DecimalInt type, if they are indexed or primary key columns, the column type growth can be performed using Online DDL.

• In OceanBase Database, Online DDL operations support the following scenarios for LOB type growth:

  • Modifying regular columns.
  • Modifying generated columns (store/virtual).
  • Modifying columns that are dependent on generated columns (store/virtual).
  • Adding NOT NULL and CHECK constraints.
  • Modifying existing NOT NULL constraints.

• For numeric data types, if a conversion between SIGNED and UNSIGNED is required, an Offline DDL operation must be performed. For character data types, if a change in character set or collation is required, an Offline DDL operation must also be performed.

• If a column is referenced by a generated column, column type changes are allowed.

Column conversion rules

In OceanBase Database, all column data types are divided into three major categories: numeric data types, character data types, and datetime data types. The conversion rules within each of these categories are similar, and the conversion rules between different categories are also similar. However, there are some special cases.

The following tables list the conversion rules within and between each of these categories.

Conversion between numeric data types

Numeric data types are categorized as follows:

• Integer types: TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT

• Floating-point types: FLOAT and DOUBLE

• Fixed-point types: NUMBER

• Binary types: BIT

The following table describes the conversion between numeric data types.

Conversion within character data types

Character data types are categorized as follows:

• CHAR and VARCHAR

• BINARY and VARBINARY

• TEXT types: TINYTEXT, MEDIUMTEXT, TEXT, and LONGTEXT

• BLOB types: TINYBLOB, MEDIUMBLOB, BLOB, and LONGBLOB

• ENUM and SET

The following table describes the conversions within character data types.

Conversions within the time data type category

Time data types include:

• DATE

• TIMESTAMP

• DATETIME

• TIME

• YEAR

The following table shows the conversions within the time data type category.

Conversion between numeric and character categories

The conversion between numeric and character categories is shown in the following table.

Conversion between numeric and temporal categories

The following table shows the conversion between numeric and temporal categories.

Conversion between character and temporal types

The following table describes the conversion between character and temporal types.

Online DDL column type upcasting scenarios

Integer type conversion

DECIMAL/DECIMALINT

DECIMAL(precision, scala) specifies a decimal number type, where precision represents the total length of the number, and scala represents the number of decimal places. During Online DDL, the precision of DECIMAL can be in the range [1, 9], [10, 18], [19, 38], or [39, 65].

If the precision value is within the same range and the scala value remains unchanged, the underlying physical storage will not change. You can use Online DDL to modify the column type. For example, you can use Online DDL to change the column type from DECIMAL(10, 2) to DECIMAL(12, 2).

• For changes to the first parameter precision, if the length increases (i.e., the precision value increases), you can use Online DDL to modify the column type. For example, you can use Online DDL to change the column type from DECIMAL(10, 2) to DECIMAL(12, 2). However, if the length decreases (i.e., the precision value decreases), you cannot use Online DDL to modify the column type. You must use Offline DDL. Cross-range changes also require Offline DDL.

• For changes to the second parameter scala, whether the value increases or decreases, you must use Offline DDL to modify the column type. You cannot use Online DDL. For example, you must use Offline DDL to change the column type from DECIMAL(10, 2) to DECIMAL(10, 4) or from DECIMAL(10, 2) to DECIMAL(10, 1).

VARCHAR type

If you want to change the column type from VARCHAR(x) to VARCHAR(y), you can use Online DDL to modify the column type as long as the length of y is greater than or equal to the length of x. For example, you can use Online DDL to change the column type from VARCHAR(10) to VARCHAR(20).

VARBINARY type

If you want to change the column type from VARBINARY(x) to VARBINARY(y), you can use Online DDL to modify the column type as long as the length of y is greater than or equal to the length of x. For example, you can use Online DDL to change the column type from VARBINARY(10) to VARBINARY(20).

Large data types

VARCHAR and TINYTEXT types

VARBINARY and TINYBLOB types