23 September 2011

MySQL - change INT from signed to unsigned

If you need to use an INT field in MySQL, it's useless to set it to signed values (default) if all the values that you will store there are possitive (eg. auto-increments starting with 1). Also, you will be limited to 2,147,483,647 records.

If you wish to change the field type to unsigned, which will allow you to store 4,294,967,295 records, you can use the following statement:

sql> alter table TABLE_NAME modify FIELD_NAME int unsigned;
Query OK, 0 rows affected (0.88 sec)
Records: 0  Duplicates: 0  Warnings: 0


Read more about INT types here.

2 comments:

  1. The column in the table which I am trying to alter contain negative value which is blocking me from altering the table. Can you please let know me on how to handle such situation.

    ReplyDelete
    Replies
    1. What do you mean it's blocking you? Negative values are changed to 0 by default in MySQL 5.6.

      mysql> create table test (a int);
      Query OK, 0 rows affected (0.51 sec)

      mysql> insert into test(a) values(-1);
      Query OK, 1 row affected (0.00 sec)

      mysql> select * from test;
      +------+
      | a |
      +------+
      | -1 |
      +------+
      1 row in set (0.00 sec)

      mysql> alter table test modify a int unsigned;
      Query OK, 1 row affected, 1 warning (0.40 sec)
      Records: 1 Duplicates: 0 Warnings: 1

      mysql> select * from test;
      +------+
      | a |
      +------+
      | 0 |
      +------+
      1 row in set (0.05 sec)

      Delete