How to use MySQL DECIMAL

Question

I can t quite get a grasp of MySQL s DECIMAL  I need the row to be able to contain a number anywhere from 00 0001 to 99 9999  How would I structure it to work like so

User · Answer

MySQL 5 x specification for decimal datatype is  DECIMAL  M  D     UNSIGNED   ZEROFILL   The answer above is wrong  now corrected  in saying that unsigned decimals are not possible  To define a field allowing only unsigned decimals  with a total length of 6 digits  4 of which are decimals  you would use  DECIMAL  6 4  UNSIGNED  You can likewise create unsigned  ie  not negative  FLOAT and DOUBLE datatypes   Update on MySQL 8 0 17   as in MySQL 8 Manual  11 1 1 Numeric Data Type Syntax    quot Numeric data types that permit the UNSIGNED attribute also permit SIGNED  However  these data types are signed by default  so the SIGNED attribute has no effect   As of MySQL 8 0 17  the UNSIGNED attribute is deprecated for columns of type FLOAT  DOUBLE  and DECIMAL  and any synonyms   you should expect support for it to be removed in a future version of MySQL  Consider using a simple CHECK constraint instead for such columns

User · Answer

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   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

User · Answer

Although the answers above seems correct  just a simple explanation to give you an idea of how it works   Suppose that your column is set to be DECIMAL 13 4   This means that the column will have a total size of 13 digits where 4 of these will be used for precision representation   So  in summary  for that column you would have a max value of  999999999 9999

User · Answer

There are correct solutions in the comments  but to summarize them into a single answer   You have to use DECIMAL 6 4    Then you can have 6 total number of digits  2 before and 4 after the decimal point  the scale   At least according to this

[mysql] How to use MySQL DECIMAL?

Examples related to mysql