changing datatype in the entire database

Question

By mistake I have created my tables with data type int(200), varchar(200), double(200,2) in MySQL. Now I want to change it to int(20), varchar(20), double(20,2). Since the database is very big changing them one by one is a very very difficult task. I am not sure but I think following SQL can do it-

UPDATE INFORMATION_SCHEMA.COLUMNS
SET COLUMN_TYPE='INT(20)'
WHERE COLUMN_TYPE='INT(200)'
  AND TABLE_SCHEMA = 'DATABASE_NAME';

Will it solve my problem and is it safe to do that.

Thanks in advance.

I'd be surprised if you can update an information_schema view. But MySQL has surprised me more than once. — Mike Sherrill 'Cat Recall', Apr 05 '14 at 18:45
First somebody please tell me is it wrong to use int(200), varchar(200), double(200,2)???????? — Denial, Apr 05 '14 at 19:15
`int(200)` specifies an integer with a display width of 200 digits. Display width doesn't affect the range of values you can store in an integer. `double(200, 2)` means to store floating point numbers of up to 200 digits, of which 2 might be to the right of the decimal point. This *does* affect what you can store in a double; `double(5,0)` disallows, for example, 123456. Using `200` all over the place tells me you don't know what those values do, and you didn't read the docs. — Mike Sherrill 'Cat Recall', Apr 05 '14 at 20:09
Did you see the end of what I wrote above? "Using 200 all over the place tells me you don't know what those values do, and you didn't read the docs." If you want to make a good impression, use search engines and [read the docs](http://dev.mysql.com/doc/refman/5.7/en/char.html). — Mike Sherrill 'Cat Recall', Apr 05 '14 at 21:56

score 0 · Answer 1 · answered Apr 05 '14 at 18:33

0

ALTER TABLE table_name CHANGE collumn_Name1 collumn_Name2 INT(20) ;

it should work.

Note:Collumn_name2 is new Collumn name if you do not want to change its name then type the same collumn name.

answered Apr 05 '14 at 18:33

N.Be

48
7

this might help :http://stackoverflow.com/questions/4822638/find-and-replace-in-entire-mysql-database – N.Be Apr 05 '14 at 18:47

score 0 · Accepted Answer · answered Apr 06 '14 at 17:02

So guys

I am not going to change anything in my database because after reading the docs I believe that there is nothing wrong to use int(200), double(200,2), varchar(200) and it will not going to make me any trouble as per my requirements.

Still I want to add one solution for my original question-

we can write a php script to change data type in the entire database together with the following code-

$loop2=$con->query("SELECT COLUMN_NAME, TABLE_NAME, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'database_name' AND COLUMN_TYPE = 'int(200)'");

    while ($row2 =mysqli_fetch_array($loop2))
    {
         $row1=$con->query("alter table database_name.$row2[TABLE_NAME] change column $row2[COLUMN_NAME] $row2[COLUMN_NAME] int(20) default '$row2[COLUMN_DEFAULT]'");

    }

changing datatype in the entire database

2 Answers2