MySQL Default float field length

If you specify a column type as float but you specify NO length, what does the length default to?

Richard KortsBusiness Owner / Chief DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
It appears that you get whatever the appropriate return value is.  It looks like it will be stored as a 8-byte/64-bit floating point value.  'length' is not the appropriate term but 'precision' may be.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NerdsOfTechTechnology ScientistCommented:
Specifying m=0 (zero width, technically "precision" in this case as it is with DOUBLE)  defaults FLOAT to UNSIGNED and 0 (zero) with SINGLE precision (4 bytes).

Since calculations in mySQL are exclusively double precision, FLOAT in SINGLE precision form is a type normally avoided at all costs.
NerdsOfTechTechnology ScientistCommented:
Also, M is the total number of digits and D is the number of digits following the decimal point. If M and D are both omitted, values are stored to the limits permitted by the hardware.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Steve BinkCommented:
@NerdOfTech: RE:M=0, where is that presented in the docs?  

According to the page you sited, FLOAT defaults to "the limits of the hardware" when M and D are not specified.  DECIMAL defaults to (10,0).  

This also means the default FLOAT is stored in 4 bytes.
Steve BinkCommented:
In my experiment, FLOATs default to signed.  It is also limited to 6 significant digits, but I'm unsure if that is hitting a hardware limit or some internal decision by MySQL.
mysql> create table stuff (m float, n float(6,0), o float (4,4));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into stuff (m,n,o) values  (123456789.123456789, 123456789.123456789, 123456798.123456798),(-123456789.123456789,-12345679.123456789,-123456789.123456789);
Query OK, 2 rows affected, 4 warnings (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 4

mysql> select * from stuff;
| m          | n       | o       |
|  123457000 |  999999 |  0.9999 |
| -123457000 | -999999 | -0.9999 |
2 rows in set (0.00 sec)

Open in new window

Dave BaldwinFixer of ProblemsCommented:
I get slightly different results but it's an old version of MySQL.
1.23457e+08 	999999 	0.9999
-1.23457e+08 	-999999 	-0.9999

Open in new window

NerdsOfTechTechnology ScientistCommented:
I erred. Yes it is stored to the hardware's max precision. Also default looks to be signed. That's what I get for assuming and not testing. Thanks you two other experts for running the tests. Also both of your results looked to be the same (other than m being in scientific notation for the last example)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.