How do I change the data type for a column in MySQL?

I want to change the data type of multiple columns from float to int. What is the simplest way to do this?

There is no data to worry about, yet.

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

ALTER TABLE tablename MODIFY columnname INTEGER;

This will change the datatype of given column

Depending on how many columns you wish to modify it might be best to generate a script, or use some kind of mysql client GUI

Method 2

alter table table_name modify column_name int(5)

Method 3

You can also use this:

ALTER TABLE [tablename] CHANGE [columnName] [columnName] DECIMAL (10,2)

Method 4

If you want to change all columns of a certain type to another type, you can generate queries using a query like this:

select distinct concat('alter table ',
                       table_name,
                       ' modify ',
                       column_name,
                       ' <new datatype> ',
                       if(is_nullable = 'NO', ' NOT ', ''),
                       ' NULL;')
  from information_schema.columns
  where table_schema = '<your database>' 
    and column_type = '<old datatype>';

For instance, if you want to change columns from tinyint(4) to bit(1), run it like this:

select distinct concat('alter table ',
                       table_name,
                       ' modify ',
                       column_name,
                       ' bit(1) ',
                       if(is_nullable = 'NO', ' NOT ', ''),
                       ' NULL;')
  from information_schema.columns
  where table_schema = 'MyDatabase' 
    and column_type = 'tinyint(4)';

and get an output like this:

alter table table1 modify finished bit(1)  NOT  NULL;
alter table table2 modify canItBeTrue bit(1)  NOT  NULL;
alter table table3 modify canBeNull bit(1)  NULL;

!! Does not keep unique constraints, but should be easily fixed with another if-parameter to concat. I’ll leave it up to the reader to implement that if needed..

Method 5

Alter TABLE `tableName` MODIFY COLUMN `ColumnName` datatype(length);

Ex :

Alter TABLE `tbl_users` MODIFY COLUMN `dup` VARCHAR(120);

Method 6

To change column data type there are change
method and modify method

ALTER TABLE student_info CHANGE roll_no roll_no VARCHAR(255);

ALTER TABLE student_info MODIFY roll_no VARCHAR(255);

To change the field name also use the change method

ALTER TABLE student_info CHANGE roll_no identity_no VARCHAR(255);

Method 7

You use the alter table ... change ... method, for example:

mysql> create table yar (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into yar values(5);
Query OK, 1 row affected (0.01 sec)

mysql> alter table yar change id id varchar(255);
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc yar;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)

Method 8

https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

You can also set a default value for the column just add the DEFAULT keyword followed by the value.

ALTER TABLE [table_name] MODIFY [column_name] [NEW DATA TYPE] DEFAULT [VALUE];

This is also working for MariaDB (tested version 10.2)

Method 9

If you want to alter the column details, set default value and add a comment, use this

ALTER TABLE [table_name] MODIFY [column_name] [new data type] 
DEFAULT [VALUE] COMMENT '<div class="su-column su-column-size-1-2"><div class="su-column-inner su-u-clearfix su-u-trim"></div></div>'

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating