MySQL Datatype Question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Bigun
Forum Contributor
Posts: 237
Joined: Tue Jun 13, 2006 10:50 am

MySQL Datatype Question

Post by Bigun »

I would like to store a number in a field that will have two decimal places at most. I'm currently using a varchar datatype, but it sees 9 higher than 10 because of the whole alphanumeric sort order. What datatype should I use?
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post by Oren »

Try TINYINT.
Bigun
Forum Contributor
Posts: 237
Joined: Tue Jun 13, 2006 10:50 am

Post by Bigun »

It's not saving the decimal points...

Code: Select all

8.75


saves as

Code: Select all

8
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

As it is a float number, you want o be looking at float, double or decimal datatypes
Bigun
Forum Contributor
Posts: 237
Joined: Tue Jun 13, 2006 10:50 am

Post by Bigun »

The highest the number will be going is 10, and it will not be going below zero. Which is the most conservative?
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

probably FLOAT(2, 2)
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post by Oren »

Bigun wrote:It's not saving the decimal points...

Code: Select all

8.75


saves as

Code: Select all

8
Sorry... I read too fast and somehow thought you wanted numbers like 0 - 99. Sorry for that :P
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Re: MySQL Datatype Question

Post by AKA Panama Jack »

Bigun wrote:I would like to store a number in a field that will have two decimal places at most. I'm currently using a varchar datatype, but it sees 9 higher than 10 because of the whole alphanumeric sort order. What datatype should I use?
Use the DECIMAL datatype. You can specify the number of places on both sides of the decimal point. Plus the DECIMAL datatype doesn have the FAILINGS of the FLOAT datatype when dealing with decimal places.

If the highest number is 10 then set the DECIMAL datatype length to 4,2. This means there are 4 positions and 2 of the positions are right of the decimal point. IE: 00.00

If you set it to 2,2 thinking that means 2 positions to the right and 2 to the left of the decimal point then trying to store a value of 10 will actually store 0.99. This is because the length for the DECIMAL datatype is "total number of positions, how many of the total to the right of the decimal point".
Post Reply