I have a local MariaDB server 5.5.37 where I have some numeric data in it stored as DOUBLE. I need to perform some queries on this data (things like AVG(...) or STD(...)), and I also have a MySQL server 5.1.73, where I have similar data stored using the same schema.
Here's the problem: when I run the same query on the MySQL server, I get results in scientific notation (e.g. 1.267e-13), while the same query on the MariaDB server gives 0.000000000001267. I'd like to be able to explicitly determine the display format, and in particular, I want the scientific notation.
The query is something like:
SELECT AVG(some_field), STD(some_field) FROM some_table GROUP BY other_field;
(actually much more complicated than that)
This happens with both the mysql command line client, and with MySQL Workbench.
I ended up defining my own function to convert from DOUBLE to a string in scientific notation, based on the answers below, which should work correctly for negative numbers:
drop function if exists sci; DELIMITER $$ create function sci (num DOUBLE) returns text deterministic language sql begin set @exp = IF(num=0, 0, FLOOR(LOG10(ABS(num)))); return CONCAT(FORMAT(num*POWER(10,-@exp),6),'e',@exp); end$$ DELIMITER ;