How To Change A Datatype Of A Column In Mysql

Summary: in this tutorial, y'all will learn how to use the MySQL ALTER TABLE argument to add a column, alter a column, rename a column, drop a column and rename a table.

Setting up a sample table

Let'southward create a table named vehicles for the sit-in:

CREATE TABLE vehicles ( vehicleId INT, yr INT Not NULL, make VARCHAR(100) NOT NULL, Master Fundamental(vehicleId) );

Code linguistic communication: SQL (Structured Query Language) ( sql )

MySQL `Change TABLE` – Add columns to a table

The ALTER TABLE Add together argument allows y'all to add one or more columns to a table.

1) Add a column to a table

To add a column to a table, you use the ALTER TABLE ADD syntax:

Change Table table_name ADD new_column_name column_definition [FIRST | AFTER column_name]

Lawmaking language: SQL (Structured Query Language) ( sql )

In this syntax:

table_name – specify the proper name of the table that you want to add a new cavalcade or columns after the ALTER Tabular array keywords.
new_column_name – specify the name of the new cavalcade.
column_definition– specify the datatype, maximum size, and column constraint of the new column
Beginning | After column_name specify the position of the new column in the table. You tin can add together a cavalcade after an existing column (ATER column_name) or as the first cavalcade (Outset). If yous omit this clause, the column is appended at the end of the cavalcade listing of the table.

The following example uses the Alter Table ADD statement to add a column at the end of the vehicles tabular array:

Alter Table vehicles ADD model VARCHAR(100) NOT NULL;

Lawmaking language: SQL (Structured Query Language) ( sql )

This statement shows the column list of the vehicles table:

DESCRIBE vehicles;

Code language: SQL (Structured Query Language) ( sql )

As shown clearly from the output, the column model has been added to the vehicles table.

2) Add multiple columns to a table

To add multiple columns to a table, you use the post-obit class of the ALTER TALE Add together statement:

Alter TABLE table_name Add new_column_name column_definition [Commencement | After column_name], ADD new_column_name column_definition [Get-go | AFTER column_name], ...;

Code language: SQL (Structured Query Language) ( sql )

For case, this statement adds two columns colour and note to the vehicles table:

Change Tabular array vehicles ADD colour VARCHAR(fifty), ADD annotation VARCHAR(255);

Code language: SQL (Structured Query Linguistic communication) ( sql )

This statement shows the new construction of the vehicles table:

Describe vehicles;

Code language: SQL (Structured Query Language) ( sql )

MySQL `ALTER Tabular array` – Modify columns

1) Change a cavalcade

Here is the basic syntax for modifying a column in a table:

Change Table table_name MODIFY column_name column_definition [ Beginning | Later column_name];

Code language: SQL (Structured Query Linguistic communication) ( sql )

It's a good practice to view the attributes of a column earlier modifying it.

Suppose that you want to change the notation cavalcade a NOT NULL cavalcade with a maximum of 100 characters.

Showtime, testify the column list of the vehicles tabular array:

Draw vehicles;

Code language: SQL (Structured Query Language) ( sql )

MySQL ALTER TABLE - before modify column

And so, modify the note column:

ALTER Tabular array vehicles Change notation VARCHAR(100) NOT NULL;

Code language: SQL (Structured Query Linguistic communication) ( sql )

Finally, show the column list of the vehicles table to verify the alter:

DESCRIBE vehicles;

Code language: SQL (Structured Query Language) ( sql )

2) Alter multiple columns

The following statement allows you lot to modify multiple columns:

ALTER Tabular array table_name MODIFY column_name column_definition [ Commencement | AFTER column_name], MODIFY column_name column_definition [ Get-go | AFTER column_name], ...;

Code linguistic communication: SQL (Structured Query Language) ( sql )

First, show the electric current columns of the vehicles table:

2nd, use the ALTER TABLE MODIFY statement to modify multiple columns:

ALTER TABLE vehicles Modify year SMALLINT NOT Nothing, MODIFY color VARCHAR(twenty) NULL Afterward make;

Lawmaking language: SQL (Structured Query Language) ( sql )

In this example:

First, modify the data type of the year cavalcade from INT to SMALLINT
Second, modify the color column by setting the maximum length to 20, removing the Non Aught constraint, and changing its position to appear after the make column.

Third, testify the new cavalcade list of the vehicles table to verify the modifications:

MySQL ALTER TABLE - after modify multiple columns

MySQL `Alter Table` – Rename a cavalcade in a table

To rename a cavalcade, you use the post-obit statement:

Modify TABLE table_name Change COLUMN original_name new_name column_definition [FIRST | AFTER column_name];

Code language: SQL (Structured Query Language) ( sql )

In this syntax:

Get-go, specify the name of the table to which the column belongs.
Second, specify the cavalcade name and the new name followed by column definition after the CHANGE Column keywords.
Third, use the Get-go or AFTER column_name option to determine the new position of the column.

The following example uses the ALTER Table CHANGE COLUMN statement to rename the column notation to vehicleCondition:

ALTER Table vehicles CHANGE Cavalcade note vehicleCondition VARCHAR(100) NOT NULL;

Lawmaking language: SQL (Structured Query Linguistic communication) ( sql )

Let's review the cavalcade list of the vehicles table:

DESCRIBE vehicles;

Code language: SQL (Structured Query Linguistic communication) ( sql )

MySQL `Change TABLE` – Drop a column

To drop a column in a table, you utilize the ALTER Table Drib Column statement:

Alter TABLE table_name DROP COLUMN column_name;

Code language: SQL (Structured Query Language) ( sql )

In this syntax:

First, specify the name of the table that you desire to driblet a column afterward the ALTER TABLE keywords.
2d, specify the name of the column that you want to drib later on the DROP Cavalcade keywords.

This example shows how to remove the vehicleCondition column from the vehicles table:

ALTER Table vehicles DROP COLUMN vehicleCondition;

Code language: SQL (Structured Query Language) ( sql )

MySQL `Change Tabular array` – Rename table

To rename a table, yous use the Modify Tabular array RENAME TO statement:

Alter Table table_name RENAME TO new_table_name;

Lawmaking language: SQL (Structured Query Linguistic communication) ( sql )

In this syntax:

Start, specify the name of the table that you desire to rename after the Modify Table keywords.
Second, specify the new name for the table subsequently the RENAME TO keywords.

This instance renames the vehicles table to cars:

ALTER TABLE vehicles RENAME TO cars;

Code language: SQL (Structured Query Linguistic communication) ( sql )

In this tutorial, you lot have learned how to use the MySQL Modify TABLE statement to add together a column, change a column, rename a cavalcade, drib a column and rename a table.

Was this tutorial helpful?

Source: https://www.mysqltutorial.org/mysql-alter-table.aspx

Posted by: bartleytheds1985.blogspot.com

How To Change A Datatype Of A Column In Mysql

Setting up a sample table