In MySQL mode of OceanBase Database, you can add a column to the end of a table, add a column to a table at a position other than the end of the table, drop a column, rename a column, rearrange a column, change the data type of a column, set a default value for a column, drop the default value of a column, change the value of an auto-increment column, set a NULL constraint on a column, and set a NOT NULL constraint on a column.
Add a column to the end of a table
The syntax for adding a column to the end of a table is as follows:
ALTER TABLE table_name ADD COLUMN column_name column_definition;
Here is an example:
obclient> CREATE TABLE tbl1 (c1 INT PRIMARY KEY,c2 VARCHAR(50));
Query OK, 0 rows affected
obclient> DESCRIBE tbl1;
+-------+------------+----------+--------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+----------+--------+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
| c2 | varchar(50)| YES | | NULL | |
+-------+------------+----------+--------+---------+-------+
2 rows in set
obclient> ALTER TABLE tbl1 ADD c3 INT;
Query OK, 0 rows affected
obclient> DESCRIBE tbl1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
| c2 | varchar(50) | YES | | NULL | |
| c3 | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set
Add a column to a table at a position other than the end of the table
The syntax for adding a column to a table at a position other than the end of the table is as follows:
ALTER TABLE tbl_name ADD COLUMN new_column_name column_definition
BEFORE column_name;
Here is an example:
obclient> ALTER TABLE tbl1 ADD COLUMN c4 INT BEFORE c3;
Query OK, 0 rows affected
obclient> DESCRIBE tbl1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
| c2 | varchar(50) | YES | | NULL | |
| c4 | int(11) | YES | | NULL | |
| c3 | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set
Drop a column
The syntax for dropping a column is as follows:
ALTER TABLE table_name DROP COLUMN column_name
Here is an example:
obclient> ALTER TABLE tbl1 DROP COLUMN c3;
Query OK, 0 rows affected
obclient> DESCRIBE tbl1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
| c2 | varchar(50) | YES | | NULL | |
| c4 | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set
Rename a column
The syntax for renaming a column is as follows:
ALTER TABLE table_name CHANGE old_col_name new_col_name data_type;
Here is an example:
obclient> DESCRIBE tbl1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
| c2 | varchar(50) | YES | | NULL | |
| c4 | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set
obclient> ALTER TABLE tbl1 CHANGE COLUMN c4 c3 VARCHAR(50);
Query OK, 0 rows affected
obclient> DESCRIBE tbl1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
| c2 | varchar(50) | YES | | NULL | |
| c3 | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set
Rearrange a column
The syntax for rearranging a column is as follows:
ALTER TABLE table_name MODIFY COLUMN column_name column_definition
FIRST | [AFTER column_name];
Here is an example:
obclient> ALTER TABLE tbl1 MODIFY COLUMN c3 INT FIRST;
Query OK, 0 rows affected
obclient> DESCRIBE tbl1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c3 | int(11) | YES | | NULL | |
| c1 | int(11) | NO | PRI | NULL | |
| c2 | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set
obclient> ALTER TABLE tbl1 MODIFY COLUMN c3 INT AFTER c2;
Query OK, 0 rows affected
obclient> DESCRIBE tbl1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
| c2 | varchar(50) | YES | | NULL | |
| c3 | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set
Change the type of a column
OceanBase Database supports the following data type conversions:
- Conversion between the following character data types:
CHAR,VARCHAR,TINYTEXT,TEXT, andLONGTEXT. - Conversion between the following numeric data types:
TINYINT,SMALLINT,MEDIUMINT,INT, andBIGINT. - Conversion between the following binary data types:
BINARY,VARBINARY,BLOB,TINYBLOB,MEDIUMBLOB, andLONGBLOB. - Precision change for the following data types with a precision:
VARCHAR,FLOAT,DOUBLE, andDECIMAL. - Conversion between the following data types with a precision:
FLOAT,DOUBLE, andDECIMAL. - Conversion between the following data types:
INT,VARCHAR,DOUBLE,FLOAT, andDECIMAL.
For more information about the rules for changing the data types of columns in OceanBase Database, see Column type change rules.
The syntax for changing the type of a column is as follows:
ALTER TABLE table_name MODEFY column_name data_type;
Examples of column type changes
Conversions between character data types
Change the data type of a character column and increase the length.
obclient> CREATE TABLE test01 (c1 INT PRIMARY KEY, c2 CHAR(10), c3 CHAR(10));
Query OK, 0 rows affected
obclient> ALTER TABLE test01 MODIFY C2 VARCHAR(20);
Query OK, 0 rows affected
obclient> ALTER TABLE test01 MODIFY C2 VARCHAR(40);
Query OK, 0 rows affected
obclient> ALTER TABLE test01 MODIFY C2 TINYTEXT;
Query OK, 0 rows affected
obclient> ALTER TABLE test01 MODIFY C2 LONGTEXT;
Query OK, 0 rows affected
obclient> ALTER TABLE test01 MODIFY C3 CHAR(20);
Query OK, 0 rows affected
obclient> ALTER TABLE test01 MODIFY C3 VARCHAR(30);
Query OK, 0 rows affected
Conversion between numeric data types
Change the data type of an integer type column and increase the length of the column.
obclient> CREATE TABLE test02 (id INT PRIMARY KEY, name VARCHAR(10),age TINYINT, description VARCHAR(65525)); Query OK, 0 rows affected obclient> ALTER TABLE test02 MODIFY age SMALLINT; Query OK, 0 rows affected obclient> ALTER TABLE test02 MODIFY age INT; Query OK, 0 rows affected obclient> ALTER TABLE test02 MODIFY age BIGGINT; Query OK, 0 rows affectedChange the data type and length for a column of a data type that has a precision.
obclient> CREATE TABLE test03(c1 INT, c2 FLOAT(8,2), c3 FLOAT(8,2), UNIQUE(c2, c3)); Query OK, 0 rows affected obclient> ALTER TABLE test03 MODIFY c2 FLOAT(5,2); Query OK, 0 rows affected obclient> ALTER TABLE test03 MODIFY c2 DOUBLE(10,4); Query OK, 0 rows affected obclient> ALTER TABLE test03 MODIFY c2 DOUBLE(5,2); Query OK, 0 rows affected obclient> ALTER TABLE test03 MODIFY c2 DECIMAL(20, 4); Query OK, 0 rows affected
Conversion for binary data types
obclient> CREATE TABLE test04 (c1 TINYBLOB, c2 BINARY(64));
Query OK, 0 rows affected
obclient> ALTER TABLE test04 MODIFY c1 BLOB;
Query OK, 0 rows affected
obclient> ALTER TABLE test04 MODIFY c1 BINARY(256);
Query OK, 0 rows affected
obclient> CREATE TABLE test05 (id INT PRIMARY KEY, name TINYTEXT,age INT, description VARCHAR(65535));
Query OK, 0 rows affected
obclient> ALTER TABLE test05 MODIFY name VARCHAR(256);
Query OK, 0 rows affected
Conversion between an integer data type and a character data type
obclient> CREATE TABLE test06 (c1 INT);
Query OK, 0 rows affected
obclient> ALTER TABLE test06 MODIFY c1 VARCHAR(64);
Query OK, 0 rows affected
obclient> CREATE TABLE test07 (c1 VARCHAR(32));
Query OK, 0 rows affected
obclient> ALTER TABLE test07 MODIFY c1 INT;
Query OK, 0 rows affected
Set a default value for a column
The syntax for setting a default value for a column is as follows:
ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT literal;
Here is an example:
obclient> ALTER TABLE tbl1 ALTER COLUMN c1 SET DEFAULT 111;
Query OK, 0 rows affected
obclient> DESCRIBE tbl1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c4 | int(11) | YES | | NULL | |
| c1 | int(11) | NO | PRI | 111 | |
| c2 | varchar(50) | YES | | NULL | |
| c3 | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set
Drop the default value of a column
The syntax for dropping the default value of a column is as follows:
ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;
Here is an example:
obclient> ALTER TABLE tbl1 ALTER COLUMN c1 DROP DEFAULT;
Query OK, 0 rows affected
Change the value of an auto-increment column
The syntax for changing the value of an auto-increment column is as follows:
ALTER TABLE table_name AUTO_INCREMENT=next_value;
Here is an example:
obclient> ALTER TABLE tbl1 AUTO_INCREMENT=12;
Query OK, 0 rows affected
Set a NULL constraint on a column
The syntax for setting a NULL constraint on a column is as follows:
ALTER TABLE table_name MODIFY COLUMN column_name data_type NULL;
Here is an example:
obclient> DESCRIBE tbl1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c4 | int(11) | YES | | NULL | |
| c1 | int(11) | NO | PRI | NULL | |
| c2 | varchar(50) | YES | | NULL | |
| c3 | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set
obclient> ALTER TABLE tbl1 MODIFY COLUMN c1 INT NULL;
Query OK, 0 rows affected
obclient> DESCRIBE tbl1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c4 | int(11) | YES | | NULL | |
| c1 | int(11) | NO | PRI | NULL | |
| c2 | varchar(50) | YES | | NULL | |
| c3 | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set
Set a NOT NULL constraint on a column
The syntax for setting a NOT NULL constraint on a column is as follows:
ALTER TABLE table_name MODIFY COLUMN column_name data_type NOT NULL;
Here is an example:
obclient> ALTER TABLE tbl1 MODIFY COLUMN c1 INT NOT NULL;
Query OK, 0 rows affected
obclient> DESCRIBE tbl1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c4 | int(11) | YES | | NULL | |
| c1 | int(11) | NO | PRI | NULL | |
| c2 | varchar(50) | YES | | NULL | |
| c3 | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set