0

I have a database (Engine InnoDB) that was setup by someone else. I noticed that one column contains data between 1 and 107 at this moment, and it is highly unlikely to further increase much.

As it is currently set up as INT my idea was to change type to TINYINT unsigned.

Can I just do that without losing data? What would be the most efficient way to go about it?

I am still rather new to databases, getting my hands dirty at the moment and learning all about data types and performance, etc. Looks like my precursor hasn't done such a good job... :(

2

A tiny int will save you space. Here are their storage:

tinyint: 1 byte
smallint: 2 bytes
int: 4 bytes

From MySQL official website: http://dev.mysql.com/doc/refman/5.1/en/integer-types.html

Keep in mind a tinyint has a range up to 255. If there is a chance you may go over, I would use smallint or int

You can alter the column in sql and not lose data:

    alter table MyTable alter column MyColumn tinyint --(null|not null)
  • 1
    Thanks Ben. I already knew the details in your answer (size, range, how to do it) but it is good to know there won't be data loss. That was the main question. – koljanep Dec 30 '14 at 20:19
  • 2
    @koljanep np. If you happen to have data that can't be converted to a tinyint, sql will throw an error and no data will be lost either way. – Ben Dec 30 '14 at 20:26
  • @koljanep Several addendums. In order to avoid mistakes, make sure you are using ENGINE=Innodb and sql_mode='STRICT_TRANS_TABLES', so you can rollback in case of an unexpected problem. I recommend using tinyint unsigned as that will give you the (0,255) range. Only starting with 5.6 can the datatype be changed online, otherwise write transactions for that table may fail while the ALTER is ongoing. – jynus Dec 31 '14 at 10:58
  • Warning, this will lock your table, at least on version 5.6! See dev.mysql.com/doc/refman/5.6/en/… – Erebus Dec 10 '18 at 19:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.