mysqlintegerunsignedsignedzerofill

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

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?

Solution

  • 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().