mysql field types integer over varchar

Forum Moderators: open

Message Too Old, No Replies

mysql field types integer over varchar

sunroof

3:19 pm on Apr 7, 2008 (gmt 0)

I have a mysql table with column ID which is a PRIMARY key. The type of column ID is varchar(10).

I need to change field type to integer, but the problem is when I have zero at the begining of the value, and change it to integer the zero disappears.

These are some of the values:

020301111
030301111
040301111
080407101
080407102
080407103
080407104
080407105
080407106
etc....

What do you suggest what type of the field should I set for this field ID?

Thanks a lot.

Demaestro

3:26 pm on Apr 7, 2008 (gmt 0)

Is there a reason you have to keep them?

It kind of gets ugly in code but you could so add some code at time of display or use that adds the 0.

Like:

id = "0" + str(id)

But if when that number flips to a 1 will you want the 0? If not then

if id < 100000000:
{id = "0" + str(id)}

Again not very elegant but it would work.

[edited by: Demaestro at 3:27 pm (utc) on April 7, 2008]

sunroof

3:31 pm on Apr 7, 2008 (gmt 0)

The problem is that I already have thousands of numbers like this in the table, and a function to create those numbers (based on YYMMDD001...)

I just think varchar isn't good field for this type of data.

Demaestro

4:42 pm on Apr 7, 2008 (gmt 0)

Your right it isn't.

Changing the function to use:

YYYYMMDD001....

Instead will solve future numbers from starting with a "0" however you are still left with a lot of legacy numbers to deal with.

Would it mess things up to go in and change the old ones to use YYYY?

You could go change them to all start with "20" assuming you have no 1900 data even if you did you could find them and add a "19"

Not sure how feasible this all is but YYYY would make more sense.