SitePoint Sponsor 

User Tag List
Results 1 to 6 of 6
Thread: Storing decimal values in MySQL

Jun 27, 2001, 16:19 #1
 Join Date
 Aug 1999
 Location
 Pittsburgh, PA, USA
 Posts
 3,910
 Mentioned
 0 Post(s)
 Tagged
 0 Thread(s)
Storing decimal values in MySQL
Simple enough (I hope): how can I order by a field (in MySQL) containing decmical values. Right now. 9.88 shows up ahead of 14.90. The field is TEXT, because I've found that the INT field type strips all data after the decimal when it's entered into the database. Is there another field type I can use, perhaps?

Jun 27, 2001, 16:37 #2
 Join Date
 Jun 2000
 Location
 Sydney, Australia
 Posts
 3,798
 Mentioned
 0 Post(s)
 Tagged
 0 Thread(s)
An integer is by definition a whole number. Real numbers you can chose from the following types:
from : http://www.mysql.com/doc/C/o/Column_types.html
FLOAT(precision) [ZEROFILL]
A floatingpoint number. Cannot be unsigned. precision can be <=24 for a singleprecision floatingpoint number and between 25 and 53 for a doubleprecision floatingpoint number. These types are like the FLOAT and DOUBLE types described immediately below. FLOAT(X) has the same range as the corresponding FLOAT and DOUBLE types, but the display size and number of decimals is undefined. In MySQL Version 3.23, this is a true floatingpoint value. In earlier MySQL versions, FLOAT(precision) always has 2 decimals. Note that using FLOAT may give you some unexpected problems as all calculation in MySQL is done with double precision. See section 21.19 Solving Problems with No Matching Rows. This syntax is provided for ODBC compatibility.
FLOAT[(M,D)] [ZEROFILL]
A small (singleprecision) floatingpoint number. Cannot be unsigned. Allowable values are 3.402823466E+38 to 1.175494351E38, 0, and 1.175494351E38 to 3.402823466E+38. The M is the display width and D is the number of decimals. FLOAT without an argument or with an argument of <= 24 stands for a singleprecision floatingpoint number.
DOUBLE[(M,D)] [ZEROFILL]
A normalsize (doubleprecision) floatingpoint number. Cannot be unsigned. Allowable values are 1.7976931348623157E+308 to 2.2250738585072014E308, 0, and 2.2250738585072014E308 to 1.7976931348623157E+308. The M is the display width and D is the number of decimals. DOUBLE without an argument or FLOAT(X) where 25 <= X <= 53 stands for a doubleprecision floatingpoint number.
DOUBLE PRECISION[(M,D)] [ZEROFILL]
REAL[(M,D)] [ZEROFILL]
These are synonyms for DOUBLE.
DECIMAL[(M[,D])] [ZEROFILL]
An unpacked floatingpoint number. Cannot be unsigned. Behaves like a CHAR column: ``unpacked'' means the number is stored as a string, using one character for each digit of the value. The decimal point and, for negative numbers, the `' sign, are not counted in M (but space for these are reserved). If D is 0, values will have no decimal point or fractional part. The maximum range of DECIMAL values is the same as for DOUBLE, but the actual range for a given DECIMAL column may be constrained by the choice of M and D. If D is left out it's set to 0. If M is left out it's set to 10. Note that in MySQL Version 3.22 the M argument had to includes the space needed for the sign and the decimal point.
NUMERIC(M,D) [ZEROFILL]
This is a synonym for DECIMAL.
for the storage requirement of each of these types consult: http://www.mysql.com/doc/n/o/node_178.html

Jun 27, 2001, 16:47 #3
 Join Date
 Aug 1999
 Location
 Pittsburgh, PA, USA
 Posts
 3,910
 Mentioned
 0 Post(s)
 Tagged
 0 Thread(s)
Bingo: changed the column to FLOAT. You the man. Thanks Sid.

Jun 27, 2001, 16:54 #4
 Join Date
 Jun 2000
 Location
 Sydney, Australia
 Posts
 3,798
 Mentioned
 0 Post(s)
 Tagged
 0 Thread(s)
You should prefer DOUBLE. As the manual says FLOAT is only provided for ODBC compatability.

Jun 27, 2001, 17:31 #5
 Join Date
 Aug 1999
 Location
 Pittsburgh, PA, USA
 Posts
 3,910
 Mentioned
 0 Post(s)
 Tagged
 0 Thread(s)
Oh. Is FLOAT inferior in some way then?

Jun 27, 2001, 17:56 #6
 Join Date
 Jun 2000
 Location
 Sydney, Australia
 Posts
 3,798
 Mentioned
 0 Post(s)
 Tagged
 0 Thread(s)
From my earier post:
Note that using FLOAT may give you some unexpected problems as all calculation in MySQL is done with double precision. See section 21.19 Solving Problems with No Matching Rows. This syntax is provided for ODBC compatibility.
Anyway, these are not important issues most of the time. But if the manual tells me to prefer DOUBLE over FLOAT then that's good enough for me
Bookmarks