Column operations

In the MySQL mode of OceanBase Database, you can perform the following column operations: add a column at the end of a table, add a column in the middle of a table, drop a column, rename a column, reorder columns, modify the data type of a column, manage the default value of a column, manage constraints, and modify the value of an auto-increment column.

Add a column

In MySQL mode of OceanBase Database, you can add a column to the end of a table or to a specified position in a table. This topic describes how to add a column to the end of a table.

Add a column to the end of a table

Syntax:

ALTER TABLE table_name ADD COLUMN column_name data_type;

Parameters:

• table_name: the name of the table to which you want to add a column.

• column_name: the name of the column to be added.

• data_type: the data type of the column to be added.

Assume that a table named tbl1 exists in the database. The following example shows how to add a column named c1 to the end of the tbl1 table.

1. View the schema of the tbl1 table.

   obclient> DESCRIBE tbl1;
   

   The output shows that the tbl1 table contains three columns: id, name, and age.

   +-------+-------------+------+-----+---------+-------+
   | Field | Type        | Null | Key | Default | Extra |
   +-------+-------------+------+-----+---------+-------+
   | id    | int(11)     | NO   | PRI | NULL    |       |
   | name  | varchar(50) | NO   |     | NULL    |       |
   | age   | int(11)     | YES  |     | NULL    |       |
   +-------+-------------+------+-----+---------+-------+
   

2. Add a column named c1 to the end of the tbl1 table. The data type of the c1 column is INT.

   obclient> ALTER TABLE tbl1 ADD COLUMN c1 INT;
   

3. View the schema of the tbl1 table again.

   obclient> DESCRIBE tbl1;
   

   The output shows that the c1 column is added to the end of the tbl1 table.

   +-------+-------------+------+-----+---------+-------+
   | Field | Type        | Null | Key | Default | Extra |
   +-------+-------------+------+-----+---------+-------+
   | id    | int(11)     | NO   | PRI | NULL    |       |
   | name  | varchar(50) | NO   |     | NULL    |       |
   | age   | int(11)     | YES  |     | NULL    |       |
   | c1    | int(11)     | YES  |     | NULL    |       |
   +-------+-------------+------+-----+---------+-------+
   

Add a column to a specified position in a table

Syntax:

ALTER TABLE table_name 
    ADD COLUMN new_column_name data_type 
    {FIRST | BEFORE | AFTER} column_name;

Parameters:

• table_name: the name of the table to which you want to add a column.

• new_column_name: the name of the column to be added.

• data_type: the data type of the column to be added.

• FIRST | BEFORE | AFTER: specifies the position where you want to add the column. You can add the column to the beginning of the table (FIRST), before the column_name column (BEFORE), or after the column_name column (AFTER).

  Notice

  When you use FIRST to add a column to the beginning of the table, you do not need to specify the column_name parameter. Otherwise, an error will be returned.

• column_name: the name of the column that is adjacent to the column to be added.

Assume that a table named tbl1 exists in the database. The schema of the tbl1 table is as follows:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

The following example shows how to add a column named c1 to the beginning of the tbl1 table, a column named c2 before the name column, and a column named c3 after the name column.

1. Add a column named c1 to the beginning of the tbl1 table. The data type of the c1 column is INT.

   obclient> ALTER TABLE tbl1 ADD COLUMN c1 INT FIRST;
   

2. Add a column named c2 before the name column in the tbl1 table. The data type of the c2 column is VARCHAR.

   obclient> ALTER TABLE tbl1 ADD COLUMN c2 VARCHAR(50) BEFORE name;
   

3. Add a column named c3 after the name column in the tbl1 table. The data type of the c3 column is VARCHAR. The c3 column cannot be empty.

   obclient> ALTER TABLE tbl1 ADD COLUMN c3 VARCHAR(25) NOT NULL AFTER name;
   

