Skip to main content

Column type change rules

When you change the column type in seekdb, you need to consider the column type change rules at the table level first, especially the scenarios where some data types are disabled. Then, consider the rules for changing the source data type to the target data type.

Column type change rules at the table level​

Disabled rules​

  • Foreign key constraints

    For Online DDL and Offline DDL, except for the following special cases (note that the type modification only changes the display precision and does not change the storage structure), other scenarios do not support modifying the type of foreign key columns.

    • If the column data type is FLOAT(m,n), the precision can be changed, but SIGNED and UNSIGNED cannot be converted to each other.

    • If the column data type is DOUBLE(m,n), the precision can be changed, but SIGNED and UNSIGNED cannot be converted to each other.

    • If the column data type is VARCHAR(m), the precision can be increased, but cannot be decreased, and cannot be changed to the CHAR type.

  • CHECK constraints

    For Online DDL and Offline DDL, except for columns of the INTEGER type, columns with CHECK constraints do not support type modification.

  • Trigger constraints

    For Offline DDL, when a column has a trigger constraint that is not DISABLE, changing the column type is prohibited. If all triggers are DISABLE, it does not affect the DDL operation, and the new table must include this DISABLE trigger.

Change rules​

  • When the data type length increases within the same data type, such as changing the data type of a column from VARCHAR(8) to VARCHAR(11), this operation can be performed as an Online DDL without data rewrite.

    • If the column length is modified using Online DDL and the column has an index table (e.g., the length of a VARCHAR column increases), the schema of the index table and related dependent objects must also be modified during the Online DDL operation. That is, "the user expects the data to remain unchanged, but the schema of the dependent objects must also be modified."
    • For columns of the INTEGER and DecimalInt types, if they are indexed or primary key columns, the column type growth operation can be performed using Online DDL.

  • In seekdb, for the growth of the LOB type, Online DDL operations support the following scenarios:

    • Modification of regular columns.
    • Modification of generated columns (Store/Virtual).
    • Modification of columns that depend 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 occurs, an Offline operation must be performed if type changes are allowed. For character data types, if a change in character set and collation occurs, an Offline operation must be performed if column type changes are allowed.

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

Column conversion rules​

In seekdb, all column data types are divided into three major categories: numeric data types, character data types, and time data types. The conversion rules within each category 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 category.

Conversion between numeric data types​

Numeric data types are divided into the following categories:

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

  • Floating-point: FLOAT, DOUBLE

  • Fixed-point: NUMBER

  • Binary: BIT

The following table describes the conversion between numeric data types.

Data typeTo TINYINT、SMALLINT、MEDIUMINT、INT、BIGINTTo FLOAT、DOUBLETo NUMBERTo BIT
IntegerSupportedSupportedSupportedSupported
Floating-pointSupportedSupportedSupportedSupported
Fixed-pointSupportedSupportedSupportedSupported
BinarySupportedSupportedSupportedSupported

Conversions between character data types​

Character data types are divided into the following categories:

  • CHAR and VARCHAR

  • BINARY and VARBINARY

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

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

  • ENUM and SET

The following table describes the conversions between character data types.

Data typeTo CHARTo BINARYTo VARCHAR\ VARBINARYTo TEXT\ BLOBTo ENUM\ SET
CHARSupportedSupportedSupportedSupportedSupported
BINARYSupportedSupportedSupportedSupportedSupported
VARCHAR、 VARBINARYSupportedSupportedSupportedSupportedSupported
TEXT large types, BLOB large typesSupportedSupportedSupportedSupportedSupported
ENUM、 SETSupportedSupportedSupportedSupportedSupported

Conversions within the time data types​

The time data types include:

  • DATE

  • TIMESTAMP

  • DATETIME

  • TIME

  • YEAR

The following table describes the conversions within the time data types.

Data typeTo DATETo TIMESTAMPDATETIMETIMEYEAR
DATESupportedSupportedSupportedSupportedSupported
TIMESTAMPSupportedSupportedSupportedSupportedSupported
DATETIMESupportedSupportedSupportedSupportedSupported
TIMESupportedSupportedSupportedSupportedSupported
YEARSupportedSupportedSupportedSupportedSupported

Conversion between numeric and character data types​

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

Data typeTo integer, float, and decimal typesTo BIT typeTo CHAR, BINARY, VARCHAR, VARBINARY, TEXT, and BLOB typesTo ENUM and SET types
Integer, float, and decimal typesSee the conversion rules within the category.See the conversion rules within the category.SupportedSupported
BIT typeSee the conversion rules within the category.See the conversion rules within the category.SupportedSupported
CHAR, BINARY, VARCHAR, VARBINARY, TEXT, and BLOB typesSupportedSupportedSee the conversion rules within the category.Supported
ENUM and SETYesYesSee the conversion rules in the corresponding category.See the conversion rules in the corresponding category.

Conversion between numeric and time categories​

The following table describes the conversion between numeric and time categories.

