How to force MySQL/MariaDB to show numbers in scientific notation?

Question

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.

Update:

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 ;

Answer 1

Yes, this seems a known issue, which probably wasn't never acknowledged much because after all, in most cases, a float is displayed/processed by the application code and not represented directly by mysqld/mysql client (and seems that workbench suffers from the same problem).

Sheeri from Mozilla seems to have discovered this problem before when running pt-table-checksum and shows a workaround for its run, but not a general solution. However, on a further discussion, she seems to provide a possible option, although with the opposite format that you want (converted into decimal instead of scientific notation.

I have just arranged a quick calculation to generate the scientific format out of a decimal number, change @n for your column name in your case:

SET @n := 0.00123; 

SELECT 
   IF(@n=0, 0, 
       CONCAT(IF(@n<0, '-', ''), 
              FORMAT(abs(@n)/pow(10, floor(log10(abs(@n)))), 6), 
              'E', 
              FORMAT(floor(log10(abs(@n))), 0)
             )
     ); 

EDIT: This is the function:

CREATE FUNCTION sci(n DOUBLE) 
RETURNS char(12) DETERMINISTIC
RETURN IF(n=0, '0.000000E0',
          CONCAT(IF(n<0, '-', ''),
                 FORMAT(abs(n)/pow(10, floor(log10(abs(n)))), 6),
                 'E',
                 FORMAT(floor(log10(abs(n))), 0)
                )
         )

Answer 2

Because I need to compare the value of Excel file, I modified the function from @jynus (Thanks!!!)

CREATE FUNCTION sci(n DOUBLE) 
RETURNS char(22) DETERMINISTIC
RETURN IF(n=0, '0.00E+00',
          CONCAT(IF(n<0, '-', ''),
                 TRIM(TRAILING '0' FROM SUBSTRING(abs(n)/pow(10, floor(log10(abs(n)))), 1, 16)),
                 'E',
                 FORMAT(floor(log10(abs(n))), 0)
                )
         )
;

-- some test cases
SELECT IF(sci(0) = '0.00E+00', 'ok', 'err');
SELECT IF(sci(10150108846695600) = '1.01501088466956E16', 'ok', 'err');
SELECT IF(sci(10150108846695635) = '1.01501088466956E16', 'ok', 'err');
SELECT IF(sci(1002717526414881) = '1.00271752641488E15', 'ok', 'err');
SELECT IF(sci(1005642422781826) = '1.00564242278182E15', 'ok', 'err');
SELECT IF(sci(1385012201817068) = '1.38501220181706E15', 'ok', 'err');
SELECT IF(sci(1496331643940903) = '1.4963316439409E15', 'ok', 'err');