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;
create function sci (num DOUBLE) returns text deterministic language sql
  set @exp = IF(num=0, 0, FLOOR(LOG10(ABS(num))));
  return CONCAT(FORMAT(num*POWER(10,-@exp),6),'e',@exp);

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; 

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

EDIT: This is the function:

RETURN IF(n=0, '0.000000E0',
          CONCAT(IF(n<0, '-', ''),
                 FORMAT(abs(n)/pow(10, floor(log10(abs(n)))), 6),
                 FORMAT(floor(log10(abs(n))), 0)
  • Please note that Rolando's solution will return null for 0 or negative numbers as of the writing of this lines.
    – jynus
    Sep 9 '14 at 22:08
  • Right you are. Never once though about NULLs and negatives. +1 !!! Sorry reached my voting limit already. Sep 9 '14 at 22:09
  • Thanks! Yes, it's good to keep in mind that numbers can be negative, although in my case they just happen to be always positive. I wonder if it's possible to somehow define a function instead of having to repeat the whole multi-line calculation every time?
    – Daniele
    Sep 9 '14 at 22:20
  • @Daniele yes, that would be the cleanest way.
    – jynus
    Sep 9 '14 at 22:21
  • @Daniele I have added such a function.
    – jynus
    Sep 10 '14 at 8:09

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

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)),
                 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');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.