Data typeTo integer, float, decimal, or BITTo DATETo DATETIMETo TIMESTAMPTo TIMETo YEAR
Integer, floating-point, fixed-point, and BIT typesSee the conversion rules in the corresponding category.SupportedSupportedSupportedSupportedSupported
DATESupportedSee the conversion rules in the corresponding category.See the conversion rules in the corresponding category.See the conversion rules in the corresponding category.See the conversion rules in the corresponding category.See the conversion rules in the corresponding category.
DATETIMESupportedSee the conversion rules in the corresponding category.See the conversion rules in the corresponding category.See the conversion rules in the corresponding category.See the conversion rules in the corresponding category.See the conversion rules in the corresponding category.
TIMESTAMPSupportedFor more information, see the conversion rules in the corresponding category.For more information, see the conversion rules in the corresponding category.For more information, see the conversion rules in the corresponding category.For more information, see the conversion rules in the corresponding category.For more information, see the conversion rules in the corresponding category.
TIMESupportedFor more information, see the conversion rules in the corresponding category.For more information, see the conversion rules in the corresponding category.For more information, see the conversion rules in the corresponding category.For more information, see the conversion rules in the corresponding category.For more information, see the conversion rules in the corresponding category.
YEARSupportedSee the conversion rules in the category.See the conversion rules in the category.See the conversion rules in the category.See the conversion rules in the category.See the conversion rules in the category.

Conversion between character classes and time classes​

The following table describes the conversion between character classes and time classes.

Data typeTo CHAR, BINARY, VARCHAR, VARBINARY, TEXT, and BLOB typesTo ENUM and SET typesTo DATETo DATETIMETo TIMESTAMPTo TIMETo YEAR
CHAR, BINARY, VARCHAR, VARBINARY, TEXT, and BLOB typesSee the conversion rules within each category.See the conversion rules within each category.SupportedSupportedSupportedSupportedSupported
ENUM, SETYesYesYesYesYes
DATESupportedSupportedSee the conversion rules in the category.See the conversion rules in the category.See the conversion rules in the category.See the conversion rules in the category.See the conversion rules in the category.
DATETIMESupportedSupportedSee the conversion rules in the category.See the conversion rules in the category.See the conversion rules in the category.See the conversion rules in the category.See the conversion rules in the category.
TIMESTAMPSupportedSupportedFor conversion rules, see the conversion rules in the corresponding major category.For conversion rules, see the conversion rules in the corresponding major category.For conversion rules, see the conversion rules in the corresponding major category.For conversion rules, see the conversion rules in the corresponding major category.For conversion rules, see the conversion rules in the corresponding major category.
TIMESupportedSupportedFor conversion rules, see the conversion rules in the corresponding major category.For conversion rules, see the conversion rules in the corresponding major category.For conversion rules, see the conversion rules in the corresponding major category.For conversion rules, see the conversion rules in the corresponding major category.For conversion rules, see the conversion rules in the corresponding major category.
YEARSupportedSupportedSee the conversion rules in the corresponding category.See the conversion rules in the corresponding category.See the conversion rules in the corresponding category.See the conversion rules in the corresponding category.See the conversion rules in the corresponding category.

Online DDL scenarios for column type conversion​

Integer type conversion​

Data typeTo SMALLINTTo MEDIUMINTTo INTTo BIGINT
TINYINTSupportedSupportedSupportedSupported
SMALLINTN/ASupportedSupportedSupported
MEDIUMINTNot supportedN/ASupportedSupported
INTNot supportedNot supportedN/ASupported
Data typeTo USMALLINTTo UMEDIUMINTTo UINTTo UBIGINT
UTINYINTSupportedSupportedSupportedSupported
USMALLINTN/ASupportedSupportedSupported
UMEDIUMINTNot supportedN/ASupportedSupported
UINTNot supportedNot supportedN/ASupported

DECIMAL/DECIMALINT​

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

tip

In seekdb, the maximum value of precision is 65.

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

  • If the first parameter precision changes, you can use Online DDL to increase the precision value. For example, you can use Online DDL to change a DECIMAL(10, 2) column to a DECIMAL(12, 2) column. However, if the precision value decreases, you cannot use Online DDL to modify the column. In this case, you must use Offline DDL. If the precision value changes across ranges, you must use Offline DDL.

  • If the second parameter scala changes, you must use Offline DDL to modify the column. In other words, you cannot use Online DDL. For example, you must use Offline DDL to change a DECIMAL(10, 2) column to a DECIMAL(10, 4) column or to a DECIMAL(10, 1) column.

VARCHAR type conversion​

You can use Online DDL to change a VARCHAR(x) column to a VARCHAR(y) column, provided that y is greater than or equal to x. For example, you can use Online DDL to change a VARCHAR(10) column to a VARCHAR(20) column.

VARBINARY type conversion​

You can use Online DDL to change a VARBINARY(x) column to a VARBINARY(y) column, provided that y is greater than or equal to x. For example, you can use Online DDL to change a VARBINARY(10) column to a VARBINARY(20) column.

Large object (LOB) type conversion​

tip

LOB columns cannot be used as indexes or primary keys.

Data typeTo MEDIUMBLOBTo LONGBLOB
BLOBSupportedSupported
MEDIUMBLOBN/ASupported
Data typeTo MEDIUMTEXTTo LONGTEXT
TEXTSupportedSupported
MEDIUMTEXTN/ASupported

Conversion between VARCHAR and TINYTEXT​

Data typeTo VARCHAR(x), x >= 255To TINYTEXT
TINYTEXTSupportedN/A
VARCHAR(x), x <= 255N/ASupported

Conversion between VARBINARY and TINYBLOB​

Data typeTo VARBINARY(x), x >= 255To TINYBLOB
TINYBLOBSupportedN/A
VARBINARY(x), x <= 255N/ASupported

Contents

Edit this page