MySQL - Change Column Type

In MySQL, users have the flexibility to modify the data type of a field. This is useful when you initially set the wrong data type for a column in a new database table, or when you need to adjust the storage size for the values in a particular column.

You can modify or update the column type in a MySQL table, using the ALTER TABLE command.

The MySQL ALTER TABLE Command

The ALTER TABLE command is used to modify any data related to a database table. This data can either be records in the table or the table definition itself. Changing the data type of a column is one such modification that can be performed using the ALTER TABLE command.

There are two methods to change a column's data type: the ALTER TABLE... MODIFY command and the ALTER TABLE... CHANGE command.

The MySQL ALTER TABLE... MODIFY Command

You can use MODIFY method with the ALTER TABLE statement to change the column/field data type.

Syntax

Following is the basic syntax to change the column type using ALTER TABLE... MODIFY command −

ALTER TABLE table_name MODIFY column_name new_datatype;

Example

Suppose you have created a table named 'test_table' using the following query −

CREATE TABLE test_table (
   field1 INT,
   field2 VARCHAR(100),
   field3 DATE,
   PRIMARY KEY(field1)
);

To check if the table is successfully created or not, use the DESC command as shown below −

DESC test_table;

This will display the table structure as follows −

Now, let us say you want to change the data type of 'field2' from VARCHAR(100) to TEXT for more simplicity. You can do this using the ALTER TABLE... MODIFY query as follows −

ALTER TABLE test_table MODIFY field2 TEXT;

Following is the output obtained −

Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

To check if the column data type is successfully changed, use the DESC command as shown below −

DESC test_table;

This will display the modified table structure as follows −

The MySQL ALTER TABLE... CHANGE Command

You can also use the CHANGE method with the ALTER TABLE statement to modify a column's data type. This method is used to alter all the data related to table after it is created.

The only difference between CHANGE and MODIFY methods is that the MODIFY method cannot rename a column whereas the CHANGE method can.

Syntax

Following is the basic syntax to change the column type using ALTER TABLE... CHANGE command −

ALTER TABLE table_name CHANGE column_name column_name new_datatype;

You must remember to specify the column name twice in the query whenever the CHANGE method is used.

Example

In this example, we are changing the datatype of 'field3' in the 'test_table' using the CHANGE method in ALTER TABLE command. Firstly, let us observe current definition of the table using DESC command −

DESC test_table;

Following is the table produced −

Now, using ALTER TABLE... CHANGE method, change the column type of 'field3' −

ALTER TABLE test_table CHANGE field3 field3 VARCHAR(20);

Output of the above code is as follows −

Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

You can verify whether the column data type is changed using the DESC command as shown below −

DESC test_table;

We get the following table as an output −

Changing Column type Using Client Program

We can also change column type using client program.

Syntax

$sql = "ALTER TABLE test_table MODIFY field2 TEXT";
$mysqli->query($sql);

sql = "ALTER TABLE test_table MODIFY field2 TEXT";
con.query(sql)

String sql = "ALTER TABLE test_table MODIFY field2 TEXT";
statement.executeUpdate(sql);

change_column_type_query = "ALTER TABLE test_table MODIFY field2 TEXT"
cursorObj.execute(change_column_type_query)

Example

Following are the programs −

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// Create table Myplayer    
$sql = 'CREATE TABLE IF NOT EXISTS test_table (field1 INT, field2 VARCHAR(100), field3 DATE, PRIMARY KEY(field1) )';
$result = $mysqli->query($sql);
if ($result) {
    echo "Table created successfully...!\n";
}
echo "Describe table before column modification...!\n";
$q = "DESC test_table";
if ($res = $mysqli->query($q)) {
    while ($row = $res->fetch_array()) {
        print_r($row[1]);
        printf("\n");
    }
}
//modifie table column...!
$sql = "ALTER TABLE test_table MODIFY field2 TEXT";
if ($mysqli->query($sql)) {
    echo "Table's column type modification done...!\n";
}
echo "Describe table after modification of field2...!\n";
$sql = "DESC test_table";
if ($res = $mysqli->query($sql)) {
    while ($row = $res->fetch_array()) {
        print_r($row[1]);
        printf("\n");
    }
}
$mysqli->close();

Table created successfully...!
Describe table before column modification...!
int
varchar(100)
date
Table's column type modification done...!
Describe table after modification of field2...!
int
text
date        

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