4. View the schema of the tbl1 table again.

   obclient> DESCRIBE tbl1;
   

   The output shows that the c1, c2, and c3 columns are added to the beginning, before the name column, and after the name column, respectively.

   +-------+-------------+------+-----+---------+-------+
   | Field | Type        | Null | Key | Default | Extra |
   +-------+-------------+------+-----+---------+-------+
   | c1    | int(11)     | YES  |     | NULL    |       |
   | id    | int(11)     | NO   | PRI | NULL    |       |
   | c2    | varchar(50) | YES  |     | NULL    |       |
   | name  | varchar(50) | NO   |     | NULL    |       |
   | c3    | varchar(25) | NO   |     | NULL    |       |
   | age   | int(11)     | YES  |     | NULL    |       |
   +-------+-------------+------+-----+---------+-------+
   

Drop a column

Syntax:

ALTER TABLE table_name DROP COLUMN column_name

In the syntax, table_name specifies the name of the table from which you want to drop a column, and column_name specifies the name of the column to be dropped.

Assume that a table named tbl1 exists in the database. The schema of the tbl1 table is as follows:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

The following example shows how to drop the name column from the tbl1 table.

obclient> ALTER TABLE tbl1 DROP COLUMN name;

After the name column is dropped, execute the DESCRIBE tbl1; statement to view the schema of the tbl1 table. The output shows that the name column no longer exists in the tbl1 table.

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

Drop multiple columns

Syntax:

ALTER TABLE table_name DROP COLUMN column_name1, DROP COLUMN column_name2, ...;

In the syntax, table_name specifies the name of the table from which you want to drop a column, and column_name specifies the name of the column to be dropped. Assume that a table named tbl1 exists in the database. The schema of the tbl1 table is as follows:

+-------+--------------+------+------+---------+-------+
| FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+-------+--------------+------+------+---------+-------+
| C1    | NUMBER(30)   | NO   | PRI  | NULL    | NULL  |
| C2    | VARCHAR(50) | YES  | NULL | NULL    | NULL  |
| C3    | NUMBER(30)   | YES  | NULL | NULL    | NULL  |
| C4    | NUMBER(30)   | YES  | NULL | NULL    | NULL  |
| C5    | NUMBER(30)   | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+------+---------+-------+

The following example shows how to use the ALTER TABLE table_name DROP COLUMN column_name1, column_name2, ... statement to drop multiple columns from the tbl1 table.

obclient> ALTER TABLE tbl1 DROP COLUMN C4, DROP COLUMN C5;

After the C4 and C5 columns are dropped, execute the DESCRIBE tbl1; statement to view the schema of the tbl1 table. The output shows that the C4 and C5 columns no longer exist in the tbl1 table.

+-------+--------------+------+------+---------+-------+
| FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+-------+--------------+------+------+---------+-------+
| C1    | NUMBER(30)   | NO   | PRI  | NULL    | NULL  |
| C2    | VARCHAR(50) | YES  | NULL | NULL    | NULL  |
| C3    | NUMBER(30)   | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+------+---------+-------+

Drop obsolete columns

Syntax:

ALTER TABLE TABLE_NAME FORCE;

The following example shows how to drop the C2, C3, C4, and C5 columns from the tbl1 table.

obclient> ALTER TABLE tbl1 FORCE;

Rename columns

The following syntax examples show how to rename columns:

• Rename the column and specify a new data type for the column.

  ALTER TABLE table_name CHANGE old_col_name new_col_name data_type;
  

  Here are descriptions of the related parameters:

  • table_name: The table that contains the column to be renamed.

  • old_col_name: Specifies the name of the column to be renamed.

  • new_col_name: specifies the name of the column to be renamed.

  • data_type is a VARCHAR2 value that specifies the column's new data type. You can specify the original data type or another compatible data type for the column. For information about compatible data types, see Change the column type.

• Only the column name can be changed.

  ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name
  

  The relevant parameter descriptions are as follows:

  • table_name is the table name that contains the column whose name is to be renamed.

  • old_col_name : The name of the column that is to be renamed.

  • new_col_name: specifies the new name of the column after the column is renamed.

Assume that the database contains the tbl1 table, and the table structure is as follows:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

This example demonstrates how to execute a column rename statement by modifying the name column to c2, the age column to c3, and changing the data type of the age column to VARCHAR.

