Description of the meaning of N and M in MySQL Data Type DECIMAL (N, M), mysqldecimal

Description of the meaning of N and M in MySQL Data Type DECIMAL (N, M), mysqldecimal

My colleague asked me what the meaning of N and M in the MySQL Data Type DECIMAL (N, M). Needless to say, M is obviously the DECIMAL place, but is N the maximum number of digits before the decimal point or the maximum number of digits after the decimal point? This is hard to remember. As a result, the test table is created and verified. The result is as follows:

In the test table, the seller_cost field is defined as decimal)

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

At first, the table content is empty.

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

Insert a 14-digit integer to report an error that exceeds 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 the length of 12 in the integer part.

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" at the end of the inserted integer"

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

Continue to insert the integer part 12 digits, decimal part 5 digits, you can insert successfully, but there is a warning, warning that the fractional part has been truncated, is 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)

The length of the integer part is reduced to 2, and the length of the fractional part continues to be 5. The integer part can be inserted successfully, but the fractional 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)

Insert a number with less than two digits in the fractional part. The number can be correctly inserted, and the fractional part is automatically supplemented 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 conclusion, the M value in DECIMAL (N, M) is the number of digits in the DECIMAL part, if the inserted value does not specify the decimal part or the decimal part is less than M digits, it will be automatically filled with M decimal places. If the inserted decimal part exceeds M, it will be truncated, truncates the first M decimal places. N is the total length of the integer part plus the decimal part, that is, the inserted integer part cannot exceed the N-M bit, otherwise the insertion fails, an error out of the range is reported.

Summary

The above section describes all the meanings of N and M in the MySQL Data Type DECIMAL (N, M. Interested friends can continue to refer to this site: MySQL or statement usage examples, brief the difference between Redis and MySQL, etc. If you have any questions, please feel free to leave a message. The editor will reply to you in time. Thank you for your support!