CODEWITHSUNDEEP
mysqlsqldatabasetinyint
Jay
Jay

Reputation: 73

Changing from tinyint to smallint with a database full of data

A peace of mind question really.

I have a field in my database that uses tinyint(3) as the integer value. I've now realised that this is to low a value and want to change it to smallint. If I change this using phpMyAdmin, will any of the current data be effected? I've tried it locally and seems fine but I prefer to run it by people with more database experience.

Upvotes: 3

Views: 4671

Answers (2)

Jeff Rosenberg
Jeff Rosenberg

Reputation: 3572

Your data will be affected in a way -- it will be changed from tinyint to smallint, so in effect the entire column will undergo a CAST operation. But it won't be affected in the sense of experiencing any truncation or data loss.

Because tinyint is a subset of smallint, this cast operation shouldn't cause any problems. If you attempted to cast a tinyint value to smallint in a query, you would never expect that to fail, because every tinyint is also a smallint. The same logic applies when modifying your table schema.

Upvotes: 2

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

You can use below query:

mysql> ALTER TABLE TableName MODIFY Id SMALLINT

Also make sure if there is any constraint attached to the column then you need to first drop those constraint.

Upvotes: 6

Related Questions