MYSQL Modify Column Data Type Script with Checking of Current Column Data Type

Question

I have here a mysql script which alters my 'Job' table and changes the DESCRIPTION column data type to TEXT. However, I have this script together with all the other scripts which are sometimes run multiple times. My question is, what do I need to add to my script so that it would check if the data type of DESCRIPTION column is already TEXT or not? This script takes too long to execute due to huge data and I don't want it to be executed again if the DESCRIPTION column has already been changed to TEXT.

ALTER TABLE Job
MODIFY DESCRIPTION TEXT;

Bhumi Shah · Accepted Answer · 2016-06-09 06:59:46Z

1

SELECT
  COLUMN_NAME, DATA_TYPE 
FROM
  INFORMATION_SCHEMA.COLUMNS 
WHERE
  TABLE_SCHEMA = 'YOUR_DB_NAME'
AND
  TABLE_NAME = 'YOUR_TABLE_NAME'
AND 
 COLUMN_NAME = 'YOUR_COLUMN_NAME';

This will give you datatype of asked column.

Using If condition you can run your alter table command;

edited Jun 9, 2016 at 6:59

Bhumi Shah

9,50210 gold badges69 silver badges110 bronze badges

answered Jun 9, 2016 at 5:59

shahmanthan9

5331 gold badge3 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jbdeguzman Over a year ago

This could work. Though I replaced {TABLE_SCHEMA = 'YOUR_DB_NAME'} to {TABLE_SCHEMA = DATABASE()} to make it work on all our DB copies. I'm trying it out now. Thanks!

jbdeguzman Over a year ago

Thanks for your answer. It helped. :D

shahmanthan9 Over a year ago

Glad that it worked, Please mark it as right if possible :) Thanks

jbdeguzman · Accepted Answer · 2016-06-09 07:55:44Z

Here's what I did. It's a long way but it worked. It needs to be in a stored procedure. Thanks for your help @shahmanthan9. If you guys know a better way please post it here. Thanks!

DROP PROCEDURE IF EXISTS sp_JobUpdateDescriptionColumnType;

CREATE PROCEDURE sp_JobUpdateDescriptionColumnType()
    DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    IF NOT EXISTS( SELECT NULL
       FROM INFORMATION_SCHEMA.COLUMNS
       WHERE TABLE_SCHEMA = DATABASE()
             AND TABLE_NAME = 'Job'
             AND COLUMN_NAME = 'Description'
             AND DATA_TYPE = 'text' )
    THEN
        ALTER TABLE Job
        MODIFY Description TEXT;
    END IF;
END;

CALL sp_JobUpdateDescriptionColumnType;

DROP PROCEDURE IF EXISTS sp_JobUpdateDescriptionColumnType;

Collectives™ on Stack Overflow

MYSQL Modify Column Data Type Script with Checking of Current Column Data Type

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related