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銆傚師濮嬪弬鑰冩墜鍐屼负鑻辨枃鐗堬紝涓庤嫳鏂囩増鍙傝冩墜鍐岀浉姣旓紝鏈炕璇戠増鍙兘涓嶆槸鏈鏂扮殑銆