1. Change the column name name to c2.

   obclient> ALTER TABLE tbl1 RENAME COLUMN name TO c2;
   

2. Change the age column to c3 and change the data type to VARCHAR.

   obclient> ALTER TABLE tbl1 CHANGE age c3 VARCHAR(50);
   

3. Execute the DESCRIBE tbl1; command again to view the structure of the tbl1 table.

   The following example shows the output of the tbl1 table, which has only three columns (id, c2, and c3) and the data type of the c3 column is VARCHAR.

   +-------+-------------+------+-----+---------+-------+
   | Field | Type        | Null | Key | Default | Extra |
   +-------+-------------+------+-----+---------+-------+
   | id    | int(11)     | NO   | PRI | NULL    |       |
   | c2    | varchar(50) | YES  |     | NULL    |       |
   | c3    | varchar(50) | YES  |     | NULL    |       |
   +-------+-------------+------+-----+---------+-------+
   

Reordering

The following is the syntax for REORDER:

ALTER TABLE table_name 
    MODIFY [COLUMN] column_name data_type 
    {FIRST | BEFORE | AFTER} column_name;

The following table describes the related parameters:

• table_name: the name of the table to be reorganized.

• column_name: Specifies the column to reorder.

• data_type: specifies the data type of the column to be rearranged. You can specify the current data type, or change the data type of the column to another data type. For more information about the supported data types for changing, see Change a column's data type.

• FIRST | BEFORE | AFTER: specifies the location where the column is to be added to the table, either at the beginning of the table (FIRST), before the specified column (BEFORE), or after the specified column (AFTER).

  Notes

  When you use the FIRST keyword to add a column to the beginning of a table, you do not need to specify a column_name, otherwise the operation will fail.

• column_name: The name of the column that corresponds to the target position for reordering.

Suppose that the database contains the tbl1 table, with the following schema:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

The following example shows how to use the ALTER COLUMN statement to move the age column before the name column and change its data type to VARCHAR.

obclient> ALTER TABLE tbl1 MODIFY COLUMN age VARCHAR(50) BEFORE name;

If you execute the DESCRIBE tbl1; command again, the output is as follows. The age column is displayed before the name column, and the data type of age is VARCHAR.

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| age   | varchar(50) | YES  |     | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

Change the column type

The column type conversion supported by OceanBase Database is described below:

• Conversion of data types of character columns, including CHAR, VARCHAR, TINYTEXT, TEXT, and LONGTEXT.

• Data type conversion for numeric column data types, including TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT.

• Conversion of binary data types, such as BINARY, VARBINARY, BLOB, TINYBLOB, MEDIUMBLOB, and LONGBLOB.

• For data types with precision that support precision changes, such as VARCHAR, FLOAT, DOUBLE, and DECIMAL, a precision change can be specified when creating a table or a partition.

• Conversion between precision data types, including FLOAT, DOUBLE, and DECIMAL.

• Conversions between different data types, including INT, VARCHAR, DOUBLE, FLOAT, and DECIMAL.

OceanBase Database column type change rules are provided in Column type change rules.

The syntax of the ALTER_COLUMN_TYPE procedure is as follows:

ALTER TABLE table_name MODIFY [COLUMN] column_name data_type;

The following table describes the parameters:

• table_name: The name of the table that contains the column whose type you want to modify.

• column_name: The name of the column to be modified.

• data_type: The data type to use for the column.

Examples of conversions between character data types

The following example creates a table named test01:

obclient> CREATE TABLE test01 (c1 INT PRIMARY KEY, c2 CHAR(10), c3 CHAR(10));

We use the test01 table as an example to illustrate how to modify and increase the length of columns with a character data type.

• Change the data type of the c2 column to VARCHAR with a maximum length of 20 characters for the test01 table.

  obclient> ALTER TABLE test01 MODIFY c2 VARCHAR(20);
  

• The c2 column in the test01 table is set to the VARCHAR type and a maximum of 40 characters.

  obclient> ALTER TABLE test01 MODIFY c2 VARCHAR(40);
  

• Change the data type of column c2 in the test01 table to TINYTEXT.

  obclient> ALTER TABLE test01 MODIFY c2 TINYTEXT;
  

