Column type change rules

When you change the data type of a column in the MySQL mode of OceanBase Database, you must first consider the table-level column type change rules, especially in scenarios where some data types are forbidden, and then consider the change rules concerning the source data type and target data type.

Table-level column type change rules

Prohibitory rules

• FOREIGN KEY constraints

  The data type of a foreign key column cannot be changed by using online or offline DDL operations except in the following special cases. Note that only the display precision is changed and the storage structure is not changed.

  • If the data type of the column is FLOAT(m,n), you can change the precision. However, conversion between SIGNED and UNSIGNED is not supported.

  • If the data type of the column is DOUBLE(m,n), you can change the precision. However, conversion between SIGNED and UNSIGNED is not supported.

  • If the data type of the column is VARCHAR(m), you can only increase the precision. You cannot decrease the precision or change the data type to CHAR.

• CHECK constraints

  You cannot change the data type of a column with a CHECK constraint, except for a column of the INTEGER type, by using online or offline DDL operations.

• Trigger constraints

  You cannot change the data type of a column with a non-DISABLE trigger constraint by using offline DDL operations. For a table with only a DISABLE trigger, DDL operations are not affected, and the new table must contain this DISABLE trigger.

Change rules

• You can increase the length of a column without changing its data type, for example, change a VARCHAR(8) column to a VARCHAR(11) column, by using an online DDL operation without rewriting data.

  • If you perform an online DDL operation to change the length of a column with a dependent object such as an index table, for example, increase the length of a VARCHAR column, you must also change the schema of the dependent object. In other words, the schema of the dependent object must still be changed even though data is expected not to change.
  • You can perform an online DDL operation to increase the length of an INTEGER or DECIMALINT column when it is an indexed column or a primary key column.

• OceanBase Database supports online DDL operations for increasing the length of LOB columns in the following scenarios:

  • Modify a LOB column as a normal column.
  • Modify a LOB column as a stored generated column or a virtual generated column.
  • Modify a LOB column as a column on which a stored generated column or a virtual generated column depends.
  • Add a NOT NULL or CHECK constraint to a LOB column.
  • Modify the original NOT NULL constraint for a LOB column.

• When column type change is allowed, to convert between SIGNED and UNSIGNED values of a numeric data type, perform an offline DDL operation. When column type change is allowed, to change the character set and collation, perform an offline DDL operation.

• If a column is referenced by a generated column, you can still change its data type.

Column-level conversion rules

In OceanBase Database, all column data types are classified into three categories: numeric data types, character data types, and date and time data types. The conversion rules are similar for the three categories. The change rules are also similar within each category. However, some special cases exist.

The following tables describe the conversion between categories and within each category.

Conversion between numeric data types

Numeric data types include the following ones:

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

• Floating-point types: FLOAT and DOUBLE

• Fixed-point type: NUMBER

• Binary type: BIT

The following table describes the conversion between numeric data types.

Conversion between character data types

Character data types include the following ones:

• 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 conversion between character data types.

Conversion between date and time data types

Date and time data types include the following ones:

• DATE

• TIMESTAMP

• DATETIME

• TIME

• YEAR

The following table describes the conversion between date and time data types.

Conversion between numeric data types and character data types

The following table describes the conversion between numeric data types and character data types.

Conversion between numeric data types and date and time data types

The following table describes the conversion between numeric data types and date and time data types.

Conversion between character data types and date and time data types

The following table describes the conversion between character data types and date and time data types.

Conversion to larger column types by using online DDL operations

Conversion between integer data types

DECIMAL/DECIMALINT

DECIMAL(precision, scala) is a decimal numeric data type. precision indicates the total length of a number, and scala indicates the number of digits in the fractional part. In online DDL operations, the ranges of precision are [1,9], [10,18], [19,38] and [39,65].

You can perform an online DDL operation to convert a DECIMAL type into another DECIMAL type that provides a precision in the same range and has the same scala, because such conversion does not change the underlying physical storage. For example, you can perform an online DDL operation to convert the DECIMAL(10, 2) type into the DECIMAL(12, 2) type.

• You can perform an online DDL operation to convert a DECIMAL type into another DECIMAL type with a larger precision. For example, you can perform an online DDL operation to convert the DECIMAL(10, 2) type into the DECIMAL(12, 2) type. However, you can only perform an offline DDL operation to convert a DECIMAL type into another DECIMAL type with a smaller precision. You can only perform an offline DDL operation to convert a DECIMAL type into another DECIMAL type with a precision in another range.

• You can only perform an offline DDL operation to convert a DECIMAL type into another DECIMAL type with a different scala, no matter whether the scala is increased or decreased. For example, you must perform an offline DDL operation to convert the DECIMAL(10, 2) type into the DECIMAL(10, 4) type, or the DECIMAL(10, 2) type into the DECIMAL(10, 1) type.

Increase the length of a VARCHAR data type

You can perform an online DDL operation to convert the VARCHAR(x) type into the VARCHAR(y) type when y is greater than or equal to x. For example, you can perform an online DDL operation to convert the VARCHAR(10) type into the VARCHAR(20) type.

Increase the length of a VARBINARY data type

You can perform an online DDL operation to convert the VARBINARY(x) type into the VARBINARY(y) type when y is greater than or equal to x. For example, you can perform an online DDL operation to convert the VARBINARY(10) type into the VARBINARY(20) type.

LOB types

Conversion between VARCHAR and TINYTEXT

Conversion between VARBINARY and TINYBLOB