Column operations

In MySQL mode of OceanBase Database, you can add columns to the end of a table, add columns to a table at a position other than the end, delete columns, rename columns, rearrange columns, modify column types, manage default values for columns, manage constraints, and modify the values of auto-increment columns.

Add a column

In the MySQL mode of OceanBase Database, you can add a column to a table at the end of the table or in the middle of the table. This topic describes how to add a column to a table at the end of 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 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. The following example shows how to add a column named c1 to the end of the tbl1 table.

1. View the table structure of tbl1.

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

   obclient> DESCRIBE tbl1;
   

The output shows that a new column named c1 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 in the middle of a table

To add a column in the middle of 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 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 the column is added to the beginning of the table. BEFORE indicates that the column is added before the specified column. AFTER indicates that the column is added 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 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 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 of 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 of 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 tbl1.

   obclient> DESCRIBE tbl1;
   

   The output shows that the tbl1 table contains the c1, c2, and c3 columns.

   +-------+-------------+------+-----+---------+-------+
   | 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 to which you want to add 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 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 drop the name column from the tbl1 table.

obclient> ALTER TABLE tbl1 DROP COLUMN name;

After you drop the name column, 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

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 to which you want to add 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 tbl1 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.

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

After you drop the C4 and C5 columns, 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

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 column

There are two syntax formats for renaming columns:

• Rename a column and change the data type of the column.

  ALTER TABLE table_name CHANGE old_col_name new_col_name data_type;
  

  The description of the parameters is as follows:

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

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

  • new_col_name: the new column name of the renamed column.

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

• RENAME COLUMN only

  ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name
  

  The following describes the parameters of the DELETE_SCHEMA_STATS procedure:

  • 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 name of the column after it is renamed.

Assume that a table named tbl1 exists in the database, and the schema of the 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 use the RENAME_COLUMN function to change the column name name to c2 and the column name age to c3, and change the data type of age 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 change the data type of the age column to VARCHAR.

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

3. Run the DESCRIBE tbl1; command again to view the schema of the tbl1 table.

   Output is as follows. Only the id, c2, and c3 columns exist in the tbl1 table, 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    |       |
   +-------+-------------+------+-----+---------+-------+
   

Rearranged

The syntax is as follows:

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

Parameters are described in the following topics:

• table_name: The name of the table to be reordered.

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

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

• FIRST | BEFORE | AFTER : Specifies the position at which to add the column. You can add the column to the beginning of the table (FIRST), before the column specified by column_name (BEFORE), or after the column specified by column_name (AFTER).

  Note

  When you use the FIRST clause to add a column to the beginning of a table, do not specify a value for the column_name parameter. Otherwise, an error is returned.

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

Assume that the database contains a table named tbl1 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    |       |
+-------+-------------+------+-----+---------+-------+

This section provides an example of how to use the ALTER COLUMN statement to rearrange the age column before the name column and to modify the data type of the age column to VARCHAR.

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

Execute the DESCRIBE tbl1; command again to view the table structure of the tbl1 table. The output is as follows: the age column is placed 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    |       |
+-------+-------------+------+-----+---------+-------+

Convert a column type

The column type conversion supported by OceanBase Database is described as follows:

• Conversion of the data type of a character data type column, including CHAR, VARCHAR, TINYTEXT, TEXT, and LONGTEXT.

• Conversion of the data type of a numeric data type column, including TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT.

• Conversion of the data type of a binary data type column, including BINARY, VARBINARY, BLOB, TINYBLOB, MEDIUMBLOB, and LONGBLOB.

• Conversion of the precision of a data type with precision, including VARCHAR, FLOAT, DOUBLE, and DECIMAL.

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

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

For more information about the rules of column type conversion, see Column type conversion rules.

The syntax of converting 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 in which the column to be modified is located.

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

• data_type: the data type after modification.

Convert a column of a character data type

Create the test01 table as follows:

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

Assume that you want to convert the data type of a column in the test01 table. For example, you can convert a CHAR column to a VARCHAR column and set the length of the VARCHAR column to 20.

• Convert the data type of the c2 column in the test01 table to VARCHAR, and set the length of the VARCHAR column to 20.

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

• Convert the data type of the c2 column in the test01 table to VARCHAR, and set the length of the VARCHAR column to 40.

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

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

  obclient> ALTER TABLE test01 MODIFY c2 TINYTEXT;
  

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

  obclient> ALTER TABLE test01 MODIFY c2 LONGTEXT;
  

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

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

• Convert the data type of the c3 column in the test01 table to VARCHAR, and set the length of the VARCHAR column to 30.

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

Convert a column of an integer data type

Integer types

Create the test02 table as follows:

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

Assume that you want to convert the data type of a column in the test02 table. For example, you can convert a TINYINT column to an INT column.

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

  obclient> ALTER TABLE test02 MODIFY age SMALLINT;
  

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

  obclient> ALTER TABLE test02 MODIFY age INT;
  

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

  obclient> ALTER TABLE test02 MODIFY age BIGGINT;
  

Numeric types with precision

Create the test03 table as follows:

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

Assume that you want to convert the data type of a column in the test03 table. For example, you can convert a DECIMAL column to a DOUBLE column and set the precision of the DOUBLE column.

• Set the number of digits that can be stored in the c2 column in the test03 table to 5.

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

• Convert the data type of the c2 column in the test03 table to DOUBLE. Set the precision of the DOUBLE column to 10, which includes four digits after the decimal point.

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

• Convert the data type of the c2 column in the test03 table to DOUBLE. Set the number of digits that can be stored in the DOUBLE column to 5, which includes two digits after the decimal point.

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

• Convert the data type of the c2 column in the test03 table to DECIMAL. Set the precision of the DECIMAL column to 20, which includes four digits after the decimal point.

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

Convert a column of a binary data type

Create the test04 table as follows:

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

Assume that you want to convert the data type of a column in the test04 table. For example, you can convert a BLOB column to a BINARY column.

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

  obclient> ALTER TABLE test04 MODIFY c1 BLOB;
  

• Convert the data type of the c1 column in the test04 table to BINARY. Set the length of the BINARY column to 256 bytes.

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

• Convert the data type of the c1 column in the test04 table to VARCHAR. Set the length of the VARCHAR column to 256.

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

Convert a column between an integer data type and a character data type

Create the test05 table as follows:

obclient> CREATE TABLE test05 (c1 INT);

1. Execute the following statement to convert the data type of the c1 column in the test05 table to VARCHAR. Set the length of the VARCHAR column to 64.

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

2. Execute the following statement to convert 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

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

Modify the default value of a column

By default, the default value of a column 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 the database contains a table named tbl1. The schema 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 schema 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 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 schema 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 schema 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 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: 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 data type of the column to another type. For more information about the supported data types, see Modify the data type of a column.

• 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 schema 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 schema 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 of tbl1 is set to 12. When a new record is inserted, the auto-increment column (for example, c1) starts from 12 and increments by 1.

References

ALTER TABLE