MySQL/MariaDB – How to Show Numbers in Scientific Notation

mariadbMySQLmysql-workbench

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 ;

Best Answer

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)
                )
         )
Related Question