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

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!

You may also be interested in:
  • A brief introduction to the usage of decimal type in MySQL
  • The difference between Decimal type and Float Double in MySQL (detailed explanation)
  • Method to convert scientific notation numeric string to decimal type
  • Database data type float to C# type decimal, float data type conversion is invalid
  • Detailed explanation of the usage of DECIMAL in MySQL data type
  • Implementation of mysql decimal data type conversion
  • Detailed explanation of the decimal padding problem of decimal data type in MySQL
  • Detailed explanation of the usage of MySQL data type DECIMAL
  • In-depth explanation of the use and implementation of the Decimal type in the database

<<:  VMware Workstation installation Linux (Ubuntu) system

>>:  WeChat applet implements form verification

Recommend

HTML+CSS+JavaScript to achieve list loop scrolling example code

Description: Set a timer to replace the content of...

Implementing a shopping cart with native JavaScript

This article shares the specific code of JavaScri...

React+Typescript implements countdown hook method

First, setInterval is encapsulated as a Hook 👇 im...

CSS scroll-snap scroll event stop and element position detection implementation

1. Scroll Snap is a must-have skill for front-end...

Javascript implements simple navigation bar

This article shares the specific code of Javascri...

The use and methods of async and await in JavaScript

async function and await keyword in JS function h...

Let me teach you how to use font icons in CSS

First of all, what is a font icon? On the surface...

MySQL trigger usage scenarios and method examples

trigger: Trigger usage scenarios and correspondin...

How to modify Flash SWF files in web pages

I think this is a problem that many people have en...

How to change the password of mysql5.7.20 under linux CentOS 7.4

After MySQL was upgraded to version 5.7, its secu...

MySQL group query optimization method

MySQL handles GROUP BY and DISTINCT queries simil...

Application example tutorial of key in Vue page rendering

introduction During the front-end project develop...

Detailed explanation of basic interaction of javascript

Table of contents 1. How to obtain elements Get i...