Note that there are some explanatory texts on larger screens.

plurals
  1. PODisable scientific notation in MySQL command-line client?
    text
    copied!<p>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 (specifically, <code>mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (x86_64) using readline 6.1</code>), like so:</p> <pre><code>SELECT * FROM table WHERE foo; </code></pre> <p>Unfortunately, if the value of any numeric field exceeds 10^6, this client displays the result in scientific notation, which makes reading the results difficult.</p> <p>I could correct the problem by FORMAT-ing each of the fields in my query, but there are many of them and many tables I would like to query. Instead I'm hoping to find a client variable or flag I can set to disable scientific notation for all queries.</p> <p>I have not been able to find one in the <code>--help</code> or the <code>man</code> page, nor searching Google or this site. Instead all I find are discussions of preserving/removing scientific notation when using <code>&lt;insert-programming-language&gt;</code>'s MySQL API.</p> <p>Thank you for any tips.</p> <p>::edit::</p> <p>Here's an example table ...</p> <pre><code>mysql&gt; 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 | +--------------+-------------+------+-----+-------------------+ </code></pre> <p>and some example values ...</p> <pre><code>mysql&gt; 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 | +------------+--------+--------------+---------------------+ </code></pre> <p>Now, the actual values I loaded into the third field are:</p> <pre><code>88779.5, 1002539.25, 78706.5390625 </code></pre> <p>and they can be seen exactly if I manipulate the value:</p> <pre><code>mysql&gt; 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 | </code></pre> <p>Something in the client seems to be enforcing that I only be allowed to see six significant figures, even though there are more in the table.</p> <p>If a query such as</p> <pre><code>mysql&gt; select ROUND(*, 2) from foo ... </code></pre> <p>were possible, that would be great! Otherwise I can't really take the time to individually wrap 100 column names in "ROUND()" whenever I need to inspect some data.</p> <p>Interestingly, I occasionally use a phpMyAdmin interface to browse the contents of some of these tables, and that interface also has this 6 significant figure limitation. So it's not limited to just the CLI.</p>
 

Querying!

 
Guidance

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload