Access to MySQL strange price field

I am developing 开发者_JAVA技巧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?

---

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

---

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