วิธีจัดเก็บผลลัพธ์การคำนวณทศนิยมใน mysql และดึงข้อมูลกลับมาเหมือนที่อยู่ในหน่วยความจำ

เอกสารประกอบ MySQL พูด :

ประเภท DECIMAL และ NUMERIC จะเก็บค่าข้อมูลตัวเลขที่แน่นอน ประเภทเหล่านี้จะใช้เมื่อจำเป็นต้องรักษาความแม่นยำที่แน่นอน เช่น กับข้อมูลทางการเงิน

ฉันทำการทดสอบนี้ในคอลัมน์นั้น decimal_column DECIMAL(31,30)

insert into tests (decimal_column) values(1/3);

จากนั้นตรวจสอบสิ่งที่เก็บไว้ก็จะได้สิ่งนี้

select * from tests ;
> result : 0.333333333000000000000000000000

จากนั้นการย้อนกลับการดำเนินการทางคณิตศาสตร์ด้วยแบบสอบถามนี้จะทำให้ได้สิ่งนี้

select decimal_column*3 from test;
> result: 0.999999999000000000000000000000

ฉันคาดหวังว่าจะได้จำนวนเต็ม "1" ตามที่เราทำบนเครื่องคิดเลขและในแผ่นงาน Excel! แบบนี้

 #calculator or excel sheet
 >input: 1 / 3
 >result: 0.3333333333333333333333333333333333
 >input: * 3
 >result: 1

1- เหตุใด MySQL จึงไม่จัดเก็บการแสดงไบนารีที่แน่นอนของ (1 / 3) ดังนั้นฉันจึงสามารถใช้ผลลัพธ์นั้นอีกครั้งในการคำนวณของฉันเนื่องจากอยู่ในหน่วยความจำเช่นเครื่องคิดเลขหรือแผ่นงาน Excel

2- วิธีจัดเก็บผลลัพธ์ของ (1/3) ใน mysql เนื่องจากอยู่ในหน่วยความจำในช่วงเวลาการคำนวณ ดังนั้นฉันจึงสามารถดึงค่าที่แน่นอนกลับมาและทำบางอย่างเช่น 3 * $storedValue เพื่อให้ได้ผลลัพธ์เป็น 1 เป็นจำนวนเต็มเหมือนกับที่เราทำในเครื่องคิดเลขหรือแผ่นงาน Excel


mysql precision floating-point floating-accuracy
person Accountant م    schedule 16.03.2017    source แหล่งที่มา

คำตอบ (3)


arrow_upward
1
arrow_downward

ปัญหาไม่ได้อยู่ในพื้นที่เก็บข้อมูล ในตัวอย่างของคุณ ค่าเสียหายก่อนที่จะจัดเก็บไว้ในตาราง

น่าเสียดาย ถ้าคุณเขียน 1/3 นั่นจะถูกคำนวณ (และแทรก) โดยใช้การแสดงค่าเริ่มต้น:

SELECT 1/3

0.333333333

ซึ่งอย่างที่คุณเห็นมีความแม่นยำไม่เพียงพอ

ปัญหาเพิ่มเติมคือเมื่อคุณส่งค่าคงที่ (1 หรือ 3) ไปยังเซิร์ฟเวอร์ คุณจะทำได้โดยใช้ ไลบรารี หรือ ตัวเชื่อมต่อ ซึ่งสามารถใช้เสรีภาพกับ ค่า. ตัวอย่างเช่น อาจเชื่อว่า "1" และ "3" เป็นจำนวนเต็ม และผลลัพธ์จะถือว่าเป็นจำนวนเต็ม ดังนั้นคุณจะได้ "1/3 = 0" แต่ "1./3 = 0.333333" เพราะจุดใน "1" ทำให้ตัวเชื่อมต่อรู้ว่าจำเป็นต้องใช้จุดลอยตัวเริ่มต้น และคุณจะได้เลข 3 เพียงหกตัวเท่านั้น เนื่องจาก "จุดลอยตัวเริ่มต้น" ของตัวเชื่อมต่อมีตัวเลข 6 หลัก จากนั้นคุณจัดเก็บไว้ในฐานข้อมูล แต่ก็สายเกินไป คุณกำลังจัดเก็บค่าที่ถูกตัดทอนให้มีความแม่นยำต่ำด้วยความแม่นยำสูง

คุณสามารถลองส่งค่าคงที่ตั้งแต่ต้นได้ แทนที่จะเป็น "1" คุณใช้การโยน 1 เป็นทศนิยมที่มีขนาดใหญ่เพียงพอ ฉันใช้ 31,30 ของคุณที่นี่ แต่ตรวจสอบว่าคุณไม่จำเป็นต้องเก็บตัวเลขที่มากขึ้น บางที "31,20" จะดีกว่า

mysql> SELECT 1/3 UNION SELECT CAST(1 AS DECIMAL(31,30))/CAST(3 AS DECIMAL(31,30));
+----------------------------------+
| 1/3                              |
+----------------------------------+
| 0.333333333000000000000000000000 |
| 0.333333333333333333333333333333 |
+----------------------------------+
2 rows in set (0.00 sec)

มันเป็นเรื่องที่น่าอึดอัดใจมาก แต่ผลลัพธ์ควรจะดีขึ้น นอกจากนี้ ฉันคิดว่าจำเป็นต้องแปลงค่า หนึ่ง ในนิพจน์เท่านั้น จากนั้น MySQL จะส่งเสริมปริมาณที่เกี่ยวข้องทั้งหมดตามความจำเป็น ดังนั้น การเพิ่ม CAST(0 AS DECIMAL(x,y)) เข้าไปในผลรวม และ CAST(1 AS DECIMAL(x,y)) เข้าไปในการคูณอาจจะเพียงพอแล้ว

mysql> SELECT 3*CAST(1 AS DECIMAL(31,30))/CAST(3 AS DECIMAL(31,30));
+-------------------------------------------------------+
| 3*CAST(1 AS DECIMAL(31,30))/CAST(3 AS DECIMAL(31,30)) |
+-------------------------------------------------------+
|                      1.000000000000000000000000000000 |
+-------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST(1 AS DECIMAL(31,30))*1/3;
+----------------------------------+
| CAST(1 AS DECIMAL(31,30))*1/3    |
+----------------------------------+
| 0.333333333333333333333333333333 |
+----------------------------------+
1 row in set (0.00 sec)

โปรดทราบว่า สิ่งนี้ ใช้ไม่ได้เนื่องจากการคูณมีลำดับความสำคัญสูงกว่า:

mysql> SELECT CAST(0 AS DECIMAL(31,30))+1/3;
+----------------------------------+
| CAST(0 AS DECIMAL(31,30))+1/3    |
+----------------------------------+
| 0.333333333000000000000000000000 |
+----------------------------------+
1 row in set (0.00 sec)
person LSerni    schedule 16.03.2017
comment
ขอบคุณ. แต่ยังมีบางอย่างทำให้ฉันสับสน ทำไมเมื่อฉันทำในแผ่นงาน Excel 1/3 แล้วคูณผลลัพธ์ด้วย 3 ฉันได้รับ 1 เมื่อฉันจัดรูปแบบผลลัพธ์ 1 เพื่อแสดงเลขทศนิยมที่ฉันยังเห็น (1.0000000000000....) ไม่มีเศษส่วนเลยสำหรับศูนย์ทั้งหมดมากกว่า 70 หลัก ! - person Accountant م; 16.03.2017
comment
Excel มีเคล็ดลับที่เป็นประโยชน์ในการจัดรูปแบบหลายประการ สิ่งที่ ช่วยให้คุณเห็น ไม่ใช่สิ่งที่อยู่ข้างใน และมันพยายามที่จะเข้าใจ เช่น 16/1/59 เป็น 1 อย่างแท้จริงในเลขคณิต - มันคือวันที่ 16 มกราคม 2016 ในสำเนา Excel ของฉัน และ 16/1/16+1 ไม่ใช่ 2 แต่ประมาณ 46250 หรือประมาณนั้น - จำนวนวันจูเลียนตั้งแต่วันที่ฉันจำไม่ได้ ในกรณีอื่นๆ Excel จะแก้ไขสิ่งที่ คิด ว่าเป็นข้อผิดพลาดในการปัดเศษ... ไม่ว่าจะเป็นสิ่งที่ถูกต้องหรือไม่ก็ตาม โดยสรุป: อย่าใช้ Excel (หรือโปรแกรม [Microsoft] อื่นใด) เป็นตัวอย่างว่าคณิตศาสตร์ (หรือ โปรแกรมเมอร์ คณิตศาสตร์) ควรทำงานอย่างไร - person LSerni; 16.03.2017
comment
เอาน่า ตลอดหลายปีที่ผ่านมาในการทำงานกับเครื่องคิดเลข ฉันคิดว่าเมื่อฉันทำ 1/3 แล้วย้อนกลับกระบวนการ result * 3 ฉันก็จะได้ 1 แบบเดียวกับที่ฉันเริ่มด้วย ขอบคุณมากสำหรับเวลาของคุณ ฉันจะอ่านคู่มือจุดลอยตัวนั้นด้วยความสนใจ - person Accountant م; 16.03.2017

