1

I have a table with a decimal column with a lenght = 9 and decimals = 2.

If I put a value of 21.59 (for example) it works ok.

If I put 52.00 it writes only 52. I need to keep 52.00 instead.

Master question: Can the database store the value this way? Instead of using format/cast in select to retrieve the value...

As noted bellow, this make sense:

"You shouldn't worry about display formatting issues at the database level but at the ... display level"

Khrys
  • 2,670
  • 9
  • 49
  • 82

1 Answers1

4

Use the FORMAT function:

select format(mycolumn, 2) from mytable;

This also has the effect of adding thousand's separator into the number, so you would get output like 123,456.70. There are workarounds if this doesn't work for you.

Given that MySQL doesn't have the world's best facilities for formatting numbers, display issues like this are usually handled in client code.

Joni
  • 108,737
  • 14
  • 143
  • 193
  • 2
    OP wants to **store** the information with the decimals, not merely display it – hd1 Mar 22 '13 at 19:36
  • 1
    The information is already being stored. The problem is that it is not being *displayed*. – Joni Mar 22 '13 at 19:38
  • 1
    @hd1 `.00` is superfluous information. There should be no reason to save it in the database. – JJJ Mar 22 '13 at 19:38
  • I agree with @Juhana. Databases do try to help you. Imagine you have one million numbers with `.00`. Thats 3 million additional and empty bytes clogging up your harddrive and slowing down your database. That's a LOT of stuff to remember. in any case, reformat the number in your `view` – Unamata Sanatarai Mar 22 '13 at 19:55
  • 1
    Actually, those `.00` are already there. They occupy less than 1 byte in total though. Since version 5.0.3 MySQL stores numbers using a binary representation and not as strings. See http://dev.mysql.com/doc/refman/5.0/en/precision-math-decimal-changes.html – Joni Mar 22 '13 at 20:06