Control MySQL Digital conversion

in the actual work we often need to format the numbers, such as the 12.0073233 into 12.01 , or put A into 12.00 , or put A into 0000012 , the conversion between this format is summarized as follows:

One, floating point conversion--Directly set the field information in the database

MySQLtwo data types are available:Numericand thedecimal(You can approximate the two without distinction), both of these data types can easily solve the above problem:
NUMERICand theDECIMALtype isMySQLimplemented in the same type, whichSQL92is allowed in the standard. They are used to hold values that have important requirements for accurate precision, such as money-related data. When declaring a column with one of them, the precision and range of values can be(is usually)be specified; For example:salary DECIMAL (5,2)in this case,5 (accuracy(precision))represents the number of important decimal digits,2 (Data Range(scale))represents the number of digits after the decimal point. In this case, therefore,SalaryA column can store a range of values from-99.99to the99.99. (actuallyMySQLThe values that can be stored in this column can be999.99, because it does not store a positive sign). Mwith theDtheDECIMAL (M, D)The range of values is affected (when the range is exceededMySQLis automatically processed, which results in inaccurate data), so the size range of the stored data to consider when building the table.

Specify field information directly when building a table, or create a table to update field information:

To Create a table: CREATE table test_number(a numeric(ten, 2), b DECIMAL(ten,2 ) )engine MyISAM charset UTF8;

Insert data: Span style= "Color:blue" > INSERT INTO Test_number values ( 12.06722 , 13.223343 );

Find data: Select * from test_number;

The results are as follows:

As you can see, the data is stored in the required format in the database.

To view the CREATE TABLE procedure: show CREATE table test_number;

The results are as follows:

Note that inside MySQL, all are built in decimal form, so it can be said that numeric and decimal are the same

two , floating-point conversion--formatting the query data

1.format

can be done by MySQL of the FORMAT ( x,d ) You can control the data X the decimal point is D bit.

CREATE TABLE: CREATE table Test_number2 ( a float ) engine MyISAM charset utf8 ;

Insert Data: insert into test_number2 values ( 12.06722);

General Enquiry: Select * from test_number2;

The number of query bits is more than the original insert: Select Format(a,ten) from test_number2 ;

The number of query bits is less than the original insert: Select Format(a,2) from test_number2 ;

As you can see, the format of the query data can be implemented in this way, but there is still a problem

problem with format

Test SELECT FORMAT (12562.6655,2);

Results: 12,562.67

View documentation: formats the number X to a format like ' #,###,###.## ' , rounded to D decimal places, and returns the result as a string. If D is 0, the result has a no decimal point or fractional part. and the result returned is String type string Type, no longer a numeric type.

Do not achieve the expected results, do not want the results separated by commas,

2,truncate

Select truncate (4545.1366,2);

Results: 4545.13 , Direct interception does not round , or there is a problem.

3. Convert

Select CONVERT (4545.1366,decimal(2);

Results: 4545.14 , the results are still data, so you should use this method when you format the data query

Select Convert(a,decimal) from test_number2 ;

Select Convert(a,decimal(2 , ) ) from test_number2;

Conversion of integers--direct setting of field information in a database

syntax example:

CREATE table Stu ( stu_id int ( 5 ) unsigned Zerofill NOT null default 0 primary Key class_id int ( 5 ) 。

Four, integer number conversion--The query data is formatted

Select Lpad(stu_id,0 ') Lpad( class_id,ten,' 0 ') from Stu;

Select Lpad(stu_id,0), Lpad( class_id,0) from Stu;

 

Control MySQL Digital conversion