Column operations

In MySQL mode of OceanBase Database, you can perform the following column operations: add a column to the end of a table, add a column to a table at a specified position, drop a column, rename a column, reorder columns, change 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 the 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

The syntax is as follows:

ALTER TABLE table_name ADD COLUMN column_name data_type;

The following table describes the parameters of 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. This example shows how to add a column named c1 to the end of the tbl1 table.

1. View the table structure 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 column is INT.

   obclient> ALTER TABLE tbl1 ADD COLUMN c1 INT;
   

   3. View the table structure 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 specified position in a table

The syntax is as follows:

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

The following table describes the parameters of 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 where you want to add the column. Valid values: FIRST, BEFORE, and AFTER. FIRST indicates that you want to add the column to the beginning of the table. BEFORE indicates that you want to add the column before the specified column. AFTER indicates that you want to add the column after the specified column. |

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

This 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 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 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 column is VARCHAR, and the column cannot be empty.

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

4. View the table structure of the tbl1 table again.

   obclient> DESCRIBE tbl1;
   

   The output shows that the c1, c2, and c3 columns have been added to the tbl1 table.

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

The syntax is as follows:

ALTER TABLE table_name DROP COLUMN column_name

In the syntax, table_name specifies the name of the table to 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 table structure 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    |       |
+-------+-------------+------+-----+---------+-------+

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

obclient> ALTER TABLE tbl1 DROP COLUMN name;

After the column is dropped, run the DESCRIBE tbl1; statement to view the table structure 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 to which you want to drop columns, and column_name specifies the name of each column to be dropped. Assume that a table named tbl1 exists in the database. The table structure 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  |
+-------+--------------+------+------+---------+-------+

This 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;

Run the DESCRIBE tbl1; statement to view the table structure 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

The syntax is as follows:

ALTER TABLE TABLE_NAME FORCE;

This 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 rename a column in the following 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 of this procedure.

  • table_name: Name of 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 after renaming.

  • data_type: Specify the data type of the column to be renamed. You can specify the same data type as the current one, or change it to another type. For more information about the data types that can be changed, see Modify the data type of a column.

• Rename only columns.

  ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name
  

  The following table describes the parameters of the procedure.

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

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

  • new_col_name: the new column name.

Assume that the database contains a table named tbl1, whose 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 examples show how to rename columns. For example, change the name of the name column to c2, the age column to c3, and 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 name of the age column to c3, and change the data type of the age column to VARCHAR.

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

3. Run the DESCRIBE tbl1; statement to view the table structure of tbl1 again.

   The following output is returned. Only three columns, id, c2, and c3, are returned from the tbl1 table. The c3 column has the VARCHAR data type.

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

Reorganization

The syntax for the REORG procedure is as follows:

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

The following table describes the related parameters.

• table_name: specifies the name of the table for which the statistics are to be recomputed.

• column_name: Specifies the name of the column that is to be reordered.

• data_type: The data type of the column to be altered, which can be the existing data type or a new data type. For more information about how to modify the column data type, see Modify the column type.

• FIRST | BEFORE | AFTER: specifies the position where the column is to be added. The column can be added at the beginning of the table (FIRST), before the column_name column (BEFORE), or after the column_name column (AFTER).

  Notice

  If you use the FIRST option when adding a column to a table, you need not specify a value for column_name; otherwise, an error will be returned.

• column_name specifies the column to be moved to the specified location.

Assume that there is a table named tbl1 in the database, and the schema of tbl1 is described 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 shows how to use the reorder statement to move 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 again to view the table structure. The output shows that the age column appears before the name column, and the data type 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 types

The following table lists column type conversion options supported by OceanBase Database:

• Conversion of character type columns. Supported types: CHAR, VARCHAR, TINYTEXT, TEXT, and LONGTEXT.

• Conversion of numeric type columns. Supported types: TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT.

• Conversion of binary type data. Supported types: BINARY, VARBINARY, BLOB, TINYBLOB, MEDIUMBLOB, and LONGBLOB.

• Precision-modifiable data types. Supported types: VARCHAR, FLOAT, DOUBLE, and DECIMAL.

• Conversion between precision-modifiable data types. Supported types: FLOAT, DOUBLE, and DECIMAL.

• Conversion between different data types. Supported types: INT, VARCHAR, DOUBLE, FLOAT, and DECIMAL.

For more information about column type conversion rules of OceanBase Database, see Column type change rules.

The syntax to change a column type is as follows:

ALTER TABLE table_name MODIFY [COLUMN] column_name data_type;

The parameters are described as follows:

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

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

• data_type: the new data type.

Examples of column type conversion between character data types

The following statements create the table test01:

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

Here is an example of how to modify the data types of the c2 and c3 columns in the test01 table:

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

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

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

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

• Change the data type of the c2 column 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;
  

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

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

• Change the data type of the c3 column in the test01 table to VARCHAR with a maximum length of 30 characters.

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

Examples of column type conversion between integer data types

Integer types

The following statements create the table test02:

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

Here is an example of how to modify the data type of the age column in the test02 table:

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

  obclient> ALTER TABLE test02 MODIFY age SMALLINT;
  

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

  obclient> ALTER TABLE test02 MODIFY age INT;
  

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

  obclient> ALTER TABLE test02 MODIFY age BIGGINT;
  

Floating point data types

The following statements create the table test03:

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

Here is an example of how to modify the data type and precision of the c2 column in the test03 table:

• Change the number of digits that the c2 column in the test03 table can store to 5.

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

• Change the data type of the c2 column in the test03 table to DOUBLE. Set the total number of digits that can be stored to 10, including 4 decimal places.

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

• Change the data type of the c2 column in the test03 table to DOUBLE. Set the number of digits that can be stored to 5, including 2 decimal places.

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

• Change the data type of the c2 column in the test03 table to DECIMAL. Set the total number of digits that can be stored to 20, including 4 decimal places.

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

Examples of column type conversion between binary data types

The following statements create the table test04:

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

Here is an example of how to modify the data type and precision of the c1 column 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 set the maximum storage size to 256 bytes.

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

• Change the data type of the c1 column in the test04 table to VARCHAR and set the maximum storage size to 256 characters.

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

Examples of conversion between integer data types and character data types

The following statements create the table test05:

obclient> CREATE TABLE test05 (c1 INT);

1. Change the data type of the c1 column in the test05 table to VARCHAR and set the maximum storage size to 64 characters.

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

2. Change the data type of the c1 column in the test05 table to INT.

   obclient> ALTER TABLE test05 MODIFY c1 INT;
   

Manage default values

In the MySQL mode of OceanBase Database, you can modify or delete the default value of a column. 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. To modify the default value of a column, use the following syntax:

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT const_value;

The following table describes the parameters in the syntax.

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

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

• const_value: specifies 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

To delete the default value of a column, use the following syntax:

ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;

In the 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

In the 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 syntax.

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

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

• data_type: specifies 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

To modify the value of an auto-increment column, use the following syntax:

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 the tbl1 table to 12.

obclient> ALTER TABLE tbl1 AUTO_INCREMENT = 12;

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

References

ALTER TABLE