Column operations

In the MySQL-compatible 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, rearrange columns, change the data type of a column, manage the default value of a column, manage constraints, and change the value of an auto-increment column.

Add a column

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

Add a column to the end of a table

To add a column to the end of a table, use the following syntax:

ALTER TABLE table_name ADD COLUMN column_name data_type;

The following table describes the parameters in the syntax.

| Parameter | Description |

| 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 has been 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 specific position in a table

To add a column to a specific position in a table, use the following syntax:

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

The following table describes the parameters in the syntax.

| Parameter | Description |

| 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 | The position to which you want to add the column. Valid values: FIRST, BEFORE, and AFTER. FIRST specifies to add the column to the beginning of the table. BEFORE specifies to add the column before the specified column. AFTER specifies to add the column after the specified column. |

| column_name | The name of the column before or after which you want to add a column. |

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 have been added to the beginning, before the name column, and after the name column of the tbl1 table, 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

To drop a column, use the following 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 again to view the schema of the tbl1 table. The output shows that the name column no longer exists.

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

Drop multiple columns

To drop multiple columns, use the following 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 columns, and column_name specifies the names of the columns 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 drop the C4 and C5 columns from the tbl1 table by using the ALTER TABLE table_name DROP COLUMN column_name1, column_name2, ... statement.

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

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

+-------+--------------+------+------+---------+-------+
| 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

To drop obsolete columns, use the following 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 a column

You can execute the following statements to rename a column in two ways:

• Rename a column and modify its data type.

  ALTER TABLE table_name CHANGE old_col_name new_col_name data_type;
  

  The following table describes the parameters.

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

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

  • new_col_name: the new name of the column.

  • data_type: the data type of the column to be renamed. You can set the parameter to the current data type of the column or specify a new data type, which is specified in Change a column type.

• Rename a column without modifying its data type.

  ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name
  

  The following table describes the parameters.

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

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

  • new_col_name: the new name of the column.

Assume that a table named tbl1 exists in the database and its schema 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 describes how to execute a RENAME statement to rename the name column to c2, rename the age column to c3, and modify the data type of the age column to VARCHAR.

1. Rename the name column to c2.

   obclient> ALTER TABLE tbl1 RENAME COLUMN name TO c2;
   

2. Rename the age column to c3 and modify its data type to VARCHAR.

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

3. Run the DESCRIBE tbl1; statement to check the schema of tbl1.

   The output shows that only id, c2, and c3 columns exist in tbl1 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    |       |
   +-------+-------------+------+-----+---------+-------+
   

Relocate a column

You can execute the following statement to relocate a column:

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

The following table describes the parameters.

• table_name: the name of the table that contains the column to be relocated.

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

• data_type: the data type of the column to be relocated. You can set the parameter to the current data type of the column or specify a new data type, which is specified in Change a column type.

• FIRST | BEFORE | AFTER: specifies the position where the column is to be added. FIRST specifies to add the column to the beginning of the table, BEFORE specifies to add the column before column_name, and AFTER specifies to add the column after column_name.

  Notice

  If the column is to be added to the beginning of the table, you cannot set column_name, otherwise an error will be returned.

• column_name: the name of the column that specifies the relocation position.

Assume that a table named tbl1 exists in the database and its schema 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 describes how to relocate the age column before the name column, and change the data type of the age column to VARCHAR.

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

Run the DESCRIBE tbl1; statement to check the schema of tbl1. The output shows that the age column is located before the name column, and the data type of the age column 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 column type

OceanBase Database supports the following type conversions:

• Type conversion for columns of character type including CHAR, VARCHAR, TINYTEXT, TEXT, and LONGTEXT.

• Data type conversion of numeric type columns, including TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT.

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

• Data types with precision support changes in precision, including VARCHAR, FLOAT, DOUBLE, and DECIMAL.

• Casting between types with precision, including FLOAT, DOUBLE, and DECIMAL.

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

For column type change rules in OceanBase Database, see Column type change rules.

You can execute the following statement to modify the column type:

ALTER TABLE table_name MODIFY [COLUMN] column_name data_type;

The description of the related parameters is as follows:

• table_name: specifies the name of the table that contains the column whose type is to be modified.

• column_name specifies the name of the column whose data type you want to modify.

• data_type: specifies the data type to which the column will be changed.

Examples of character data type conversion

The following example shows how to create a table named test01:

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

Use the ALTER TABLE statement to modify the data types of columns with the CHAR data type to the VARCHAR2 data type and increase the maximum length allowed. The following example modifies the data type and length of the TEST01 column in the T1 table:

• Modify the c2 column data type of test01 table to VARCHAR and set the maximum length to 20 characters.

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

• ALTER test01 ALTER COLUMN c2 VARCHAR(40)

  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 data type of the c2 column in the test01 table to LONGTEXT.

  obclient> ALTER TABLE test01 MODIFY c2 LONGTEXT;
  

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

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

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

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

Sample conversion between numeric data types

Integer types

The following sample statement creates a table named test02:

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

For example, to modify the data type of the integer type column in the test02 table and increase its length, you can use the following examples:

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

  obclient> ALTER TABLE test02 MODIFY age SMALLINT;
  

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

  obclient> ALTER TABLE test02 MODIFY age INT;
  

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

  obclient> ALTER TABLE test02 MODIFY age BIGGINT;
  

With a precise data type

As shown in the following example, create the test03 table:

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

Take the test03 table as an example, and provide several examples to show how to modify the data type and length of columns with specified precision.

• Modify the c2 column of the test03 table to allow 5-digit storage.

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

• Change the c2 column of the test03 table to type DOUBLE and allow a total of 10 digits, with 4 decimal places.

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

• Alter the data type of c2 column in test03 table to DOUBLE, allowing for 5 digits in total, with 2 decimal places.

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

• In table test03, modify column c2 to be of type DECIMAL and allow storage of 20 digits, with 4 digits after the decimal point.

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

Binary type data conversion examples

The following example shows how to create a table named test04:

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

Consider the test04 table and the following examples to understand how to modify the data type and length of a binary data type column.

• 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 set the maximum data size that can be stored to 256 bytes.

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

• Modify the c1 column data type to VARCHAR and specify a maximum length of 256 characters in the test04 table.

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

Conversion between numeric and character data types

The following example creates a table named test05:

obclient> CREATE TABLE test05 (c1 INT);

1. Execute the following statement to modify the data type of the c1 column in the test05 table to VARCHAR with a maximum length of 64 characters:

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

2. Run the following command to change the data type of the c1 column in the test05 table to INT:

   obclient> ALTER TABLE test05 MODIFY c1 INT;
   

Manage default values of columns

OceanBase Database allows you to modify or delete the default value of a column in MySQL-compatible mode. This topic describes how to perform these operations.

Modify the default value of a column

If no default value is specified for a column, its 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 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 a table named tbl1 exists in the database. 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    |       |
+-------+-------------+------+-----+---------+-------+

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. 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 this 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 a table named tbl1 exists in the database. 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  |     | 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. 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

OceanBase Database allows you to add constraints to a table in MySQL-compatible mode, 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 perform these operations.

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 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 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 a table named tbl1 exists in the database. The table structure 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.

   +-------+-------------+------+-----+---------+----------------+
   | 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 a table named tbl1 exists in the database. 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 of tbl1 is set to 12. When you insert a new record, the auto-increment column (for example, c1) starts from 12 and increments by 1.

References

ALTER TABLE