Geek Answers Handbook

Disable scientific notation in MySQL command line client?

I have a MySQL table with many numeric columns (some INT, some FLOAT). I would like to query it with the MySQL command line client (in particular mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (x86_64) using readline 6.1), for example:

SELECT * FROM table WHERE foo;

Unfortunately, if the value of any number field exceeds 10 ^ 6, this client displays the result in scientific notation, which makes it difficult to read the results.

I could fix the problem by FORMATTING each of the fields in my query, but there are many of them and many tables that I would like to query. Instead, I hope to find a client variable or flag that I can set to turn off scientific notation for all queries.

I was not able to find it on the page --helpor man, and also not to search on Google or on this site. Instead, all I find is a discussion of saving / deleting scientific notation when using <insert-programming-language>the MySQL API.

Thanks for any advice.

:: change ::

Here is an example table ...

mysql> desc foo;
+--------------+-------------+------+-----+-------------------+
| Field        | Type        | Null | Key | Default           |
+--------------+-------------+------+-----+-------------------+
| date         | date        | NO   | PRI | NULL              |
| name         | varchar(20) | NO   | PRI | NULL              |
| val          | float       | NO   |     | NULL              |
| last_updated | timestamp   | NO   |     | CURRENT_TIMESTAMP |
+--------------+-------------+------+-----+-------------------+

and some approximate values ​​...

mysql> select * from foo where date='20120207';
+------------+--------+--------------+---------------------+
| date       | name   | val          | last_updated        |
+------------+--------+--------------+---------------------+
| 2012-02-07 | A      |      88779.5 | 2012-02-07 13:38:14 |
| 2012-02-07 | B      |  1.00254e+06 | 2012-02-07 13:38:14 |
| 2012-02-07 | C      |      78706.5 | 2012-02-07 13:38:15 |
+------------+--------+--------------+---------------------+

Now the actual values ​​loaded in the third field are as follows:

88779.5, 1002539.25, 78706.5390625

and they can be seen exactly if I manipulate the value:

mysql> select date, name, ROUND(val, 10), last_updated from foo where ...
+------------+---+--------------------+---------------------+
| 2012-02-07 | A |   88779.5000000000 | 2012-02-07 13:38:14 |
| 2012-02-07 | B | 1002539.2500000000 | 2012-02-07 13:38:14 |
| 2012-02-07 | C |   78706.5390625000 | 2012-02-07 13:38:15 |

Something in the client seems to ensure that I am only allowed to see six significant digits, although there are more in the table.

If request, for example

mysql> select ROUND(*, 2) from foo ...

were possible, that would be great! Otherwise, I cannot take the time to individually wrap 100 column names in "ROUND ()" whenever I need to check some data.

, phpMyAdmin , 6 . , CLI.

+5
3

, , , 6 - FLOAT (, ).

, ( ) , DECIMAL (16,4) FLOAT. , , 4 ( "0" ). , - .

0

CAST , DECIMAL? , , .

0

, , .. .

create view foo2 as select date, name, ROUND(val, 10) val, last_updated from foo

foo2. phpmyadmin

0

More articles:


All Articles