arrow_upward
1
arrow_downward

ขึ้นอยู่กับสิ่งที่คุณต้องการข้อมูล

หากเก็บไว้สำหรับการคำนวณและการจัดเก็บ ให้คำนวณผลลัพธ์ (0.33 แทน 1/3) และจัดเก็บเป็นทศนิยม (1,5) แต่คุณไม่สามารถคำนวณย้อนหลังได้อย่างง่ายดายถ้าคุณต้องการแสดง

หากเก็บไว้เพื่อแสดงในภายหลังแต่ไม่เคยแก้ไขอีก (หรืออย่างน้อยก็ไม่เร็ว) คุณสามารถจัดเก็บเป็น varchar ได้ แต่จะทำให้การเรียงลำดับเสียหาย

หรือคุณสามารถจัดเก็บองค์ประกอบต่างๆ (บวก ลบ ผลรวม ... อะไรก็ได้) เป็นทศนิยม (5,0) แล้วแสดง / คำนวณในขณะที่ใช้งาน

และแน่นอน คุณสามารถรวมสิ่งที่กล่าวมาข้างต้นได้หากต้องการลดเวลาในการประมวลผลในขณะที่เลือก

person Ashwin Golani    schedule 16.03.2017

arrow_upward
1
arrow_downward

MySQL ทำการคำนวณเศษส่วนภายในโดยใช้ตัวเลข จุดลอยตัว IEEE 754 แบบ 64 บิต

โดยทั่วไปเป็นการประมาณ มันไม่สมเหตุสมผลเลยที่จะคาดหวังจุดลอยตัวของ IEEE หรือเลขคณิตทศนิยมสำหรับเรื่องนั้นเพื่อให้ได้ความเท่าเทียมกันที่แน่นอนเมื่อทำ

   3*(1/3) == 1

นั่นไม่ใช่วิธีการทำงานของเลขคณิตของคอมพิวเตอร์

ไม่มีทางที่จะจัดเก็บการแสดงค่า 1/3 ได้อย่างแม่นยำ เว้นแต่ว่าคุณจะใช้ระบบคอมพิวเตอร์แปลกใหม่ที่เก็บตัวเลขตรรกยะ (เศษส่วน) ไว้ในรูปแบบของคู่ (ตัวเศษ ตัวส่วน) MySQL ไม่ใช่ระบบดังกล่าว

เครื่องคิดเลขส่วนใหญ่จะปัดเศษผลลัพธ์ตามจำนวนหลักที่สามารถแสดงได้ Excel ก็มีโมดูลการจัดรูปแบบด้วย คุณสามารถเลือกรูปแบบของเซลล์ได้โดยการกด -1 โมดูลการจัดรูปแบบจะปัดเศษตัวเลขทศนิยมเหล่านี้ คุณสามารถบรรลุผลแบบเดียวกันใน MySQL ได้โดยใช้ ROUND( ) ฟังก์ชัน ฟังก์ชันนั้นจะไม่เปลี่ยนค่าที่เก็บไว้ แต่จะแสดงผลในลักษณะที่ปกปิดข้อผิดพลาดเล็กๆ น้อยๆ ที่มีอยู่ในเลขคณิตลอยตัวของ IEEE 754

SELECT ROUND(decimal_column, 2) AS decimal_column

(คนบัญชีไม่จำเป็นต้องเรียนรู้สิ่งนี้ในโรงเรียนเหรอ?)

person O. Jones    schedule 16.03.2017
comment
ดังนั้น เมื่อฉันใช้เครื่องคิดเลข 1/3 ให้คูณผลลัพธ์ด้วย 3 *3 แล้วเครื่องคิดเลขจะให้ 1 นั่นหมายความว่า 1 นี้ไม่ใช่ค่าจำนวนเต็ม 1 เดียวกันกับที่ฉันเริ่มคำนวณใช่ไหม มันค่อนข้างจะเหมือนกับ 1.0000000000000000000000000000000000000000001 ? - person Accountant م; 16.03.2017
comment
คนบัญชีไม่จำเป็นต้องเรียนรู้สิ่งนี้ในโรงเรียนเหรอ? . ไม่พวกเขาไม่ได้ การแสดงตัวเลขทศนิยมในระบบไบนารี่นั้นอยู่นอกเหนือเนื้อหาทางบัญชีอย่างไร มันยังทำให้โปรแกรมเมอร์บางคนสับสนด้วยซ้ำ - person Accountant م; 16.03.2017