// Connecting to MySQL
con.connect(function (err) {
    if (err) throw err;
    console.log("Connected!");
    console.log("--------------------------");

    // Create a new database
    sql = "Create Database TUTORIALS";
    con.query(sql);

    sql = "USE TUTORIALS";
    con.query(sql);

    sql = "CREATE TABLE test_table (field1 INT,field2 VARCHAR(100),field3 DATE,PRIMARY KEY(field1));"
    con.query(sql);

    sql = "DESC test_table;"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log("**Description of test_table:**");
      console.log(result);
    });

    sql = "ALTER TABLE test_table MODIFY field2 TEXT;"
    con.query(sql);

    sql = "DESC test_table;"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log("**Description of test_table after modifying:**");
      console.log(result);
    });
});    

 
Connected!
--------------------------
**Description of test_table:**
[
  {
    Field: 'field1',
    Type: 'int',
    Null: 'NO',
    Key: 'PRI',
    Default: null,
    Extra: ''
  },
  {
    Field: 'field2',
    Type: 'varchar(100)',
    Null: 'YES',
    Key: '',
    Default: null,
    Extra: ''
  },
  {
    Field: 'field3',
    Type: 'date',
    Null: 'YES',
    Key: '',
    Default: null,
    Extra: ''
  }
]
**Description of test_table after modifying:**
[
  {
    Field: 'field1',
    Type: 'int',
    Null: 'NO',
    Key: 'PRI',
    Default: null,
    Extra: ''
  },
  {
    Field: 'field2',
    Type: 'text',
    Null: 'YES',
    Key: '',
    Default: null,
    Extra: ''
  },
  {
    Field: 'field3',
    Type: 'date',
    Null: 'YES',
    Key: '',
    Default: null,
    Extra: ''
  }
]    

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class ChangeColumnType {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //create a table...
         statement.execute("CREATE TABLE IF NOT EXISTS test_table (field1 INT, field2 VARCHAR(100), field3 DATE, PRIMARY KEY(field1))");
         System.out.println("Table created successfully...!");

         System.out.println("Describe table before column modification...!");
         ResultSet resultSet = statement.executeQuery("DESC test_table");
         while (resultSet.next()){
            System.out.println(resultSet.getNString(1)+" "+resultSet.getNString(2)+" "+resultSet.getNString(3)+" "+resultSet.getNString(4));
         }

         //Updating the table's column...!
         statement.executeUpdate("ALTER TABLE test_table MODIFY field2 TEXT");
         System.out.println("Table column updated successfully...!");

         System.out.println("Describe table After column modification...!");
         ResultSet resultSet1 = statement.executeQuery("DESC test_table");
         while (resultSet1.next()){
            System.out.println(resultSet1.getNString(1)+" "+resultSet1.getNString(2)+" "+resultSet1.getNString(3)+" "+resultSet1.getNString(4));
         }
         connection.close();
      } catch (Exception e) {
         e.printStackTrace();
      }
   }
}    

Connected successfully...!
Table created successfully...!
Describe table before column modification...!
field1 int NO PRI
field2 text YES 
field3 date YES 
Table column updated successfully...!
Describe table After column modification...!
field1 int NO PRI
field2 text YES 
field3 date YES       

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# Creating a cursor object
cursorObj = connection.cursor()
# Creating the table 'test_table'
create_table_query = '''
CREATE TABLE test_table (
    field1 INT,
    field2 VARCHAR(100),
    field3 DATE,
    PRIMARY KEY(field1)
)'''
cursorObj.execute(create_table_query)
print("Table 'test_table' is created successfully!")
# Checking whether the table is created
desc_query = "DESC test_table;"
cursorObj.execute(desc_query)
results = cursorObj.fetchall()
print("\nTable structure for 'test_table':")
for result in results:
    print(result)
# Changing column type
change_column_type_query = "ALTER TABLE test_table MODIFY field2 TEXT"
cursorObj.execute(change_column_type_query)
print("\nColumn 'field2' type has been changed to TEXT.")
# Verification
desc = "DESC test_table;"
cursorObj.execute(desc)
results = cursorObj.fetchall()
print("\nTable structure for 'test_table':")
for result in results:
    print(result)
# Closing the cursor and connectimon
cursorObj.close()
connection.close()    

Table 'test_table' is created successfully!
Table structure for 'test_table':
('field1', b'int', 'NO', 'PRI', None, '')
('field2', b'varchar(100)', 'YES', '', None, '')
('field3', b'date', 'YES', '', None, '')

Column 'field2' type has been changed to TEXT.

Table structure for 'test_table':
('field1', b'int', 'NO', 'PRI', None, '')
('field2', b'text', 'YES', '', None, '')
('field3', b'date', 'YES', '', None, '')