MySQL Datatype Question
Moderator: General Moderators
MySQL Datatype Question
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?
- AKA Panama Jack
- Forum Regular
- Posts: 878
- Joined: Mon Nov 14, 2005 4:21 pm
Re: MySQL Datatype Question
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.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?
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".