3 Replies - 970 Views - Last Post: 01 May 2009 - 05:30 PM

#1 ktsirig  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 94
  • Joined: 14-September 05

Question on numeric data types in Mysql

Post icon  Posted 30 April 2009 - 03:50 PM

Hello all!
I have a field in my Mysql db which can store various numeric types, like 123, -45.74423, 23e-56 etc, and for those numbers, I can't know if they will be float, integers, positive or negative or how many decimal digits they may have.
My question is, should I use something like VARCHAR (100) as data type for this column?
If, in a query, I want to select all numbers that are >50 for example, will the comparison work or there will be a problem because I would have stored the numbers as VARCHAR?

Please advice!

Is This A Good Question/Topic? 0
  • +

Replies To: Question on numeric data types in Mysql

#2 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Question on numeric data types in Mysql

Posted 30 April 2009 - 07:04 PM

Can't hurt to try, if no error msg just audit your results and see if it worked as you want. If the comparison treats everything as text then I don't think you will like the results (100 would sort before 2). A quick check would be to open the table and sort based on that field. Wait, that's Access thinking, you can open and view a table and sort records in MySQL DB?
Was This Post Helpful? 0
  • +
  • -

#3 ktsirig  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 94
  • Joined: 14-September 05

Re: Question on numeric data types in Mysql

Posted 01 May 2009 - 01:52 AM

Hi, thank you for your answer!
But my question remains: will these data types you suggested be able to store numbers like -1235456.754 or 645E-45 or 23.6 without any problem? As I said before, I CAN'T know beforehand what kind of number will the user enter, how many integer digits will it have, how many decimal places or if it will be negative or positive... And I MUST store the number exactly as it will be provided.
Was This Post Helpful? 0
  • +
  • -

#4 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Question on numeric data types in Mysql

Posted 01 May 2009 - 05:30 PM

I have never used MySQL so I can't say for sure what would happen. Whether a Varchar field would recognize 645E-45 as scientific notation and store a number or if would store as a string the literal characters, don't know. Suggest building a table with several fields of datatype Double and Varchar and entering data to test. A Text field type would save the literal characters but then data would sort as alpha not numbers. I would think a number field would recognize the scientific notation and then sorting would be numeric. But when you retrieve the data and display in form/report boxes, will take the format you set or whatever the object can acommodate. 45 decimals would possibly force the scientific notation format automatically.

This post has been edited by June7: 01 May 2009 - 05:33 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1