Automatically trimming extra zeros in mysql

mysql> select 28/4;
+--------+
| 28/4   |
+--------+
| 7.0000 |
+--------+
1 row in set (0.00 sec)

Is there a way I can set something system-wide to be more clever about this, or at least to automatically round eve开发者_如何学运维ry float to 2 places?

That's controlled by the div_precision_increment variable:

• http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_div_precision_increment

MySQL> select ceiling(28/4);
+---------------+
| ceiling(28/4) |
+---------------+
|             7      |
+---------------+
1 row in set (0.00 sec)

MySQL> select format(28/4,0);
+----------------+
| format(28/4,0) |
+----------------+
|       7              |
+----------------+
1 row in set (0.00 sec)

I GOT IT !!!

set div_precision_increment = 0;

MySQL> select 28/4;
+------+
| 28/4 |
+------+
| 7 |
+------+
1 row in set (0.00 sec)

You could also set it globally:

set global div_precision_increment = 0;

or add this to /etc/my.cnf

div_precision_increment=0

I think your answer, if it exists, lies on this page

I have to be honest, I read through the rules about how precision is applied and don't really understand how you end up with 5 digits of precision by diving 28 by 4.

Good luck!