访问MySQL奇怪的价格字段

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

我正在开发一个使用ms-access数据库的应用程序的新版本,因为它变得太慢了。所以我决定使用MySQL作为数据库。我很满意我的选择。问题是我有一个充满价格的庞大数据库。此价格在旧应用程序中正确显示,但在我的数据库中显示如下:'26,.000000.00','71,9.00000.00','24,9.00000.00'。这个领域是

'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?

我不知道如何解决这个问题。这是因为数据类型还是应用程序非常糟糕?

2 个解决方案

#1

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)

添加所需类型的新列,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)

从旧列填充它(你需要一个方便的函数将你的字符串转换为数字)更新herpderp set new_price = handy_function(price)

drop the old column ALTER TABLE herpderp DROP COLUMN price

删除旧列ALTER TABLE herpderp DROP COLUMN价格

rename the new column to the old name ALTER TABLE herpderp CHANGE COLUMN new_price price

将新列重命名为旧名称ALTER TABLE herpderp CHANGE COLUMN new_price price

#2

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.

DECIMAL和NUMERIC类型存储精确的数字数据值。在保持精确精度很重要时使用这些类型,例如使用货币数据。在MySQL中,NUMERIC实现为DECIMAL,因此以下有关DECIMAL的备注同样适用于NUMERIC。

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

您可以在此处阅读更多相关信息:http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html

#1