4

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.

jpw
  • 44,361
  • 6
  • 66
  • 86
Jay
  • 83
  • 2
  • 9

2 Answers2

7

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.

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • Thanks for your reply. I'm not really sure what constraints are but I haven't manually set any up. My table is very simple with one of the fields (called 'score') holding a score. This was initially set to tinyint(3) but it turns out this isn't big enough. I just changed it in phpmyadmin but this should be fine shouldn't it? – Jay Mar 20 '15 at 13:34
  • Do `SHOW CREATE TABLE` to see what it currently says about Id. Examples: `NOT NULL`, `AUTO_INCREMENT`, ... -- Those need to be repeated in the ALTER, pelase you will lose them. – Rick James Mar 20 '15 at 22:28
2

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.

Jeff Rosenberg
  • 3,522
  • 1
  • 18
  • 38