CheeseFlavored CheeseFlavored - 1 month ago 7
MySQL Question

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?

Answer

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

Comments