3

I'm trying to find the best column type to use for numeric values of varying length both before and after the decimal place.

The DECIMAL type seems to only allow a fixed-length number with a fixed-length also after the decimal. I need something that preserves to exact precision numbers like:

  • 1.50
  • 222.05
  • 124.2584879775435298
  • 5344.87987797797979077

I can't find anything other than varchar that clearly accommodates this. Am I missing something?

Works for a Living
  • 1,262
  • 2
  • 19
  • 44
  • see this. This might help you http://stackoverflow.com/questions/425389/c-sharp-equivalent-of-sql-server-datatypes – user1627167 Feb 20 '15 at 05:51
  • Thanks. Looking at that chart in the accepted answer, and then comparing those to what's available in MySQL, it still seems like varchar is my only option. :/ – Works for a Living Feb 20 '15 at 06:01

1 Answers1

0

Do you need these numbers for future numeric operations? If not, then you should be able to store the data as a variable length character string so VARCHAR could work.

here this website may answer your questions:

http://dev.mysql.com/doc/refman/5.6/en/numeric-types.html

user3690249
  • 63
  • 1
  • 8
  • I do math on the numbers every time the row is updated. Is there a problem doing basic math on varchar numbers? – Works for a Living Feb 20 '15 at 07:42
  • Thanks for the link. I actually read that whole page, and a bunch of others, before asking the question here. – Works for a Living Feb 20 '15 at 07:44
  • 1
    As I saw it, that if you set them as numeric values such as DECIMAL the DB would know the result you wanted upon adding subtracting etc, would be of that type no conversion would be necessary : http://www.dummies.com/how-to/content/how-to-store-different-types-of-mysql-data.html – user3690249 Feb 20 '15 at 16:04
  • 1
    The issue with setting VARCHAR, I Know mySQL does an automatic cast from a character string to a numeric value for you, that it might not be the exact format you wanted...say you want a DECIMAL format of a certain length and number of places after the decimal to result from the arithmetic operation it might auto cast this result to an INT type instead. I found these websites about using the CONVERT() and CAST() methods for data. : http://dba.fyicenter.com/faq/sql_server/CONVERT_Converting_Character_Strings_to_Numeric_Values.html – user3690249 Feb 20 '15 at 16:06
  • 1
    http://www.geeksengine.com/database/single-row-functions/conversion-functions.php I think essentially they meant if you were wanting the programmer to use the data for nothing more than retrieval processes through queries that it wouldn't need any conversions thus to display in the manner you want above with varying lengths then VARCHAR would work just fine. – user3690249 Feb 20 '15 at 16:07
  • Maybe this will help!? – user3690249 Feb 20 '15 at 16:08