benjam Posted September 28, 2007 Share Posted September 28, 2007 I am pulling data from a table and want the numbers to be output in decimal notation, but MySQL keeps outputting the numbers in scientific notation. Is there a way (and I'm sure it's a simple one) to force MySQL to use decimal notation instead? Quote Link to comment https://forums.phpfreaks.com/topic/71107-solved-force-mysql-to-use-decimal-numbers-instead-of-sci-notation/ Share on other sites More sharing options...
fenway Posted September 29, 2007 Share Posted September 29, 2007 I've never heard of such a thing... what query? Quote Link to comment https://forums.phpfreaks.com/topic/71107-solved-force-mysql-to-use-decimal-numbers-instead-of-sci-notation/#findComment-357689 Share on other sites More sharing options...
deadimp Posted September 30, 2007 Share Posted September 30, 2007 How are you outputting the results from the MySQL query? Are you using the command line tool, PHP, ...? Quote Link to comment https://forums.phpfreaks.com/topic/71107-solved-force-mysql-to-use-decimal-numbers-instead-of-sci-notation/#findComment-358210 Share on other sites More sharing options...
benjam Posted September 30, 2007 Author Share Posted September 30, 2007 Outputting to command line. The data gets inserted in, then some simple arithmetic when it gets pulled out. I apologize, I don't have any test data for you to look at. But apparently, my wife (the person with the problem), switched the data type to DEC from FLOAT and it worked fine, giving all returns in decimal notation. Why would that be, I thought they were the same? Quote Link to comment https://forums.phpfreaks.com/topic/71107-solved-force-mysql-to-use-decimal-numbers-instead-of-sci-notation/#findComment-358421 Share on other sites More sharing options...
fenway Posted October 1, 2007 Share Posted October 1, 2007 Outputting to command line. The data gets inserted in, then some simple arithmetic when it gets pulled out. I apologize, I don't have any test data for you to look at. But apparently, my wife (the person with the problem), switched the data type to DEC from FLOAT and it worked fine, giving all returns in decimal notation. Why would that be, I thought they were the same? With no test data, not test output and no query... we're not going to get anyhere. Quote Link to comment https://forums.phpfreaks.com/topic/71107-solved-force-mysql-to-use-decimal-numbers-instead-of-sci-notation/#findComment-358984 Share on other sites More sharing options...
benjam Posted October 1, 2007 Author Share Posted October 1, 2007 Well, the problem is no longer an issue, as it was 'fixed' by switching the data type from FLOAT to DEC. Now I just want to satiate my curiosity, and for future reference. But here is some sample data, the query, and some sample return data: (Please note that bsyn and tsyn are basically the same tables with some differences in the Z values) CREATE TABLE bsyn ( X FLOAT NULL, Y FLOAT NULL, Z FLOAT NULL, ) ; INSERT INTO bsyn VALUES ('455526.272000','1644214.016000','5613.504598') , ('455529.088000','1644226.176000','5613.766831') , ('455531.904000','1644238.208000','5614.029063') , ('455534.720000','1644250.496000','5614.291295') , ('455537.536000','1644262.656000','5614.553528') , ('455540.352000','1644274.816000','5614.815760') , ('455543.168000','1644286.976000','5615.077992') , ('455545.984000','1644299.264000','5615.340224') ; SELECT bsyn.X , bsyn.Y , bsyn.Z AS z1 , tsyn.Z AS z2 , bsyn.Z - tsyn.Z AS diff FROM bsyn , tsyn WHERE bsyn.X = tsyn.X AND bsyn.Y = tsyn.Y ; +--------+--------------+---------+---------+------------------+ | x | y | z1 | z2 | diff | +--------+--------------+---------+---------+------------------+ | 456187 | 1.64708e+006 | 5797.5 | 5644 | 153.50244140625 | | 456190 | 1.64709e+006 | 5798.31 | 5645.12 | 153.1904296875 | | 456193 | 1.6471e+006 | 5799.12 | 5646.24 | 152.8779296875 | | 456196 | 1.64711e+006 | 5800 | 5647.37 | 152.63623046875 | | 456199 | 1.64712e+006 | 5800.88 | 5648.49 | 152.39501953125 | | 456201 | 1.64714e+006 | 5801.76 | 5649.61 | 152.15283203125 | | 456204 | 1.64715e+006 | 5802.64 | 5650.73 | 151.9111328125 | | 456207 | 1.64716e+006 | 5803.52 | 5651.85 | 151.6689453125 | | 456210 | 1.64717e+006 | 5804.4 | 5652.97 | 151.42724609375 | | 456213 | 1.64719e+006 | 5805.28 | 5654.09 | 151.185546875 | | 456216 | 1.6472e+006 | 5806.16 | 5655.21 | 150.94384765625 | | 456218 | 1.64721e+006 | 5807.04 | 5656.33 | 150.7021484375 | +--------+--------------+---------+---------+------------------+ Quote Link to comment https://forums.phpfreaks.com/topic/71107-solved-force-mysql-to-use-decimal-numbers-instead-of-sci-notation/#findComment-359218 Share on other sites More sharing options...
deadimp Posted October 4, 2007 Share Posted October 4, 2007 This deals with the MySQL command line console rather than raw data retrieval from MySQL itself. Sadly, I don't use the console (at all), so I don't know how to change the formatting. Quote Link to comment https://forums.phpfreaks.com/topic/71107-solved-force-mysql-to-use-decimal-numbers-instead-of-sci-notation/#findComment-361377 Share on other sites More sharing options...
fenway Posted October 4, 2007 Share Posted October 4, 2007 I'll take a look... Quote Link to comment https://forums.phpfreaks.com/topic/71107-solved-force-mysql-to-use-decimal-numbers-instead-of-sci-notation/#findComment-362028 Share on other sites More sharing options...
fenway Posted October 7, 2007 Share Posted October 7, 2007 From the manual: The FLOAT and DOUBLE data types are used to represent approximate numeric data values. The DECIMAL and NUMERIC data types are used to store exact numeric data values. To be honest, I've never used FLOAT ever... so I guess I didn't know. The easy way to cheat is the use CAST( field AS decimal) -- but only in MySQL 5 -- otherwise, use yourField+0, which forces an implicit converstion back to decimal. HTH. Quote Link to comment https://forums.phpfreaks.com/topic/71107-solved-force-mysql-to-use-decimal-numbers-instead-of-sci-notation/#findComment-364058 Share on other sites More sharing options...
benjam Posted October 9, 2007 Author Share Posted October 9, 2007 I didn't know that either, thanks for the help. Quote Link to comment https://forums.phpfreaks.com/topic/71107-solved-force-mysql-to-use-decimal-numbers-instead-of-sci-notation/#findComment-365122 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.