mysql storing problem

Question

i have a column like decimal(2,1) , its working well, but when i try to store a value like 10 it stores as 9.9 ..any specific reason?

Answer 1

Yes, when you say 2,1 what you really say is: this column can store two digits, and one of them can be after the decimal point. If you would like to be able to save 10.1 you would use DECIMAL(3,1), since 10.1 is 3 digits total.

It will throw a warning, BTW: mysql> SHOW CREATE TABLE t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE t ( a decimal(2,1) DEFAULT '0.0' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

mysql> INSERT INTO t (a) VALUES (10); Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Warning Code: 1264-- Message: Out of range value for column 'a' at row 1 1 row in set (0.00 sec)

mysql>

--

Edit: And when running in STRICT mode (http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_traditional) it will actually throw a warning.

Answer 2

Your decimal field is two digits in width and one of those digits is to the right of the decimal point. That means there is only one digit to the left of the decimal point. You can't possibly store "10" in that field.