MySQL – Change Column Type


MySQL – Change Column Type


Table of content


”;


Previous

Next

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 −



Field Type Null Key Default Extra
field1 int NO PRI NULL
field2 varchar(100) YES

NULL
field3 date YES

NULL

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 −



Field Type Null Key Default Extra
field1 int NO PRI NULL
field2 text YES

NULL
field3 date YES

NULL

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 −



Field Type Null Key Default Extra
field1 int NO PRI NULL
field2 text YES

NULL
field3 date YES

NULL

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 −



Field Type Null Key Default Extra
field1 int NO PRI NULL
field2 text YES

NULL
field3 varchar(20) YES

NULL




Changing Column type Using Client Program

We can also change column type using client program.

Syntax


PHP
NodeJS
Java
Python

To change column type through a PHP program, we need to execute the “ALTER TABLE” statement using the mysqli function query() as follows −


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

To change column type through a JavaScript program, we need to execute the “ALTER TABLE” statement using the query() function of mysql2 library as follows −


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

To change column type through a Java program, we need to execute the “ALTER TABLE” statement using the JDBC function executeUpdate() as follows −


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

To change column type through a Python program, we need to execute the “ALTER TABLE” statement using the execute() function of the MySQL Connector/Python as follows −


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

Example

Following are the programs −


PHP
NodeJS
Java
Python 


$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();

Output

The output obtained is as shown below −


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);
    });
});

Output

The output obtained is as shown below −

 
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();
      }
   }
}

Output

The output obtained is as shown below −


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()

Output

The output obtained is as shown below −


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, '''')

Leave a Reply

Your email address will not be published. Required fields are marked *