Explain the meanings of N and M in MySQL data types DECIMAL of N and M respectively

Colleagues asked what N and M in MySQL data types DECIMAL (N, M) mean respectively. Needless to say, M is obviously the decimal place after the decimal point, but is this N the maximum digit before the decimal point or the maximum digit after adding the decimal part? I really can't remember this. As a result, the test table was created and verified once, and the results are as follows:

Test table, seller_cost field defined as decimal (14, 2)

CREATE TABLE `test_decimal` (
 `id` int(11) NOT NULL,
 `seller_cost` decimal(14,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

At first, the contents of the table are empty

mysql> select * from test_decimal;
Empty set (0.00 sec)

Insert a number with a length of 14 in the integer part to report an error outside the column range

mysql> insert into test_decimal(id,seller_cost) values(1,12345678901234);
ERROR 1264 (22003): Out of range value for column 'seller_cost' at row 1

Insert a number with a length of 12 in the integer part, which can be inserted correctly

mysql> insert into test_decimal(id,seller_cost) values(1,123456789012);
Query OK, 1 row affected (0.00 sec)

Look up the table and find that the inserted integer value is filled by two decimal places ". 00" at the end of MySQL

mysql> select * from test_decimal;
+----+-----------------+
| id | seller_cost   |
+----+-----------------+
| 1 | 123456789012.00 |
+----+-----------------+
1 row in set (0.00 sec)

Continue to insert 12 digits in the integer part and 5 digits in the decimal part, which can be successfully inserted, but there is a warning, which indicates that the decimal part has been truncated and truncated into two decimal places

mysql> insert into test_decimal(id,seller_cost) values(1,123456789012.12345);
Query OK, 1 row affected, 1 warning (0.00 sec)
 
mysql> show warnings;
+-------+------+--------------------------------------------------+
| Level | Code | Message                     |
+-------+------+--------------------------------------------------+
| Note | 1265 | Data truncated for column 'seller_cost' at row 1 |
+-------+------+--------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> select * from test_decimal;
+----+-----------------+
| id | seller_cost   |
+----+-----------------+
| 1 | 123456789012.00 |
| 1 | 123456789012.12 |
+----+-----------------+
2 rows in set (0.00 sec)

Reduce the length of the integer part to 2, and keep the length of the decimal part to 5, which can be inserted successfully, but the decimal part is truncated to two digits.

mysql> insert into test_decimal(id,seller_cost) values(1,12.12345);
Query OK, 1 row affected, 1 warning (0.00 sec)
 
mysql> show warnings;
+-------+------+--------------------------------------------------+
| Level | Code | Message                     |
+-------+------+--------------------------------------------------+
| Note | 1265 | Data truncated for column 'seller_cost' at row 1 |
+-------+------+--------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> select * from test_decimal;
+----+-----------------+
| id | seller_cost   |
+----+-----------------+
| 1 | 123456789012.00 |
| 1 | 123456789012.12 |
| 1 |      12.12 |
+----+-----------------+
3 rows in set (0.00 sec)

Continue to insert 1 number with less than two decimal places, which can be inserted correctly, and the decimal part is automatically completed to two places.

mysql> insert into test_decimal(id,seller_cost) values(1,12.1);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from test_decimal;
+----+-----------------+
| id | seller_cost   |
+----+-----------------+
| 1 | 123456789012.00 |
| 1 | 123456789012.12 |
| 1 |      12.12 |
| 1 |      12.10 |
+----+-----------------+
4 rows in set (0.00 sec)

To sum up, the value of M in DECIMAL (N, M) is the number of decimal parts. If the inserted value does not specify the decimal part or the decimal part is less than M bits, it will be automatically supplemented to the decimal part of M bits. If the decimal part of the inserted value exceeds M, truncation will occur, and the former M bits will be truncated. The N value is the total length of the integer part plus the decimal part, that is, the integer part of the number inserted cannot exceed N-M bits, otherwise the insertion cannot be successful, and an out-of-range error will be reported.

Summarize

The above is all the contents of this article about explaining the meanings of N and M in MySQL data type DECIMAL (N, M), hoping to be helpful to everyone. Interested friends can continue to refer to this site: MySQL in or sentence usage examples, Redis and MySQL, etc., any questions can leave a message at any time, this site will reply to everyone in time. Thank you friends for your support to this site!