Why can't I change my INT from unsigned to signed? Is signed Zerofill possible?

Question

I have a field called NUMBER in a table called TEST

NUMBER is int(8) Unsigned Zerofill Not Null

I need negative numbers in this field now and I'd still like them zerofilled For example -00023419

I can change it to signed, but can't seem to get zerofilled.

if I do: alter table test modify number int(8) signed zerofill not null - It stays unsigned zerofill

if I do: alter table test modify number int(8) zerofill not null - It stays unsigned zerofill

if I do: alter table test modify number int(8) signed not null - I get signed but no zerofill

When I had it set to signed, I put an a negative number then tried to change to zerofill and the number changed to 00000000 and everything was set to unsigned again. It it impossible to have signed zerofilled numbers?

Answer 1

https://dev.mysql.com/doc/refman/5.7/en/numeric-type-attributes.html says:

If you specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to the column.

Reported as a bug in 2006, closed as "Not a Bug". https://bugs.mysql.com/bug.php?id=24356

---

Re your comment:

So does this mean no one out there has negative zero filled data in a mysql database?

Right. The most common use case for ZEROFILL is to make sure things like zip codes or bank account numbers use a fixed number of digits. These cases don't support negative values anyway, so why bother with ugly strings like -00001234?

can you convert it to zerofilled with PHP?

Yes, you can format numbers with zero-padding in PHP.

<?php

$n = -1234;
printf("Number is %08d\n", $n);

Output:

Number is -0001234

Read http://php.net/manual/en/function.sprintf.php for more neat formatting things you can do with printf()/sprintf().

Answer 2

ZEROFILL implies UNSIGNED.

MySQL Reference Manual https://dev.mysql.com/doc/refman/5.7/en/numeric-type-attributes.html

If you specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to the column.

With all the potential problems, I avoid using the non-standard MySQL ZEROFILL attribute.

Answer 3

Well, if your column is a foreign key or a primary key, MySQL doesn't allow this modification to happen. You have to do this beforehand.