Bootstrap FreeKB - mySQL / MariaDB - Change the size of a column in a table using the alert table command
mySQL / MariaDB - Change the size of a column in a table using the alert table command

Updated:   |  mySQL / MariaDB articles

The alter table command can be used to change the size of a column in a table. You will first need to log into your MariaDB or mySQL server or configure passwordless authentication and then use the -e command line option and the user must have the Alter_priv.

You will probably want to show the columns in the table that you want to add a column to. The show columns or describe <tablename> commands can be used to list the columns in the table. In this example, the columns in table001 in the database named db001 will be described.

use db001; describe table001;

 

Or like this.

describe db001.table001;

 

Something like this should be returned. Notice in this example that column001 type is varchar(200).

+-----------------+---------------+------+-----+---------+----------------+
| Field           | Type          | Null | Key | Default | Extra          |
+-----------------+---------------+------+-----+---------+----------------+
| id              | int(11)       | NO   | PRI | NULL    | auto_increment |
| column001       | varchar(200)  | NO   |     | NULL    |                |
| column002       | varchar(1000) | NO   |     | NULL    |                |
| column003       | varchar(2000) | NO   |     | NULL    |                |
+-----------------+---------------+------+-----+---------+----------------+

 

Update the column type. In this example, column001 is updated to varchar(250).

AVOID TROUBLE

Notice in this example that the "id" column has the PRI (PRIMARY KEY) set and auto_increment. Each table can have only one column set as the PRIMARY KEY. For this reason, the new column cannot have the PRIMARY KEY set (nor would it make any sense for the new column to have the PRIMARY KEY set). Refer to Understanding PRIMARY KEY and auto_increment.

alter table db001.table001 modify column001 varchar(250) not null;

 

And here is an example for a char column.

alter table db001.table001 modify column002 char(50);

 

Then show the column again to validate that the column was updated.

describe db001.table001;

 

Something like this should be returned.

+-----------------+---------------+------+-----+---------+----------------+
| Field           | Type          | Null | Key | Default | Extra          |
+-----------------+---------------+------+-----+---------+----------------+
| id              | int(11)       | NO   | PRI | NULL    | auto_increment |
| column001       | varchar(250)  | NO   |     | NULL    |                |
| column002       | varchar(1000) | NO   |     | NULL    |                |
| column003       | varchar(2000) | NO   |     | NULL    |                |
+-----------------+---------------+------+-----+---------+----------------+



Did you find this article helpful?

If so, consider buying me a coffee over at Buy Me A Coffee



Comments


Add a Comment


Please enter 3d7d0a in the box below so that we can be sure you are a human.