Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      Change Column Type

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

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

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

      Example

      Following are the programs
      PHPNodeJSJavaPython
      $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