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 the default value of 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
Syntax:
ALTER TABLE table_name ADD COLUMN column_name column_definition;
Sample code:
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
Syntax:
ALTER TABLE tbl_name ADD COLUMN new_column_name column_definition
BEFORE column_name;
Sample code:
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
Syntax:
ALTER TABLE table_name DROP COLUMN column_name
Sample code:
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
Syntax:
ALTER TABLE table_name CHANGE old_col_name new_col_name data_type;
Sample code:
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
Syntax:
ALTER TABLE table_name MODIFY COLUMN column_name column_definition
FIRST | [AFTER column_name];
Sample code:
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:
- Character data types that can be converted from and to each other:
CHAR,VARCHAR,TINYTEXT,TEXT, andLONGTEXT - Numeric data types that can be converted from and to each other:
TINYINT,SMALLINT,MEDIUMINT,INT, andBIGINT - Binary data types that can be converted from and to each other:
BINARY,VARBINARY,BLOB,TINYBLOB,MEDIUMBLOB, andLONGBLOB - Data types with a precision that support change of the precision:
VARCHAR,FLOAT,DOUBLE, andDECIMAL - Data types with a precision that can be converted from and to each other:
FLOAT,DOUBLE, andDECIMAL - Conversion between different 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.
Syntax:
ALTER TABLE table_name MODEFY column_name data_type;
Examples
Conversion between character data types
Change the data type of a character type column and increase the length of the column.
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 affected
- Change 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 between 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 integer and character data types
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
Syntax:
ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT literal;
Sample code:
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
Syntax:
ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;
Sample code:
obclient> ALTER TABLE tbl1 ALTER COLUMN c1 DROP DEFAULT;
Query OK, 0 rows affected
Change the value of an auto-increment column
Syntax:
ALTER TABLE table_name AUTO_INCREMENT=next_value;
Sample code:
obclient> ALTER TABLE tbl1 AUTO_INCREMENT=12;
Query OK, 0 rows affected
Set a NULL constraint on a column
Syntax:
ALTER TABLE table_name MODIFY COLUMN column_name data_type NULL;
``Sample code:
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
Syntax:
ALTER TABLE table_name MODIFY COLUMN column_name data_type NOT NULL;
``Sample code:
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