MariaDB中如何仅限制小数点后位数而不限制整数位？

Got it, let's tackle this problem for you. The issue with your current DOUBLE(10,2) setup is that the first number (10) defines the total number of digits (both integer and decimal combined), which is why your integer part gets capped. Here are the best ways to fix this:

1. Use DECIMAL Type (Recommended) #

This is the most reliable approach for fixed-point decimal values, especially when you need precise control over decimal places without limiting the integer part.

MariaDB allows the DECIMAL type to have a maximum precision of 65 digits total. By setting DECIMAL(65, 2), you get:

• 2 fixed decimal places
• Up to 63 digits for the integer part (more than enough for even extremely large numbers like 999999999999.01)

Create Table Statement: #

CREATE TABLE mytable (
    mynumber DECIMAL(65, 2) NOT NULL
);

How It Works: #

• When you insert values like 1.11, 56789.12, 9999.00, or 999999999999.01, they'll be stored exactly as-is.
• If you insert a value with more than 2 decimal places (e.g., 1.1234), MariaDB will automatically round it to 1.12 (default behavior, which aligns with your needs).

2. Workaround with DOUBLE (Not Recommended) #

If you insist on using DOUBLE (note: floating-point types can have precision errors for decimal values), you can avoid restricting the integer part while enforcing 2 decimal places using:

Option A: Explicit Rounding on Insert/Update #

Always round values to 2 decimals when inserting or updating:

INSERT INTO mytable(mynumber) VALUES(ROUND(1234567890123.456, 2));
UPDATE mytable SET mynumber = ROUND(987654321.123, 2) WHERE id = 1;

Option B: Add a CHECK Constraint #

To enforce that only values with 2 decimal places are stored:

CREATE TABLE mytable (
    mynumber DOUBLE CHECK (mynumber = ROUND(mynumber, 2))
);

⚠️ Caution: Due to floating-point precision quirks, this might occasionally reject valid values (e.g., 0.2 could be stored as 0.2000000000000001 internally). Stick with DECIMAL if precision matters.

3. Auto-Enforce Rounding with Triggers #

For either type, you can use triggers to automatically round values to 2 decimals on insert/update, so you don't have to remember to call ROUND() every time:

-- Trigger for inserts
CREATE TRIGGER trg_mytable_round_insert BEFORE INSERT ON mytable
FOR EACH ROW
SET NEW.mynumber = ROUND(NEW.mynumber, 2);

-- Trigger for updates
CREATE TRIGGER trg_mytable_round_update BEFORE UPDATE ON mytable
FOR EACH ROW
SET NEW.mynumber = ROUND(NEW.mynumber, 2);

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