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:
|
<<: VMware Workstation installation Linux (Ubuntu) system
>>: WeChat applet implements form verification
Description: Set a timer to replace the content of...
This article shares the specific code of JavaScri...
First, setInterval is encapsulated as a Hook 👇 im...
1. Scroll Snap is a must-have skill for front-end...
This article shares the specific code of Javascri...
Table of contents Written in front 1. Ngixn image...
async function and await keyword in JS function h...
First of all, what is a font icon? On the surface...
Yesterday when I was implementing the function of...
trigger: Trigger usage scenarios and correspondin...
I think this is a problem that many people have en...
After MySQL was upgraded to version 5.7, its secu...
MySQL handles GROUP BY and DISTINCT queries simil...
introduction During the front-end project develop...
Table of contents 1. How to obtain elements Get i...