Chapter聽24.聽Precision Math - MySQL 5.1参考手册中文版
MySQL 5.1鎻愪緵浜嗗绮惧害鏁板鐨勬敮鎸侊紝涔熷氨鏄锛屾暟鍊煎鐞嗗姛鑳斤紝瀹冭兘缁欏嚭鏋佸叾绮剧‘鐨勭粨鏋滐紝骞惰兘瀵规棤鏁堝�艰繘琛岄珮搴︽帶鍒躲�傜簿搴︽暟瀛﹀熀浜庝笅杩颁袱绉嶇壒鎬э細
路 SQL妯″紡锛屾帶鍒舵湇鍔″櫒鎺ュ彈鎴栨嫆缁濇棤鏁堝�肩殑涓ユ牸绋嬪害锛堣鍙傝5.3.2鑺傦紝鈥淪QL鏈嶅姟鍣ㄦā寮忊��锛夈��
路 鐢ㄤ簬瀹氱偣绠楁硶鐨�MySQL搴撱��
瀵逛簬鏁板�兼搷浣滐紝杩欎簺鐗规�у叿鏈夋暟绉嶉殣鍚剰涔夛細
路 绮剧‘璁＄畻锛�瀵逛簬鍑嗙‘鍊兼暟鍊硷紝璁＄畻涓嶄細寮曞叆娴偣閿欒銆傜浉鍙嶏紝灏嗕娇鐢ㄥ噯纭殑绮惧害銆備緥濡傦紝瀵逛簬鏁板��.0001锛屼細灏嗗叾褰撲綔鍑嗙‘鍊间簣浠ュ鐞嗭紝鑰屼笉鏄繎浼煎�硷紝灏嗗叾鍔�10000娆″彲鑾峰緱鍑嗙‘鐨勭粨鏋�1锛岃�屼笉鏄繎浼间簬1鐨勫�笺��
路 瀹氫箟鑹ソ鐨勫洓鑸嶄簲鍏ョ壒鎬э細瀵逛簬鍑嗙‘鍊兼暟鍊硷紝ROUND()鐨勭粨鏋滃彇鍐充簬鍏跺弬閲忥紝鑰屼笉鏄幆澧冨洜绱狅紝濡傚簳灞�C搴撶殑宸ヤ綔鏂瑰紡绛夈��
路 骞冲彴鏃犲叧鎬э細瀵瑰噯纭暟鍊肩殑鎿嶄綔鍦ㄤ笉鍚屽钩鍙颁笂锛堝Unix鍜�Windows锛夋槸鐩稿悓鐨勩��
路 瀵规棤鏁堝�煎鐞嗙殑鎺у埗锛�鑳藉妫�娴嬪埌婧㈠嚭鍜岄櫎0鎯呭喌锛屽苟浼氬皢鍏跺綋浣滈敊璇姞浠ュ鐞嗐�備緥濡傦紝鑳藉灏嗗浜庢煇鍒楁潵璇磋繃澶х殑鍊煎綋浣滈敊璇寰咃紝鑰屼笉鏄璇ュ�艰繘琛屾埅鐭娇涔嬩綅浜庡垪鏁版嵁绫诲瀷鐨勮寖鍥村唴銆傚悓鏍凤紝涔熶細灏嗛櫎0褰撲綔閿欒锛岃�屼笉鏄細鑾峰緱NULL缁撴灉鐨勬搷浣溿�傝嚦浜庨�夋嫨閭ｇ鏂瑰紡锛屽畠鏄敱绯荤粺鍙橀噺sql_mode鐨勮缃喅瀹氱殑锛堣鍙傝5.3.2鑺傦紝鈥淪QL鏈嶅姟鍣ㄦā寮忊��锛夈��
杩欑被鐗规�х殑涓�涓噸瑕佺粨鏋滄槸锛�MySQL 5.1鎻愪緵浜嗕笌鏍囧噯SQL鐨勯珮搴﹀吋瀹规�с��
鍦ㄤ笅闈㈢殑璁ㄨ涓紝浠嬬粛浜嗙簿搴︽暟瀛︾殑鏁扮宸ヤ綔鏂瑰紡锛堝寘鎷笌鏃╂湡搴旂敤绋嬪簭鐨勫彲鑳界殑涓嶅吋瀹规�э級銆傚湪鏈�鍚庯紝缁欏嚭浜嗕竴浜涚ず渚嬶紝婕旂ず浜�MySQL 5.1鏄浣曠簿纭鐞嗘暟鍊兼搷浣滅殑銆�
瀵逛簬鍑嗙‘鍊兼搷浣滐紝绮惧害鏁板鐨勮寖鍥村寘鎷噯纭�肩殑鏁版嵁绫诲瀷锛�DECIMAL鍜屾暣鏁扮被鍨嬶級浠ュ強鍑嗙‘鍊兼暟鍊兼枃瀛椼�傚浜庤繎浼煎�兼暟鎹被鍨嬪拰鏁板�兼枃瀛楋紝浠嶄細灏嗗叾褰撲綔娴偣鏁板�间簣浠ュ鐞嗐��
鍑嗙‘鍊兼暟鍊兼枃瀛楀叿鏈夋暣鏁伴儴鍒嗗拰灏忔暟閮ㄥ垎锛屾垨涓よ�呫�傚畠浠彲浠ユ槸鏈夌鍙风殑銆備緥濡傦細1銆�.2銆�3.4銆�-5銆�-6.78銆�+9.10銆�
杩戜技鍊兼暟鍊兼枃瀛楅噰鐢ㄧ瀛﹁鏁版硶琛ㄧず锛屽寘鍚熬鏁板拰鎸囨暟銆備换鎰忛儴鍒嗘垨涓よ�呭潎鍙互鏄甫绗﹀彿鐨勩�備緥濡傦紝1.2E3銆�1.2E-3銆�-1.2E3銆�-1.2E-3銆�
瀵逛簬鐪嬩笂鍘荤被浼肩殑鏁板�硷紝涓嶉渶瑕佸潎涓哄噯纭�兼垨杩戜技鍊笺�備緥濡傦紝2.34鏄噯纭��锛堝畾鐐癸級鏁板�硷紝鑰�2.34E0鏄繎浼煎�硷紙娴偣锛夋暟鍊笺��
DECIMAL鏁版嵁绫诲瀷鏄畾鐐圭被鍨嬶紝鍏惰绠楁槸鍑嗙‘鐨勩�傚湪MySQL涓紝DECIMAL绫诲瀷鏈夊涓悓涔夎瘝锛�NUMERIC銆�DEC銆�FIXED銆傛暣鏁扮被鍨嬩篃鏄噯纭�肩被鍨嬨��
FLOAT鍜�DOUBLE鏁版嵁绫诲瀷鏄诞鐐圭被鍨�锛屽叾璁＄畻鏄繎浼肩殑銆傚湪MySQL涓紝涓�FLOAT鎴�DOUBLE鍚屼箟鐨勭被鍨嬫槸DOUBLE PRECISION鍜�REAL銆�
鏈妭璁ㄨ浜�MySQL 5.1涓�DECIMAL鏁版嵁绫诲瀷锛堜互鍙婂叾鍚屼箟绫诲瀷锛夌殑鐗规�э紝灏ゅ叾鏄笅杩版柟闈細
路 鏁板瓧鐨勬渶澶ф暟銆�
路 瀛樺偍鏍煎紡銆�
路 瀛樺偍瑕佹眰銆�
路 瀵�DECIMAL鍒椾笂鐣� 鐨勯潪鏍囧噯MySQL鎵╁睍銆�
鍦ㄦ湰鑺備腑锛屽浜庝负杈冩棭MySQL鐗堟湰缂栧啓鐨勫簲鐢ㄧ▼搴忥紝鍦ㄧ浉搴旂殑鍦版柟鎸囧嚭浜嗗彲鑳界殑涓嶅吋瀹规�с��
DECIMAL鍒楃殑澹版槑璇硶鏄�DECIMAL(M,D)銆傚湪MySQL 5.1涓紝鍙傞噺鐨勫彇鍊艰寖鍥村涓嬶細
路 M鏄暟瀛楃殑鏈�澶ф暟锛堢簿搴︼級銆傚叾鑼冨洿涓�1锝�65锛堝湪杈冩棫鐨�MySQL鐗堟湰涓紝鍏佽鐨勮寖鍥存槸1锝�254锛夈��
路 D鏄皬鏁扮偣鍙充晶鏁板瓧鐨勬暟鐩紙鏍囧害锛夈�傚叾鑼冨洿鏄�0锝�30锛屼絾涓嶅緱瓒呰繃M銆�
瀵逛簬M锛屾渶澶у��65鎰忓懗鐫�锛屽DECIMAL鍊肩殑璁＄畻鑳界簿纭埌65浣嶆暟瀛椼�傝繖绉�65浣嶆暟瀛楃殑绮惧害闄愬埗涔熼�傜敤浜庡噯纭�兼暟鍊兼枃瀛楋紝鍥犳锛岃繖绫绘枃瀛楀�肩殑鏈�澶ц寖鍥翠笉鍚屼簬浠ュ墠鐨勮寖鍥达紙鍦ㄨ緝鏃╃殑MySQL鐗堟湰涓紝鍗佽繘鍒跺�艰兘杈惧埌254浣嶃�備笉杩囷紝閲囩敤鐨勬槸娴偣璁＄畻锛屽洜鑰屾槸杩戜技璁＄畻鑰屼笉鏄噯纭绠楋級銆�
鍦�MySQL 5.1涓紝閲囩敤浜岃繘鍒舵牸寮忎繚瀛�DECIMAL鍒楃殑鍊硷紝灏�9涓崄杩涘埗鏁板瓧鎵撳寘鍦�4瀛楄妭涓�傚浜庢瘡涓�肩殑鏁存暟閮ㄥ垎鍜屽皬鏁伴儴鍒嗭紝鍏跺瓨鍌ㄨ姹傛槸鍒嗗埆纭畾鐨勩�傛瘡9涓暟瀛楅渶瑕�4瀛楄妭锛屼换浣曞墿浣欑殑鏁板瓧灏嗗崰鐢�4瀛楄妭鐨勪竴閮ㄥ垎銆備緥濡傦紝DECIMAL(18,9)鍒楀湪灏忔暟鐐圭殑姣忎竴渚у潎鏈�9浣嶆暟瀛楋紝鍥犳锛屾暣鏁伴儴鍒嗗拰灏忔暟閮ㄥ垎鍧囬渶瑕�4瀛楄妭銆�DECIMAL(20,10)鍒楀湪灏忔暟鐐圭殑姣忎竴渚у潎鏈�10浣嶆暟瀛�銆傚浜庢瘡涓�閮ㄥ垎锛�9浣嶆暟瀛楅渶瑕�4瀛楄妭锛屽墿浣欑殑1浣嶆暟瀛楅渶瑕�1瀛楄妭銆�
鍦ㄤ笅琛ㄤ腑锛岀粰鍑轰簡鍏充簬鍓╀綑鏁板瓧鐨勫瓨鍌ㄨ姹傦細
|
鍓╀綑鐨勬暟瀛� |
瀛楄妭鏁� |
|
0 |
0 |
|
1 |
1 |
|
2 |
1 |
|
3 |
2 |
|
4 |
2 |
|
5 |
3 |
|
6 |
3 |
|
7 |
4 |
|
8 |
4 |
|
9 |
4 |
涓庢煇浜涜緝鏃╃殑MySQL鐗堟湰涓嶅悓锛屽湪MySQL 5.1涓紝DECIMAL鍒椾笉淇濆瓨鍓嶅鈥�+鈥濆瓧绗︽垨鍓嶅鈥�0鈥�鏁板瓧銆傚鏋滃皢+0003.1鎻掑叆DECIMAL(5,1)鍒楋紝灏嗕繚瀛樹负3.1銆備负浜嗛�傚簲璇ュ彉鍖栵紝蹇呴』鏇存敼渚濊禆浜庢棭鏈熻涓虹殑搴旂敤绋嬪簭銆�
鍦�MySQL 5.1涓紝DECIMAL鍒椾笉鍏佽淇濆瓨澶т簬鍒楀畾涔変腑闅愬惈鑼冨洿鐨勫�笺�備緥濡傦紝DECIMAL(3,0)鍒楁敮鎸佺殑鑼冨洿涓�-999锝�999銆傚浜�DECIMAL(M,D)鍒楋紝灏忔暟鐐瑰乏渚ф渶澶氬厑璁�M 鈥�D浣嶆暟瀛楋紙瀹冧笌渚濊禆浜庢棭鏈�MySQL鐗堟湰鐨勫簲鐢ㄧ▼搴忎笉鍏煎锛屽厑璁镐繚瀛橀澶栨暟瀛椾唬鏇库��+鈥濆彿锛夈��
SQL鏍囧噯瑕佹眰锛�NUMERIC(M,D)鐨勭簿搴﹀繀椤诲噯纭负M浣嶆暟瀛椼�傚浜�DECIMAL(M,D)锛屾爣鍑嗚姹傜殑绮惧害鑷冲皯涓�M浣嶆暟瀛楋紝浣嗗厑璁告洿澶氥�傚湪MySQL涓紝DECIMAL(M,D)鍜�NUMERIC(M,D)鏄浉鍚岀殑锛屼袱鑰呯殑绮惧害鍧囧噯纭负M浣嶆暟瀛椼��
瀵逛簬渚濊禆DECIMAL鏁版嵁绫诲瀷鏃╂湡澶勭悊鏂瑰紡鐨勫簲鐢ㄧ▼搴忥紝鍏充簬绉绘杩欑被搴旂敤绋嬪簭鐨勬洿澶氫俊鎭紝璇峰弬瑙�MySQL 5.0鍙傝�冩墜鍐屻��
瀵逛簬绮惧害鏁板锛屽彧瑕佸彲鑳斤紝灏变細浣跨敤缁欏畾鐨勫噯纭�兼暟鍊笺�備緥濡傦紝鍦ㄦ瘮杈冧腑鎵�鐢ㄧ殑鏁板�间笌缁欏畾鐨勫�煎噯纭浉鍚岋紝鏃犱换浣曞彉鍖栥�傚湪涓ユ牸鐨�SQL妯″紡涓嬶紝瀵逛簬鎻掑叆鍏锋湁鍑嗙‘鏁版嵁绫诲瀷锛�DECIMAL鎴栨暣鏁帮級鐨勫垪鐨�INSERT鎿嶄綔锛屽鏋滃�煎湪鍒楃殑鍏佽鑼冨洿鍐咃紝灏嗘彃鍏ュ叿鏈夊噯纭�肩殑鏁板�笺�傛绱㈡椂锛屾墍鑾峰緱鐨勫�间笌鎻掑叆鐨勫�煎簲鏄浉鍚岋紙濡傛灉鏈噰鐢ㄤ弗鏍兼ā寮忥紝鍏佽INSERT鎵ц鎴煭鎿嶄綔锛夈��
瀵规暟鍊艰〃杈惧紡鐨勫鐞嗗彇鍐充簬琛ㄨ揪寮忓寘鍚殑鍊肩殑绫诲瀷锛�
路 濡傛灉瀛樺湪浠讳綍杩戜技鍊硷紝琛ㄨ揪寮忎篃鏄繎浼肩殑锛屽苟灏嗕娇鐢ㄦ诞鐐圭畻娉曡繘琛岃瘎浼般��
路 濡傛灉涓嶅瓨鍦ㄨ繎浼煎�硷紝琛ㄨ揪寮忎粎鍖呭惈鍑嗙‘鍊笺�傚鏋滀换涓�鍑嗙‘鍊煎寘鍚皬鏁伴儴鍒嗭紙灏忔暟鐐瑰悗闈㈢殑鍊硷級锛屽皢浣跨敤DECIMAL鍑嗙‘绠楁硶鏉ヨ绠楄〃杈惧紡锛屽叾绮惧害涓�65浣嶆暟瀛椼�傛湳璇�滃噯纭�濆彈浜岃繘鍒惰〃杩版柟闈㈢殑闄愬埗銆備緥濡傦紝1.0/3.0鍦ㄥ崄杩涘埗琛ㄨ堪涓彲杩戜技涓�.333...锛屼絾骞朵笉鏄噯纭暟鍊硷紝鍥犳(1.0/3.0)*3.0涓嶄細琚绠椾负鍑嗙‘鐨�1.0銆�
路 鍙﹀锛岃〃杈惧紡浠呭寘鍚暣鏁板�笺�傝〃杈惧紡鏄噯纭殑锛屽苟灏嗕娇鐢ㄦ暣鏁扮畻娉曡繘琛岃绠楋紝鍏剁簿搴︿笌BIGINT鐨勭浉鍚岋紙64姣旂壒锛夈��
濡傛灉鏁板�艰〃杈惧紡鍖呭惈浠讳綍瀛楃涓诧紝浼氬皢鍏惰浆鎹负鍙岀簿搴︽诞鐐瑰�硷紝琛ㄨ揪寮忔槸杩戜技鐨勩��
鏁板�煎垪涓殑鎻掑叆鎿嶄綔鍙�SQL妯″紡鐨勫奖鍝嶏紝瀹冩槸鐢�sql_mode绯荤粺鍙橀噺鎺у埗鐨勶紙璇峰弬瑙�1.8.2鑺傦紝鈥滈�夋嫨SQL妯″紡鈥�锛夈�備笅闈粙缁嶄簡涓ユ牸妯″紡锛堢敱STRICT_ALL_TABLES鎴�STRICT_TRANS_TABLES妯″紡鍊奸�夋嫨锛�鍜�RROR_FOR_DIVISION_BY_ZERO銆傝鎯虫墦寮�鎵�鏈夐檺鍒讹紝鍙畝鍗曞湴浣跨敤TRADITIONAL妯″紡锛屽畠鍖呭惈涓ユ牸妯″紡鍜�ERROR_FOR_DIVISION_BY_ZERO锛�
mysql> SET SQL_MODE='TRADITIONAL';
濡傛灉灏嗘暟鍊兼彃鍏ュ叿鏈夊噯纭被鍨嬬殑鍒楋紙DECIMAL鎴栨暣鏁帮級锛屽鏋滃�煎湪鍒楀厑璁哥殑鑼冨洿鍐咃紝灏嗕互鍑嗙‘鍊煎舰寮忔彃鍏ユ暟鍊笺��
濡傛灉鏁板�煎湪鍏跺皬鏁伴儴鍒嗘湁杩囧浣嶏紝灏嗘墽琛屽洓鑸嶄簲鍏ユ搷浣滃苟缁欏嚭鍛婅銆傚叧浜庡洓鑸嶄簲鍏ョ殑鍏蜂綋浠嬬粛锛岃鍙傝鍥涜垗浜斿叆涓�鑺傘��
濡傛灉鏁板�煎湪鍏舵暣鏁伴儴鍒嗘湁杩囧浣嶏紝鏁板�艰繃澶э紝骞跺皢鎸変笅杩版柟寮忓鐞嗭細
路 濡傛灉鏈惎鐢ㄤ弗鏍兼ā寮忥紝璇ユ暟鍊煎皢琚埅鐭负鏈�杩戠殑鍚堟硶鍊硷紝骞跺彂鍑鸿鍛娿��
路 濡傛灉鍚敤浜嗕弗鏍兼ā寮忥紝灏嗙粰鍑烘孩鍑洪敊璇��
涓嶆娴嬩笅婧紝鍥犺�屼笅婧㈠鐞嗘槸涓嶇‘瀹氱殑銆�
榛樿鎯呭喌涓嬶紝闄�0鎿嶄綔浼氬鑷�NULL缁撴灉锛屼笉浜х敓鍛婅銆傚惎鐢ㄤ簡ERROR_FOR_DIVISION_BY_ZERO SQL妯″紡鍚庯紝MySQL浼氫互涓嶅悓鏂瑰紡澶勭悊闄�0闂锛�
路 濡傛灉鏈惎鐢ㄤ弗鏍兼ā寮忥紝鍙戝嚭璀﹀憡銆�
路 濡傛灉鍚敤浜嗕弗鏍兼ā寮忥紝灏嗙姝㈠寘鍚櫎0鎿嶄綔鐨勬彃鍏ュ拰鏇存柊锛屽苟缁欏嚭閿欒銆�
鎹㈠彞璇濊锛屽浜庡寘鍚墽琛岄櫎0鎿嶄綔鐨勮〃杈惧紡鐨勬彃鍏ュ拰鏇存柊锛屽皢琚綋浣滈敊璇寰咃紝浣嗛櫎浜嗕弗鏍兼ā寮忓杩橀渶瑕�ERROR_FOR_DIVISION_BY_ZERO銆�
鍋囧畾涓嬭堪璇彞锛�
INSERT INTO t SET i = 1/0;
瀵逛簬涓ユ牸妯″紡鍜�ERROR_FOR_DIVISION_BY_ZERO妯″紡鐨勭粍鍚堬紝鎯呭喌濡備笅锛�
|
sql_mode鍊� |
缁撴灉 |
|
'' (Default) |
鏃犲憡璀︼紝鏃犻敊璇細i琚缃负NULL銆� |
|
strict |
鏃犲憡璀︼紝鏃犻敊璇細i琚缃负NULL銆� |
|
ERROR_FOR_DIVISION_BY_ZERO |
鍛婅锛屾棤閿欒锛�i琚缃负NULL銆� |
|
strict,ERROR_FOR_DIVISION_BY_ZERO |
閿欒鏉′欢锛屼笉鎻掑叆浠讳綍琛屻�� |
灏嗗瓧绗︿覆鎻掑叆鏁板�煎垪鏃讹紝濡傛灉瀛楃涓插叿鏈夐潪鏁板�煎唴瀹癸紝灏嗘寜涓嬭堪鏂瑰紡灏嗗瓧绗︿覆杞崲涓烘暟鍊硷細
路 瀵逛簬鏈互鏁板�煎紑濮嬬殑瀛楃涓诧紝鍦ㄤ弗鏍兼ā寮忎笅锛屼笉鑳藉皢鍏朵綔涓烘暟鍊间娇鐢紝骞朵細浜х敓閿欒锛屽湪鍏朵粬鎯呭喌涓嬶紝缁欏嚭璀﹀憡銆�鍖呮嫭绌哄瓧绗︿覆銆�
路 瀵逛簬浠ユ暟鍊煎紑濮嬬殑瀛楃涓诧紝鍙互杩涜杞崲锛屼絾灏鹃殢鐨勯潪鏁板�奸儴鍒嗗皢琚埅鍘汇�傚湪涓ユ牸妯″紡涓嬩細瀵艰嚧閿欒锛屽湪鍏朵粬鎯呭喌涓嬶紝缁欏嚭璀﹀憡銆�
鏈妭璁ㄨ浜嗙簿搴︽暟瀛︾殑鍥涜垗浜斿叆鐗规�э紝ROUND()鍑芥暟锛屼互鍙婃彃鍏�DECIMAL鍒楁椂鐨勫洓鑸嶄簲鍏ョ壒鎬с��
ROUND()鍑芥暟鐨勮涓哄彇鍐充簬鍏跺弬閲忔槸鍑嗙‘鐨勮繕鏄繎浼肩殑锛�
路 瀵逛簬鍑嗙‘鍊兼暟鍊硷紝ROUND()閲囩敤鈥滃崐鍊煎悜涓婅垗鍏モ�濊鍒欙細濡傛灉灏忔暟閮ㄥ垎鐨勫�间负.5鎴栨洿澶э紝濡傛灉鏄鏁帮紝鍚戜笂鍙栦笅涓�涓暣鏁帮紝濡傛灉鏄礋鏁帮紝鍚戜笅鍙栦笅涓�涓暣鏁帮紙鎹㈠彞璇濊锛屼互0涓虹晫闄愭墽琛岃垗鍏ワ級銆傚鏋滃皬鏁伴儴鍒嗙殑鍊煎皬浜�.5锛屽鏋滄槸姝ｆ暟锛屽悜涓嬪彇涓嬩竴涓暣鏁帮紝濡傛灉鏄礋鏁帮紝鍚戜笂鍙栦笅涓�涓暣鏁般��
路 瀵逛簬杩戜技鍊兼暟鍊硷紝缁撴灉鍙栧喅浜�C搴撳嚱鏁般�傚湪寰堝绯荤粺涓婏紝瀹冩剰鍛崇潃ROUND()灏嗕娇鐢�鈥�鑸嶅叆鑷虫渶杩戠殑鍋舵暟鈥�瑙勫垯锛氬叿鏈変换浣曞皬鏁伴儴鍒嗙殑鍊�鍧囧皢琚垗鍏ヤ负鏈�杩戠殑鍋舵暟銆�
鍦ㄤ笅闈㈢殑绀轰緥涓紝浠嬬粛浜嗚垗鍏ユ搷浣滃鍑嗙‘鍊煎拰杩戜技鍊肩殑涓嶅悓澶勭悊鏂瑰紡锛�
mysql> SELECT ROUND(2.5), ROUND(25E-1);
+------------+--------------+
| ROUND(2.5) | ROUND(25E-1) |
+------------+--------------+
| 3 | 2 |
+------------+--------------+
瀵逛簬鍚�DECIMAL鍒楃殑鎻掑叆鎿嶄綔锛岀洰鏍囨槸鍑嗙‘鐨勬暟鎹被鍨嬶紝鏃犺瑕佹彃鍏ョ殑鍊兼槸鍑嗙‘鐨勮繕鏄繎浼肩殑锛屽皢閲囩敤鈥滃崐鍊煎悜涓婅垗鍏モ�濊鍒欙細
mysql> CREATE TABLE t (d DECIMAL(10,0)); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t VALUES(2.5),(2.5E0); Query OK, 2 rows affected, 2 warnings (0.00 sec) Records: 2 Duplicates: 0 Warnings: 2 mysql> SELECT d FROM t; +------+ | d | +------+ | 3 | | 3 | +------+
鏈妭缁欏嚭浜嗕竴浜涚ず渚嬶紝浠嬬粛浜�MySQL 5.1涓殑绮惧害鏁板鏌ヨ缁撴灉銆�
绀轰緥1銆傚彲鑳芥椂锛屽皢浣跨敤缁欏畾鐨勫噯纭�硷細
mysql> SELECT .1 + .2 = .3;
+--------------+
| .1 + .2 = .3 |
+--------------+
| 1 |
+--------------+
浣嗘槸锛屽浜庢诞鐐瑰�硷紝缁撴灉鏄笉鍑嗙‘鐨勶細
mysql> SELECT .1E0 + .2E0 = .3E0;
+--------------------+
| .1E0 + .2E0 = .3E0 |
+--------------------+
| 0 |
+--------------------+
鏌ョ湅鍑嗙‘鍊煎拰杩戜技鍊煎鐞嗗樊寮傜殑鍙︿竴涓柟娉曟槸锛屽鍔�1涓皬鐨勬暟鍊硷紝骞跺娆＄疮鍔犮�傝鑰冭檻涓嬭堪瀛樺偍绋嬪簭锛屽畠灏�.0001鍔犲埌鍙橀噺涓�1000娆°��
CREATE PROCEDURE p ()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE d DECIMAL(10,4) DEFAULT 0;
DECLARE f FLOAT DEFAULT 0;
WHILE i < 10000 DO
SET d = d + .0001;
SET f = f + .0001E0;
SET i = i + 1;
END WHILE;
SELECT d, f;
END;
浠庨�昏緫涓婅锛�d鍜�f鐨勫悎璁″簲涓�1锛屼絾浠呭decimal璁＄畻鏉ヨ鏄繖鏍枫�傛诞鐐硅绠椾細寮曞叆灏忕殑璇樊锛�
+--------+------------------+
| d | f |
+--------+------------------+
| 1.0000 | 0.99999999999991 |
+--------+------------------+
绀轰緥2銆備箻娉曟槸鎸夌収鏍囧噯SQL鎵�瑕佹眰鐨勬爣搴︽墽琛屻�備篃灏辨槸璇达紝瀵逛簬鍏锋湁鏍囧害S1鍜�S2鐨勪袱涓暟鍊�X1鍜�X2锛岀粨鏋滅殑鏍囧害涓�S1 + S2锛�
mysql> SELECT .01 * .01;
+-----------+
| .01 * .01 |
+-----------+
| 0.0001 |
+-----------+
绀轰緥3锛氬洓鑸嶄簲鍏ュ畾涔夎壇濂斤細
鍦�MySQL 5.1涓紝鍥涜垗浜斿叆鎿嶄綔锛堜緥濡傦紝浣跨敤ROUND()鍑芥暟锛夌嫭绔嬩簬搴曞眰C搴撳嚱鏁扮殑瀹炴柦锛�杩欐剰鍛崇潃锛屽湪涓嶅悓骞冲彴涓婄粨鏋滄槸涓�鑷寸殑銆�
鍦�MySQL 5.1涓紝瀵逛簬DECIMAL鍒楀拰鍑嗙‘鍊兼暟鍊硷紝閲囩敤浜�鈥�鍗婂�煎悜涓婅垗鍏�鈥�瑙勫垯銆傚浜庡皬鏁伴儴鍒嗙瓑浜庢垨澶т簬0.5鐨勫�硷紝浠�0涓哄垎鐣岃垗鍏ヨ嚦鏈�杩戠殑鏁存暟锛屽涓嬫墍绀猴細
mysql> SELECT ROUND(2.5), ROUND(-2.5);
+------------+-------------+
| ROUND(2.5) | ROUND(-2.5) |
+------------+-------------+
| 3 | -3 |
+------------+-------------+
浣嗘槸锛屽浜庢诞鐐瑰�肩殑鑸嶅叆閲囩敤C搴擄紝鍦ㄥ緢澶氱郴缁熶笂锛�浣跨敤鈥�鑸嶅叆鑷虫渶杩戠殑鍋舵暟鈥�瑙勫垯銆傚湪杩欑被绯荤粺涓婏紝鍏锋湁浠讳綍灏忔暟閮ㄥ垎鐨勫��鍧囧皢琚垗鍏ヤ负鏈�杩戠殑鍋舵暟锛�
mysql> SELECT ROUND(2.5E0), ROUND(-2.5E0);
+--------------+---------------+
| ROUND(2.5E0) | ROUND(-2.5E0) |
+--------------+---------------+
| 2 | -2 |
+--------------+---------------+
绀轰緥4銆傚湪涓ユ牸妯″紡涓嬶紝鎻掑叆杩囧ぇ鐨勫�间細瀵艰嚧婧㈠嚭鍜岄敊璇紝鑰屼笉鏄埅鐭嚦鍚堟硶鍊笺��
褰�MySQL鏈繍琛屽湪涓ユ牸妯″紡涓嬫椂锛屽皢鎴煭鑷冲悎娉曞�硷細
mysql> SET SQL_MODE='';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE t (i TINYINT);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t SET i = 128;
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> SELECT i FROM t;
+------+
| i |
+------+
| 127 |
+------+
1 row in set (0.00 sec)
浣嗘槸锛屽鏋滀弗鏍兼ā寮忚捣浣滅敤锛屽皢鍑虹幇婧㈠嚭鐘跺喌锛�
mysql> SET SQL_MODE='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE t (i TINYINT);
Query OK, 0 rows affected (0.01 sec)
mysql> SET sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.10 sec)
mysql> INSERT INTO t SET i = 128;
ERROR 1264 (22003): Out of range value adjusted for column 'i' at row 1
mysql> SELECT i FROM t;
Empty set (0.00 sec)
绀轰緥5銆傚湪涓ユ牸妯″紡涓嬪苟鍏锋湁ERROR_FOR_DIVISION_BY_ZERO璁剧疆鏃讹紝闄�0浼氬鑷撮敊璇紝鑰屼笉鏄骇鐢�NULL缁撴灉銆�
鍦ㄩ潪涓ユ牸妯″紡涓嬶紝闄�0灏嗗緱鍑�NULL缁撴灉锛�
mysql> SET SQL_MODE='';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE t (i TINYINT);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t SET i = 1 / 0;
Query OK, 1 row affected (0.06 sec)
mysql> SELECT i FROM t;
+------+
| i |
+------+
| NULL |
+------+
1 row in set (0.01 sec)
浣嗘槸锛屽鏋滄伆褰撶殑SQL妯″紡澶勪簬鏈夋晥鐘舵�侊紝闄�0灏嗗鑷撮敊璇細
mysql> SET SQL_MODE='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE t (i TINYINT);
Query OK, 0 rows affected (0.00 sec)
mysql> SET sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t SET i = 1 / 0;
ERROR 1365 (22012): Division by 0
mysql> SELECT i FROM t;
Empty set (0.01 sec)
绀轰緥6銆傚湪MySQL 4涓紙寮曞叆绮惧害鏁板涔嬪墠锛夛紝鍑嗙‘鍊煎拰杩戜技鍊兼枃瀛楀潎浼氳杞崲涓哄弻绮惧害娴偣鍊硷細
mysql> SELECT VERSION();
+-----------------+
| VERSION() |
+-----------------+
| 4.0.25-standard |
+-----------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE t SELECT 2.5 AS a, 25E-1 AS b;
mysql> DESCRIBE t;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a | double(3,1) | | | 0.0 | |
| b | double | | | 0 | |
+-------+-------------+------+-----+---------+-------+
鍦�MySQL 5.1涓紝杩戜技鍊兼枃瀛椾粛浼氳杞崲涓烘诞鐐瑰�硷紝浣嗗噯纭�兼枃瀛楀皢琚綋浣�DECIMAL澶勭悊锛�
mysql> SELECT VERSION();
+-----------------+
| VERSION() |
+-----------------+
| 5.1.2-alpha-log |
+-----------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE t SELECT 2.5 AS a, 25E-1 AS b;
mysql> DESCRIBE t;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| a | decimal(2,1) | NO | | 0.0 | |
| b | double | NO | | 0 | |
+-------+--------------+------+-----+---------+-------+
绀轰緥7銆傚鏋滆仛鍚堝嚱鏁扮殑鍙傞噺鏄噯纭殑鏁板�肩被鍨嬶紝鍏剁粨鏋滀篃鏄噯纭殑鏁板�肩被鍨嬶紝鏍囧害鑷冲皯涓哄弬閲忕殑鏍囧害銆�
鑰冭檻涓嬭堪璇彞锛�
mysql> CREATE TABLE t (i INT, d DECIMAL, f FLOAT);
mysql> INSERT INTO t VALUES(1,1,1);
mysql> CREATE TABLE y SELECT AVG(i), AVG(d), AVG(f) FROM t;
鍦�MySQL 4.0鎴�4.1锛堝湪MySQL涓紩鍏ョ簿搴︽暟瀛︿箣鍓嶏級涓殑缁撴灉锛�
mysql> DESCRIBE y;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| AVG(i) | double(17,4) | YES | | NULL | |
| AVG(d) | double(17,4) | YES | | NULL | |
| AVG(f) | double | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
鏃犺鍙傞噺绫诲瀷鏄粈涔堬紝缁撴灉涓�double銆�
鍦�MySQL 5.1涓殑缁撴灉锛�
mysql> DESCRIBE y;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| AVG(i) | decimal(14,4) | YES | | NULL | |
| AVG(d) | decimal(14,4) | YES | | NULL | |
| AVG(f) | double | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
浠呭娴偣鍙傞噺锛屽叾缁撴灉涓�double銆�瀵逛簬鍑嗙‘绫诲瀷鍙傞噺锛岀粨鏋滀篃涓哄噯纭被鍨嬨��
杩欐槸MySQL鍙傝�冩墜鍐岀殑缈昏瘧鐗堟湰锛屽叧浜嶮ySQL鍙傝�冩墜鍐岋紝璇疯闂�dev.mysql.com銆傚師濮嬪弬鑰冩墜鍐屼负鑻辨枃鐗堬紝涓庤嫳鏂囩増鍙傝�冩墜鍐岀浉姣旓紝鏈炕璇戠増鍙兘涓嶆槸鏈�鏂扮殑銆