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

  For an integer column with a CHECK constraint, you can use an online DDL operation to change the data type to another integer data type that supports a wider value range, such as from INT to BIGINT. However, you cannot change the data type of other columns with a CHECK constraint.

• 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

• To change the length of a column without changing its data type, if the column is just an index column, you can perform an online DDL operation. For example, you can change a CHAR column to a CHAR(11) column online. Since OceanBase Database can store CHAR data online, data rewriting is not required in this example. If the column is a primary key column or a partitioning key column, you need to perform an offline DDL operation. In other words, to change the length of a primary key column or a partitioning key column without changing its data type, perform an offline DDL operation.

  • To change the length of a VARCHAR column, you can perform an online DDL operation. However, you must also change the schema of dependent objects such as the index table. Note that an online DDL operation can be performed in this case to change the column length even though the column is used as a primary key or partitioning key. In other words, even if the data does not change, the schema of the dependent object must still be changed.

  • To change the length of an integer column, you can perform an online DDL operation. The column can be a common column, a primary key column, a partitioning key column, an index column, a column that a generated column depends on, or a column with a CHECK constraint.

• In OceanBase Database, to change the data type of a numeric column, character column, or date and time column, you can perform an online DDL operation if this column is not referenced by an index table, foreign key, primary key, or partitioning key. If the column is referenced by a dependent object, you need to perform an offline DDL operation. In other words, offline DDL operations are required for changing the data type of columns on which any objects depend.

• In OceanBase Database, if an integer column is a foreign key column, a column on which a foreign key depends, or a generated column, you cannot change the column type.

• 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.