0

I am developing a new version of an application that was using an ms-access database because it was becoming too slow. So I decided to use MySQL as database. i'm happy with my choice. The problem is that i have a huge database filled with prices. This prices are shown correctly in the old application but in my database it's shown like this: '26,.000000.00','71,9.00000.00','24,9.00000.00'. The field is 

'price' VARCHAR(255) NOT NULL DEFAULT '0',

I do not know how to fix this. is this because of a data type or because the app was really terrible?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Simone
  • 1

2 Answers2

3

i do not know how to fix this. is this because of a data type or because the app was really terrible?

The problem should be the datatype. This thread will help you choose one.

Also, you probably will want to convert your column to decimal (or other numeric) type. It goes like

  • adding a new column of the desired type, ALTER TABLE herpderp add new_price Decimal (19,4)
  • populate it from the old column (you'll need a handy function to convert your strings to numbers) update herpderp set new_price = handy_function(price)
  • drop the old column ALTER TABLE herpderp DROP COLUMN price
  • rename the new column to the old name ALTER TABLE herpderp CHANGE COLUMN new_price price
Community
  • 1
  • 1
bpgergo
  • 15,669
  • 5
  • 44
  • 68
0

I think you should use the correct data type to store those values, if you are trying to store prices (money) then you should use Decimal data type.

This is what the documentation says:

Fixed-Point (Exact-Value) Types

The DECIMAL and NUMERIC types store exact numeric data values. These types are used when it is important to preserve exact precision, for example with monetary data. In MySQL, NUMERIC is implemented as DECIMAL, so the following remarks about DECIMAL apply equally to NUMERIC.

You can read more about this, here: http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html

PachinSV
  • 3,680
  • 2
  • 29
  • 41
  • Trying to change the type to deimal results in an error like "truncated incorrect value". This is not data that i'm storing but data that i have to recover. – Simone May 03 '11 at 14:29
  • I would like to know how can you interpret this number 26,.000000.00, obviously maybe you will loose some of that zeros, that's why your data has to be truncated. – PachinSV May 03 '11 at 17:28