Detailed explanation of the meaning of N and M in the MySQL data type DECIMAL(N,M)

A colleague asked me what N and M mean in the MySQL data type DECIMAL(N,M). Needless to say, M is the number of decimal places after the decimal point, but is this N the maximum number of digits before the decimal point, or the maximum number of digits after adding the decimal part? I really can't remember this. So, I created a test table to verify it, and the results are as follows:

Test table, seller_cost field is 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

Initially, the table is empty.

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

Inserting a number with an integer part length of 14 will result in an error that the column range is exceeded.

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

Inserting a number with an integer part length of 12 can be inserted correctly

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

Query the table and find that MySQL adds two decimal places ".00" to the end of the inserted integer value

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

Continue to insert numbers with 12 digits for the integer part and 5 digits for the decimal part. The number can be inserted successfully, but there is a warning. The warning indicates that the decimal part has been truncated to 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)

The length of the integer part is reduced to 2, and the length of the decimal part remains 5. Insertion can be successful, 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 a number with a decimal part of less than two digits. It can be inserted correctly and the decimal part is automatically rounded to two digits.

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)

In summary, the M value in DECIMAL(N,M) is the number of decimal places. If the inserted value does not specify the decimal part or the decimal part is less than M digits, it will be automatically padded to M decimal places. If the decimal part of the inserted value exceeds M, it will be truncated and the first M decimal places will be truncated. N is the total length of the integer part and the decimal part, that is, the integer part of the inserted number cannot exceed NM digits, otherwise it cannot be inserted successfully and an out of range error will be reported.

Summarize

The above is all the content of this article about the detailed explanation of the meanings of N and M in the MySQL data type DECIMAL(N,M). I hope it will be helpful to everyone. Interested friends can continue to refer to this site: Examples of using or statements in MySQL, a brief description of the differences between Redis and MySQL, etc. If you have any questions, you can leave a message at any time and the editor will reply to you in time. Thank you friends for supporting this site!