0

My apology with my English. I am currently trying to create a database in putty, for words we use varchar, but what about if we want to insert number? for example i am creating a car database which include the year of the making, the price, the making and the model of the car.

What is the correct syntax for price, i didn't do the right code for it as i receive an error?

Thanks!

this is my code:

CREATE TABLE cars(
  cars_id int(10) UNSIGNED not null AUTO_INCREMENT PRIMARY KEY,
  make VARCHAR(25) not null,
  model VARCHAR(25) not null,
  price(value) null float,
  yom varchar(25) not null
);
Sammitch
  • 30,782
  • 7
  • 50
  • 77
Tyler Matema
  • 65
  • 1
  • 3
  • 9
  • 1
    MySQL provides a number of numeric types - each designed for storing different sized numbers. Docs are at http://dev.mysql.com/doc/refman/5.0/en/integer-types.html. For example: price INT, yom TINYINT – phil-lavin Oct 15 '13 at 22:41
  • 3
    For money data use NUMERIC or DECIMAL.See this [answer](http://stackoverflow.com/questions/13030368/best-data-type-to-store-money-values-in-mysql) – david strachan Oct 15 '13 at 23:07
  • Thanks guys, help me a lot, i will have to check mysql manual more often. – Tyler Matema Oct 15 '13 at 23:41

2 Answers2

0

For storing prices you do not want to use a FLOAT. They are reliant on rounding and can have variable precision based on your system's hardware and/or software configuration.

http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html

Prices have a fixed decimal which is why you need a fixed decimal data type like DECIMAL.

To declare a DECIMAL column you'll need to specify the total number of digits, and the number of decimal places you'd like.

eg: to store $199.95 the column would need to be DECIMAL(5,2).

These are essentially stored as an integer 19995 and the decimal point is added in on retrieval. In this way you do not need to worry about gaining/losing cents randomly due to floating point precision issues.

Sammitch
  • 30,782
  • 7
  • 50
  • 77
-1

For Float Types, you have to specify the total number of digits max, and the number of digits on the right of the decimal, separated by a comma. So, for the highest price of 999999.99 you would specify:

price FLOAT(8,2) NULL

Which means 8 digits total, 2 to the right of the decimal.

If you have cars in the millions (eg, 1378599.99), then we would need price FLOAT(9,2) NULL.

Jason
  • 1,987
  • 1
  • 14
  • 16
  • so for price it would be price(9,2) null? – Tyler Matema Oct 15 '13 at 23:01
  • My bad, I totally fell for your price(value) scheme. Fixed now lol. – Jason Oct 15 '13 at 23:04
  • **Do not use FLOAT for storing prices!** You will randomly gain/lose cents/fractions of a currency unit. [Floating point numbers](http://dev.mysql.com/doc/refman/5.0/en/floating-point-types.html) are imprecise and rely on rounding. Use [`DECIMAL(9,2)`](http://dev.mysql.com/doc/refman/5.0/en/fixed-point-types.html) where mySQL will store an *integer* values with *no rounding* and present it with a decimal in the proper place. – Sammitch Oct 15 '13 at 23:37
  • Well, thanks for the down-vote, Sammitch. While you're right, the OP did ask about syntax, not preferred type. – Jason Oct 15 '13 at 23:51