To be able to insert a decimal number in MySQL, you need to ensure that the column type is one of the following:

- DECIMAL (or NUMERIC)
- FLOAT
- DOUBLE (or REAL)

You can add one of these new columns to an existing table with MySQL with something similar to:

```
ALTER TABLE table_name ADD COLUMN price DECIMAL(4,2);
```

or if you need to change an existing column:

```
ALTER TABLE table_name MODIFY COLUMN price DECIMAL(4,2);
```

Then to actually insert a new row (assuming we have just `id`

and `price`

), it is as easy as:

```
INSERT INTO table_name (id, price) VALUES (123, 987.44);
```

However, before just choosing a column type make sure you know if you want a **fixed-point** or **floating-point** data type.

`DECIMAL`

is a *fixed-point* type which means that it stores the exact numeric data values. You will want this when you want to preserve the exact precision, such as with a monetary value (dollars, pounds, etc). It allows for up to 65 significant digits. This is good for business math. The drawback? Performance is worse and they take more storage space. In the SQL syntax, the quotes contain two numbers, the first being the **precision**, and the second being the **scale**:

When defining this ensure that you provide enough precision to accommodate the largest numbers you expect. For `DECIMAL`

, precision is the portion before the decimal (does not count digits after decimal). For the scale, this is the portion after the decimal. If you are using money you would simply use 2, but if your needs are different then you might adjust this accordingly.

`FLOAT`

and `DOUBLE`

is a *floating-point* type that stores approximate numeric data values. You will want to use this when approximation is sufficient. It uses four bytes for `float`

(up to 7 significant digits) and eight bytes for `double`

(up to about 16 significant digits). These are not a good type to use when you need to do **exact** comparisons. They also suffer from rounding errors which can lead to inaccuracies. They are good for any scientific calculations where values don't need to be exact. They are also great for performance and storage efficiency because they require less processing power and memory. In the SQL syntax, this is similar to how it was done with `DECIMAL`

, but the second parameter is optional (and not a standard syntax). Additionally, the precision (first number in parenthesis) is the **total** numbers that can be stored (before and after the decimal), and the second number restricts how many can be after the decimal. That is a little bit different than how it works with `DECIMAL`

.

`int`

and multiply any values you want to store by 100 first and divide by 100 whenever you retrieve values. However, that's not the right way to do things since there is a specific feature for this. — Mas Sehguh