Help storing data in mysql-Collection of common programming errors

11 years ago
admin
1 minute

I need to store a salary in a mysql database, I cannot work out what data type I need to store it as, basically I want to store them in a similar format to this, 22,000 or 25,500.

  1. Strongly advice you to treat the salaries as ‘float’ and use runtime-formatting to add the commas.

    But otherwise as far as I know you’ll have to resort to TEXT for a data type.

  2. I would store the salaries as DECIMAL(10,2) and then, when showing the salaries FORMAT them. This way you could sort, search and do math operations (SUM) correctly. Avoid using FLOAT for money. It will save you a lot of headackes – storing money amounts in mysql

  3. If you are intending to only store the salary details as 22,000, then you can simply go for a varchar or nvarchar type. However if you are using this column in some analytics or calculation, then either you will need to convert the column datatype in code as and when required. That depends on how many places you will be using or querying for this column for calculation.