CODEWITHSUNDEEP
mysqlcsvloading
mlwacosmos
mlwacosmos

Reputation: 4561

Load numbers in MySQL field

I have a MySQL table with some Decimal(4,2) fields.

I have a CSV file with numbers like 1485.23 to load in those fields.

After loading the file, the values in those fields is 99.99, like a max value to put inside.

Upvotes: 0

Views: 91

Answers (2)

mitkosoft
mitkosoft

Reputation: 5316

Just alter your table in order to receive decimals more than 4 digits in total.

DECIMAL(4,2) means a number with 4 digits in total, 2 of them after floating point, so 99.99 will be maximum allowed.

DECIMAL(9,3) will receive numbers with 9 digits in total, 3 of them after floating point.

Upvotes: 3

Vipin Jain
Vipin Jain

Reputation: 3756

The declaration syntax for a DECIMAL column is DECIMAL(M,D). The ranges of values for the arguments in MySQL 5.7 are as follows:

M is the maximum number of digits (the precision). It has a range of 1 to 65.

D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M.

The maximum value of 65 for M means that calculations on DECIMAL values are accurate up to 65 digits. This limit of 65 digits of precision also applies to exact-value numeric literals, so the maximum range of such literals differs from before.

Example

In a DECIMAL column declaration, the precision and scale can be (and usually is) specified; for example:

salary DECIMAL(5,2)

In this example, 5 is the precision and 2 is the scale. The precision represents the number of significant digits that are stored for values, and the scale represents the number of digits that can be stored following the decimal point.

Standard SQL requires that DECIMAL(5,2) be able to store any value with five digits and two decimals, so values that can be stored in the salary column range from -999.99 to 999.99.

for more info https://dev.mysql.com/doc/refman/5.7/en/fixed-point-types.html

Upvotes: 2

Related Questions