Best data type to store money values in MySQL

mysql sqldatatypes currency
Mohammad Saberi picture Mohammad Saberi · Oct 23, 2012 · Viewed 268.6k times · Source

I want to store many records in a MySQL database. All of them contains money values. But I don't know how many digits will be inserted for each one.
Which data type do I have to use for this purpose?
VARCHAR or INT (or other numeric data types)?

Answer

juergen d picture juergen d · Oct 23, 2012

Since money needs an exact representation don't use data types that are only approximate like float. You can use a fixed-point numeric data type for that like

decimal(15,2)
  • 15 is the precision (total length of value including decimal places)
  • 2 is the number of digits after decimal point

See MySQL Numeric Types:

These types are used when it is important to preserve exact precision, for example with monetary data.

Related questions

Should I use the datetime or timestamp data type in MySQL?

Would you recommend using a datetime or a timestamp field, and why (using MySQL)? I'm working with PHP on the server side.

Read More
Which MySQL data type to use for storing boolean values

Since MySQL doesn't seem to have any 'boolean' data type, which data type do you 'abuse' for storing true/false information in MySQL? Especially in the context of writing and reading from/to a PHP script. Over time I have …

Read More
MySql: Tinyint (2) vs tinyint(1) - what is the difference?

I knew boolean in mysql as tinyint (1). Today I see a table with defined an integer like tinyint(2), and also others like int(4), int(6) ... What does the size means in field of type integer and tinyint ?

Read More