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

  In the MySQL mode of OceanBase Database, you cannot change the data types of columns with CHECK constraints 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

• For a variable-length data type, if you change the data type of a column from CHAR to CHAR(11), data does not need to be rewritten because CHAR data stored in OceanBase Database supports online DDL operations. If an index is created on a column, you can change the length of this index column by using an online DDL operation. To specify the column as a primary key or partitioning key, perform an offline operation. In other words, to change the length of a column without changing the data type and specify the column as a primary key or partitioning key, perform offline DDL operations.

  For the variable-length VARCHAR data type, you can change the data length by using an online DDL operation. However, you must also change the schemas of dependent objects such as the index table. In this case, the primary key or partitioning key can be changed by using an online DDL operation. In other words, even if the data does not change, the schema of the dependent object must still be changed.

• In OceanBase Database, the type change of a column may occur within a category of data types (such as numeric, character, and date and time data types). If this column is not referenced by an index table, foreign key, primary key, or partitioning key, you can perform online DDL operations to change the column type. Otherwise, you need to perform offline DDL operations to change the column type. In other words, offline DDL operations are required for column type change if any objects depend on the 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.