• Change the c2 column data type of the test01 table to LONGTEXT.

  obclient> ALTER TABLE test01 MODIFY c2 LONGTEXT;
  

• Modify the length of the c3 column in the test01 table to 20 characters.

  obclient> ALTER TABLE test01 MODIFY c3 CHAR(20);
  

• Alter the c3 column to the VARCHAR data type and specify the maximum length of 30 characters in the test01 table.

  obclient> ALTER TABLE test01 MODIFY c3 VARCHAR(30);
  

Example: Conversion between numeric data types

Integer types

Here's the statement to create a table named test02:

obclient> CREATE TABLE test02 (id INT PRIMARY KEY, name VARCHAR(10),age TINYINT, description VARCHAR(65525));

For example, suppose that the test02 table exists. To modify the data type and length of an integer-type column in the table, perform the following steps.

• Change the data type of the age column in test02 table to SMALLINT.

  obclient> ALTER TABLE test02 MODIFY age SMALLINT;
  

• Modify the age column data type of the test02 table to INT.

  obclient> ALTER TABLE test02 MODIFY age INT;
  

• Change the age column data type of the test02 table to BIGINT.

  obclient> ALTER TABLE test02 MODIFY age BIGGINT;
  

Data types that have precision

Create a table named test03, as shown in the example below:

obclient> CREATE TABLE test03(c1 INT, c2 FLOAT(8,2), c3 FLOAT(8,2), UNIQUE(c2, c3));

For example, consider the table test03 and use the following examples to modify the data types and lengths of columns with precision.

• Change the c2 column of test03 table to store 5 digit numbers.

  obclient> ALTER TABLE test03 MODIFY c2 FLOAT(5,2);
  

• Modify the data type of the c2 column in the test03 table to DOUBLE and specify that it can store a total of 10 digits, with 4 digits to the right of the decimal point.

  obclient> ALTER TABLE test03 MODIFY c2 DOUBLE(10,4);
  

• Modify the data type of column c2 in the test03 table to DOUBLE. Set the numeric precision to 5 digits, with 2 digits after the decimal point.

  obclient> ALTER TABLE test03 MODIFY c2 DOUBLE(5,2);
  

• Modify the data type of column c2 of table test03 to DECIMAL and set the total number of digits to 20, with 4 digits after the decimal point.

  obclient> ALTER TABLE test03 MODIFY c2 DECIMAL(20, 4);
  

Data Type Conversion Example of a Binary Type

Here is the statement to create the test04 table:

obclient> CREATE TABLE test04 (c1 TINYBLOB, c2 BINARY(64));

For example, the following statements modify the data types and lengths of the binary data type columns in the test04 table.

• Change the data type of the c1 column in the test04 table to BLOB.

  obclient> ALTER TABLE test04 MODIFY c1 BLOB;
  

• Change the data type of the c1 column in the test04 table to BINARY and specify that the maximum size is 256 bytes.

  obclient> ALTER TABLE test04 MODIFY c1 BINARY(256);
  

• Change the c1 column data type in the test04 table to VARCHAR and specify that its maximum length is 256 characters.

  obclient> ALTER TABLE test04 MODIFY c1 VARCHAR(256);
  

Example that converts an integer value to a character value and vice versa

CREATE TABLE test05 AS SELECT * FROM all_objects;

obclient> CREATE TABLE test05 (c1 INT);

1. Run the following command to modify the data type of the c1 column to VARCHAR in the test05 table and specify a maximum length of 64 characters:

   obclient> ALTER TABLE test05 MODIFY c1 VARCHAR(64);
   

2. Run the following commands to modify the c1 column in the test05 table to INT type.

   obclient> ALTER TABLE test05 MODIFY c1 INT;
   

Manage column defaults

In MySQL mode of OceanBase Database, you can modify or delete the default values of columns. This topic describes how to do so.

Modify the default value of a column

If no default value is specified for a column, the default value is NULL. The syntax for modifying the default value of a column is as follows:

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT const_value;

The following table describes the parameters in the preceding syntax.

• table_name: the name of the table that contains the column whose default value you want to modify.

