How to use MySQL DECIMAL?

by

DOUBLE columns are not the same as DECIMAL columns, and you will get in trouble if you use DOUBLE columns for financial data.

DOUBLE is actually just a double precision (64 bit instead of 32 bit) version of FLOAT. Floating point numbers are approximate representations of real numbers and they are not exact. In fact, simple numbers like 0.01 do not have an exact representation in FLOAT or DOUBLE types.

DECIMAL columns are exact representations, but they take up a lot more space for a much smaller range of possible numbers. To create a column capable of holding values from 0.0001 to 99.9999 like you asked you would need the following statement

CREATE TABLE your_table
(
    your_column DECIMAL(6,4) NOT NULL
);

The column definition follows the format DECIMAL(M, D) where M is the maximum number of digits (the precision) and D is the number of digits to the right of the decimal point (the scale).

This means that the previous command creates a column that accepts values from -99.9999 to 99.9999. You may also create an UNSIGNED DECIMAL column, ranging from 0.0000 to 99.9999.

As an example, if you want a column that accepts values from -9999.99 to 9999.99 the command would be DECIMAL(6,2). As you can see, you still use a precision of 6, but only allow a scale of 2.

For more information on MySQL DECIMAL the official docs are always a great resource.

Bear in mind that all of this information is true for versions of MySQL 5.0.3 and greater. If you are using previous versions, you really should upgrade.

Update on MySQL 8.0.17+

Unsigned is deprecated for FLOAT, DOUBLE, and DECIMAL columns.

More Related Contents:

  1. Out of memory Microsoft VBScript runtime error 800a0007
  2. Left Outer Join doesn’t return all rows from my left table?
  3. Are table names in MySQL case sensitive?
  4. MySQL: is a SELECT statement case sensitive?
  5. 1114 (HY000): The table is full
  6. Get table names using SELECT statement in MySQL
  7. UPDATE multiple tables in MySQL using LEFT JOIN
  8. Can a foreign key reference a non-unique index?
  9. How to raise an error within a MySQL function
  10. What is maximum query size for mysql?
  11. Storing Data in MySQL as JSON
  12. Finding all parents in mysql table with single query (Recursive Query)
  13. How do I retrieve my MySQL username and password?
  14. How to execute a MySQL command from a shell script?
  15. Select most common value from a field in MySQL
  16. Returning the ‘last’ row of each ‘group by’ in MySQL
  17. How does a MYSQL Self-Join Work?
  18. How to change the default port of mysql from 3306 to 3360
  19. Is it possible to reference one column as multiple foreign keys?
  20. No Entity Framework provider found for ‘MySql.Data.MySqlClient’ ADO.NET provider
  21. How to Import Multiple csv files into a MySQL Database
  22. MySQL error #2014 – Commands out of sync; you can’t run this command now
  23. Row to column transformation in MySQL
  24. Data truncated for column?
  25. MySQL concurrency, how does it work and do I need to handle it in my application
  26. mysql select id and name from other table and join query
  27. How can I “select *” from a table in MySQL but omit certain columns?
  28. For homebrew mysql installs, where’s my.cnf?
  29. Force MySQL to return duplicates from WHERE IN clause without using JOIN/UNION?
  30. comparison of truncate vs delete in mysql/sqlserver [duplicate]

Leave a Comment