CODEWITHSUNDEEP
phpmysqldatabase
Farzher
Farzher

Reputation: 14573

MySQL PHP alter table, change all MEDIUMINT to INT

In the past I've used a PHP script to change the language of all fields, but I can't find anything for switching all UNSIGNED MEDIUMINT to UNSIGNED INT. Is this possible? Should I manually switch them all? :o

Turns out mediumint is too small... so I have to change a lot of fields /:

edit: thanks for the input, I might just do it manually now that I know there's no simple automated way of doing it. I just didn't want to waste my time changing 100+ fields manually if there was an automated way.

Upvotes: 1

Views: 2635

Answers (3)

Anoop Naik
Anoop Naik

Reputation: 335

You can use the below query to find all mediumint columns (with respective table names) in a specific database and then iterate over the results to generate and execute ALTER queries in your choice of programming language. 

SELECT 
    table_name, column_name, numeric_precision, column_type
FROM
    information_schema.columns
WHERE
    TABLE_SCHEMA = 'DATABASE_NAME'
        AND data_type = 'mediumint';

Upvotes: 1

Xesued
Xesued

Reputation: 4167

Has to be done in SQL, so if it makes sense in your case to write a script to do it, go for it, but most likely you will want to do it manually.

Since you are going from a data type that is less descriptive to a data type that is more descriptive, you should be fine.

Usint the ALTER TABLE Syntax should work:

ALTER TABLE table_name MODIFY column_name INT UNSIGNED

Upvotes: 0

cdhowie
cdhowie

Reputation: 169038

MySQL does not have an automated way to say "take all of the columns of type X and change them to type Y." You may be able to query the information_schema database to figure out which columns you need to alter, and then you can build the ALTER TABLE query based on that information.

Specifically, have a look at the information_schema.columns table.

Upvotes: 2

Related Questions