First of all, for things with higher accuracy, such as money, I would use the decimal type, do not consider float,double, because they are prone to error,
The declaration syntax for a decimal column is decimal (m,d). In MySQL 5.1, the parameters are evaluated as follows:
· M is the maximum number of digits (precision). The range is 1~65 (in older versions of MySQL, the allowable range is 1~254).
· D is the number (scale) of the digits to the right of the decimal point. Its scope is 0~30, but not more than M.
Note: Float is 4 bytes, double is 8 bytes, and Decimail (m,d) occupies m+2 bytes.
The maximum value for decimal (5, 2) is 9 9 9 9. 9 9 Because there are 7 bytes available.
Translator Note:
Effects of M and D on the range of decimal (M, d) values
Type description range of values (MySQL < 3.23) range (MySQL >= 3.23)
The code is as follows |
Copy Code |
DECIMAL (4, 1)-9.9 to 99.9-999.9 to 9999.9 DECIMAL (5, 1)-99.9 to 999.9-9999.9 to 99999.9 DECIMAL (6, 1)-999.9 to 9999.9-99999.9 to 999999.9 DECIMAL (6, 2)-99.99 to 999.99-9999.99 to 99999.99 DECIMAL (6, 3)-9.999 to 99.999-999.999 to 9999.999 |
# in MySQL 3.23 and later versions, the range of decimal (M, d) is equal to the value range of decimal (M + 2, D) in earlier versions.
There is also a kind of data type is also good: that is Longblob, this data type can be directly to the image file into the database!
The author, recently in the study of MySQL data type of decimal, the results of the experimental data are published as follows
Database version: Server version:5.0.45 Source Distribution
1. CREATE TABLE structure
The code is as follows |
Copy Code |
CREATE Table Ta (a float,b decimal (10,5));
|
2. Inserting data
The code is as follows |
Copy Code |
Insert into TA (a,b) values (1,12345.123423);
|
The actual B-column data inserted is: 12345.12342
The code is as follows |
Copy Code |
Insert into TA (a,b) values (1,123456.1234); |
The actual B-column data inserted is: 99999.99999
Conclusion: Decimal Data type,
1. When the value of the integral part of the insertion exceeds the range of its representation, the value of the decimal part is ignored and filled with the maximum value.
2, when the integer part of the legal, fractional number of redundant digits, directly truncated.