How to store decimals in MySQL?

#To store decimals in MySQL, you need to understand these two parameters. The syntax is as follows -

DECIMAL(yourTotalDigit,yourDigitsAfterDecimalPoint);

For example -

DECIMAL(4,2), which means that a total of 4 digits can be taken, and 2 digits after the decimal point.

The first parameter can have up to 2 digits before the decimal point.

The second parameter can have up to 2 digits after the decimal point.

• Case 1 − 12.34 valid.
• Case 2 − 123.4 is invalid.
• Case 3 − 1.234 is valid because the value 4 will be ignored and treated as 1.23

Now you can check using the table -

mysql> create table DecimalDemo
   -> (
   -> Amount DECIMAL(4,2)
   -> );
Query OK, 0 rows affected (0.47 sec)

Our example The invalid values ​​of Decimal(4,2) are as follows -

mysql> insert into DecimalDemo values(123.4);
ERROR 1264 (22003): Out of range value for column 'Amount' at row 1

mysql> insert into DecimalDemo values(1234);
ERROR 1264 (22003): Out of range value for column 'Amount' at row 1

mysql> insert into DecimalDemo values(1234.56);
ERROR 1264 (22003): Out of range value for column 'Amount' at row 1

The valid values ​​are as follows-

mysql> insert into DecimalDemo values(12.34);
Query OK, 1 row affected (0.13 sec)

mysql> insert into DecimalDemo values(12.4);
Query OK, 1 row affected (0.18 sec)

mysql> insert into DecimalDemo values(.2345);
Query OK, 1 row affected, 1 warning (0.18 sec)

mysql> insert into DecimalDemo values(1.234);
Query OK, 1 row affected, 1 warning (0.16 sec)

Use the select statement to display all valid values ​​in the table. The query is as follows -

mysql> select *from DecimalDemo;

Output

+--------+
| Amount |
+--------+
| 12.34  |
| 12.40  |
| 0.23   |
| 1.23   |
+--------+
4 rows in set (0.00 sec)

The above is the detailed content of How to store decimals in MySQL?. For more information, please follow other related articles on the PHP Chinese website!