Decimal与MySQL计算精度问题求助：总价丢失小数位

Hey there! Let's break down why your calculated total price (29.25) is ending up as 29 in the database. Here's what's going on and how to fix it:

The Root Cause #

Your code's calculation logic is actually correct—using decimal.Parse for the inputs and multiplying them gives you the proper decimal value (29.25). The issue almost certainly lies in one of two places:

1. Incorrect Database Field Type #

If your Sales table's TotalPrice column is set to an integer type (like INT or BIGINT), the database will automatically truncate any decimal values when inserting, turning 29.25 into 29.

2. Risky String-Formatted SQL Queries #

Using string.Format to build your SQL query can lead to unexpected formatting issues (like regional settings turning decimals into commas) and also exposes you to SQL injection attacks. Even if your calculation is right, the string conversion might be mangling the decimal value before it hits the database.

Fixes to Implement #

Step 1: Update the Database Column Type #

Modify your Sales table's TotalPrice column to use a decimal type that supports precision. For most pricing scenarios, DECIMAL(10,2) works great—it allows up to 10 total digits with 2 decimal places (adjust the numbers based on your needs).

Step 2: Use Parameterized Queries (Critical!) #

Replace the string-formatted query with parameterized SQL. This ensures your decimal values are passed correctly to the database without formatting errors, and eliminates SQL injection risks. Here's your updated Insert method:

public static Sales Insert(int cID, String cNameSurname, String cPlate, int mID, String mName, Decimal mPrice, Decimal pWeight, Decimal totalPrice, String dt, String explanation) {
    String query = @"INSERT INTO sales(CustomerID,CustomerNameSurname,CustomerPlate,MaterialID,MaterialType,MaterialPrice,PurchasedWeight,TotalPrice,SalesDate,Explanation) 
                     VALUES (@CustomerID, @CustomerNameSurname, @CustomerPlate, @MaterialID, @MaterialType, @MaterialPrice, @PurchasedWeight, @TotalPrice, @SalesDate, @Explanation)";
    MySqlCommand cmd = new MySqlCommand(query, DB.dbConn);
    
    // Add parameters to safely pass values
    cmd.Parameters.AddWithValue("@CustomerID", cID);
    cmd.Parameters.AddWithValue("@CustomerNameSurname", cNameSurname);
    cmd.Parameters.AddWithValue("@CustomerPlate", cPlate);
    cmd.Parameters.AddWithValue("@MaterialID", mID);
    cmd.Parameters.AddWithValue("@MaterialType", mName);
    cmd.Parameters.AddWithValue("@MaterialPrice", mPrice);
    cmd.Parameters.AddWithValue("@PurchasedWeight", pWeight);
    cmd.Parameters.AddWithValue("@TotalPrice", totalPrice);
    cmd.Parameters.AddWithValue("@SalesDate", dt);
    cmd.Parameters.AddWithValue("@Explanation", explanation);
    
    DB.dbConn.Open();
    cmd.ExecuteNonQuery();
    int id = (int)cmd.LastInsertedId;
    Sales sale = new Sales(id, cID, cNameSurname, cPlate, mID, mName, mPrice, pWeight, totalPrice, dt, explanation);
    DB.dbConn.Close();
    return sale;
}

Quick Verification #

After making these changes:

• Double-check that your TotalPrice column is now a decimal type in the database
• Test your input again (4.5 and 6.5) — the total should correctly store as 29.25

内容的提问来源于stack exchange，提问作者komtan2