For details about the floating-point to serial-type problems in MySQL, mysql floating-point

For details about the floating-point to serial-type problems in MySQL, mysql floating-point

Preface

This article mainly introduces a problem that MySQL encountered when converting the floating point type to the standby type. I will share it for your reference and learning. I will not talk about it here. Let's take a look at the detailed introduction.

I. Problem Description

Today, we have a requirement for data refreshing: to modify the product weight (the field type is float). After modifying the product weight, is to be recorded in the log table (the field type is varchar), the table structure is as follows:

Temporary data table flushing:

Create table 'temp _ 100' ('id' int (10) unsigned not null AUTO_INCREMENT COMMENT 'Primary key id', 'goods _ sn 'varchar (170830) not null default ''comment' product Code', 'goods _ weight 'float (9, 4) not null default '0. 0000 'comment' product weight', 'actual _ weight 'float () not null default '0. 0000 'comment' actual weight', 'new _ actual_weight 'float () not null default '0. 0000 'comment' new actual weight', 'create _ user' varchar (30) not null default ''comment' creator ', primary key ('id '), KEY 'idx _ goods_sn '('goods _ sn') ENGINE = InnoDB AUTO_INCREMENT = 8192 default charset = utf8 COMMENT = 'temporary weight refresh table ';

Log table:

Create table 'Log _ weight '('id' int (10) unsigned not null AUTO_INCREMENT COMMENT 'Primary key id', 'goods _ ss' varchar (50) not null default ''comment' product Code', 'which _ col' varchar (100) not null default ''comment' modify field', 'old _ value' varchar (50) not null default '0. 00 'comment' pre-update value', 'new _ value' varchar (50) not null default '0. 00 'comment' value after update ', 'Update _ user' varchar (100) not null default ''' comment' creator ', 'Update _ time' datetime not null default CURRENT_TIMESTAMP ON update CURRENT_TIMESTAMP, 'wh _ update_time 'timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'record modification time ', primary key ('id'), KEY 'idx _ goods_sn '('goods _ sn'), KEY 'idx _ update_user '('Update _ user '), KEY 'wh _ update_time '('wh _ update_time') ENGINE = InnoDB AUTO_INCREMENT = 14601620 default charset = utf8 COMMENT = 'weight change log ';

As shown in the preceding table, I Need To fl the actual_weight and new_actual_weight fields of the temp_170830 table into the old_value and new_value fields of the log_weight table respectively. The SQL statement is as follows:

INSERT INTO log_weight(goods_sn, which_col, old_value, new_value, update_user)SELECT goods_sn,'actual_weight',actual_weight,new_actual_weight,create_user FROM temp_170830;

I thought it was done by now. After all, I just inserted some log records. Later, I found some problems with the data for simple check, as shown in:

Temporary table data:

Log table data:

The comparison shows that the inserted log record data is followed by many decimal places for no reason and does not know where it came from. Later I thought it would be possible that the original floating-point data was not enough, when converting to varchar, we will also bring out the following ones. It is not very certain for the time being. We will add them after confirmation, and then we will find a temporary concat Method for converting to varchar, the adjustment is as follows:

INSERT INTO log_weight(goods_sn, which_col, old_value, new_value, update_user)SELECT goods_sn,'actual_weight',concat(actual_weight,''),concat(new_actual_weight,''),create_user FROM temp_170830;

Solved the log record problem smoothly.

Summary:

1 When recording the price and weight numeric fields, try not to use the floating point type !!!, There are many floating point pitfalls (for example, the floating point type cannot be equal !!!), It is best to use an int integer. When the business needs to display decimal places, read them and divide them by the corresponding digits. For example, 99.98 yuan should be stored, and 9998 should be used for display when reading the data.

2 when float is converted to varchar, you should first convert float to varchar using the concat function, and then store it into the varchar field.

Well, the above is all the content of this article. I hope the content of this article has some reference and learning value for everyone's learning or work. If you have any questions, please leave a message to us, thank you for your support.