• column_name: the name of the column whose default value you want to modify.

• const_value: the new default value of the column.

Assume that the database contains a table named tbl1. The table structure of tbl1 is as follows:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

The following example shows how to modify the default value of the age column to 18.

obclient> ALTER TABLE tbl1 ALTER COLUMN age SET DEFAULT 18;

Execute the DESCRIBE tbl1; statement again to view the table structure of tbl1. The output shows that the default value of the age column is 18.

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | int(11)     | YES  |     | 18      |       |
+-------+-------------+------+-----+---------+-------+

Delete the default value of a column

The syntax for deleting the default value of a column is as follows:

ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;

In the preceding syntax, table_name specifies the name of the table that contains the column whose default value you want to delete, and column_name specifies the name of the column.

Assume that the database contains a table named tbl1. The table structure of tbl1 is as follows:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | int(11)     | YES  |     | 18      |       |
+-------+-------------+------+-----+---------+-------+

The following example shows how to delete the default value of the age column.

obclient> ALTER TABLE tbl1 ALTER COLUMN age DROP DEFAULT;

Execute the DESCRIBE tbl1; statement again to view the table structure of tbl1. The output shows that the default value of the age column is NULL.

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

Manage constraints

In MySQL mode of OceanBase Database, you can add constraints to a table, such as modifying an existing column to an auto-increment column, setting whether a column can be null, and specifying the uniqueness of a column. This topic describes how to do so.

The syntax for managing constraints is as follows:

ALTER TABLE table_name 
    MODIFY [COLUMN] column_name data_type
    [AUTO_INCREMENT] 
    [NULL | NOT NULL] 
    [[PRIMARY] KEY] 
    [UNIQUE [KEY]];

The following table describes the parameters in the preceding syntax.

• table_name: the name of the table that contains the column to which you want to add a constraint.

• column_name: the name of the column to which you want to add a constraint.

• data_type: the data type of the column to be modified. You can specify the current data type or change the column to another data type. For more information about the supported data types, see Modify the column type.

• AUTO_INCREMENT: specifies that the selected column is an auto-increment column.

• NULL | NOT NULL: specifies whether the selected column can be null (NULL) or cannot be null (NOT NULL).

• [PRIMARY] KEY: specifies that the selected column is a primary key.

• UNIQUE [KEY]: specifies that the selected column is unique.

Assume that the database contains a table named tbl1. The table structure of tbl1 is as follows:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

1. Set the id column as the primary key.

   obclient> ALTER TABLE tbl1 MODIFY COLUMN id INT PRIMARY KEY;
   

2. Set the id column as an auto-increment column.

   obclient> ALTER TABLE tbl1 MODIFY COLUMN id INT AUTO_INCREMENT;
   

3. Set the name column to NOT NULL.

   obclient> ALTER TABLE tbl1 MODIFY COLUMN name VARCHAR(50) NOT NULL;
   

4. Set the age column to be unique.

   obclient> ALTER TABLE tbl1 MODIFY COLUMN age INT UNIQUE;
   

5. Execute the DESCRIBE tbl1; statement again to view the table structure of tbl1.

   +-------+-------------+------+-----+---------+----------------+
   | Field | Type        | Null | Key | Default | Extra          |
   +-------+-------------+------+-----+---------+----------------+
   | id    | int(11)     | NO   | PRI | NULL    | auto_increment |
   | name  | varchar(50) | NO   |     | NULL    |                |
   | age   | int(11)     | YES  | UNI | NULL    |                |
   +-------+-------------+------+-----+---------+----------------+
   

Modify the value of an auto-increment column

The syntax for modifying the value of an auto-increment column is as follows:

ALTER TABLE table_name [SET] AUTO_INCREMENT = next_value;

Assume that the database contains a table named tbl1. The following example shows how to modify the value of the auto-increment column in tbl1 to 12.

obclient> ALTER TABLE tbl1 AUTO_INCREMENT = 12;

After the modification, the next auto-increment value for tbl1 is set to 12. When you insert a new record, the auto-increment column (for example, c1) starts from 12 and increments.

References

ALTER TABLE