About table structure modification|V3.2.4|OceanBase Database| docs|Distributed Database

About table structure modification

Last Updated：2023-10-24 09:23:03 Updated

This topic describes how to rename columns and tables, change column types, add columns, and drop columns in OceanBase Database in MySQL mode.

Prerequisites

The current user has the ALTER privilege for the target table.

Rename a column

Syntax

 ALTER TABLE table_name CHANGE[COLUMN] column_name new_column_name new_column_name_definition;

Example

Rename the name1 column in the dws_ny table to name.

View the structure of the dws_ny table.

obclient> desc dws_ny;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   |     | NULL    | auto_increment |
| name1    | varchar(100)     | NO   |     | NULL    |                |
| describe | varchar(256)     | NO   |     | NULL    |                |
| date     | date             | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Change name1 to name.

 ALTER TABLE dws_ny CHANGE name1  name VARCHAR(100);

After the statement is executed, the name1 column in the dws_ny table changes to name.

obclient> desc dws_ny;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   |     | NULL    | auto_increment |
| name     | varchar(100)     | YES  |     | NULL    |                |
| describe | varchar(256)     | NO   |     | NULL    |                |
| date     | date             | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Change the type of a column

Syntax

ALTER TABLE table_name MODIFY[COLUMN] new_column_name_type;

Example

Change the type of the name column from VARCHAR(100) to VARCHAR(128).

View the structure of the dws_ny table.

obclient> desc dws_ny;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   |     | NULL    | auto_increment |
| name1    | varchar(100)     | NO   |     | NULL    |                |
| describe | varchar(256)     | NO   |     | NULL    |                |
| date     | decimal(10,0)    | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Change the type of the name column from VARCHAR(100) to VARCHAR(128).
```
ALTER TABLE dws_ny MODIFY name VARCHAR(128);
```

After the statement is executed, the type of the name column changes from VARCHAR(100) to VARCHAR(128).

obclient> desc dws_ny;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   |     | NULL    | auto_increment |
| name     | varchar(128)     | YES  |     | NULL    |                |
| describe | varchar(256)     | NO   |     | NULL    |                |
| date     | date             | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Note

When you change the type of a column, you can only increase the column length for specific character data types, such as VARCHAR, VARBINARY, and CHAR.

Add a column

Syntax

ALTER TABLE table_name ADD[COLUMN] column_name column_type;

Example

Add the AGE column to the dws_ny table.

View the structure of the dws_ny table.

obclient> desc dws_ny;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   |     | NULL    | auto_increment |
| name1    | varchar(100)     | NO   |     | NULL    |                |
| describe | varchar(256)     | NO   |     | NULL    |                |
| date     | decimal(10,0)    | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Add the AGE column.

obclient> ALTER TABLE dws_ny ADD AGE BIGINT;
Query OK, 0 rows affected (0.02 sec)

After the statement is executed, you can view the AGE column in the structure of the dws_ny table.

obclient> desc dws_ny;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   |     | NULL    | auto_increment |
| name     | varchar(128)     | YES  |     | NULL    |                |
| describe | varchar(256)     | NO   |     | NULL    |                |
| date     | date             | YES  |     | NULL    |                |
| AGE      | bigint(20)       | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

Drop a column

Syntax

ALTER TABLE table_name DROP[COLUMN] column_name;

Example

Drop the date column from the dws_ny table.

View the structure of the dws_ny table.

obclient> desc dws_ny;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   |     | NULL    | auto_increment |
| name     | varchar(128)     | YES  |     | NULL    |                |
| describe | varchar(256)     | NO   |     | NULL    |                |
| date     | date             | YES  |     | NULL    |                |
| AGE      | bigint(20)       | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

Drop the date column.

obclient> alter table dws_ny drop date;
Query OK, 0 rows affected (0.03 sec)

After the statement is executed, the date column disappears from the structure of the dws_ny table.

obclient> desc dws_ny;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   |     | NULL    | auto_increment |
| name     | varchar(128)     | NO   |     | NULL    |                |
| describe | varchar(256)     | NO   |     | NULL    |                |
| AGE      | bigint(20)       | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Rename a table

Method 1

Syntax:

ALTER TABLE table_name RENAME new_table_name;

Example: Rename the dws_ny table to dws_ny1.

Show the dws_ny table.

obclient> show tables;
+--------------+
| Tables_in_ny |
+--------------+
| dws_ny       |
+--------------+
1 row in set (0.01 sec)

Rename the dws_ny table to dws_ny1.

obclient> ALTER TABLE DWS_NY RENAME DWS_NY1;
Query OK, 0 rows affected (0.03 sec)

View the results.

obclient> show tables;
+--------------+
| Tables_in_ny |
+--------------+
| dws_ny1      |
+--------------+
1 row in set (0.01 sec)

Method 2

Syntax:

RENAME TABLE table_name TO new_table_name;

Example: Rename the dws_ny1 table to dws_ryxx_ny.

Show the dws_ny1 table.

obclient> show tables;
+--------------+
| Tables_in_ny |
+--------------+
| dws_ny1      |
+--------------+
1 row in set (0.01 sec)

Rename the dws_ny1 table to dws_ryxx_ny.

obclient> RENAME TABLE DWS_NY1 TO DWS_RYXX_NY;
Query OK, 0 rows affected (0.01 sec)

View the results.

obclient> show tables;
+--------------+
| Tables_in_ny |
+--------------+
| dws_ryxx_ny  |
+--------------+
1 row in set (0.00 sec)

Parameters

Parameter	Description
ADD [COLUMN]	Adds a column. You can add a generated column. Note At present, you are not allowed to add a primary key column.
CHANGE [COLUMN]	Renames a column or modifies the column definition. When you change the type of a column, you can only increase the column length for specific character data types, such as `VARCHAR`, `VARBINARY`, and `CHAR`.
MODIFY [COLUMN]	Modifies the column definition. When you change the type of a column, you can only increase the column length for specific character data types, such as `VARCHAR`, `VARBINARY`, and `CHAR`.
DROP [COLUMN]	Drops a column. You cannot drop a primary key column, non-empty columns, or columns that contain indexes.
ALTER INDEX	Modifies whether an index is visible. When the status of an index is `INVISIBLE`, the SQL optimizer will not select this index.
RENAME [TO] table_name	Renames a table.