Detailed explanation of possible problems in converting floating point data to character data in MySQL

Preface

This article mainly introduces a problem encountered when converting floating point types to character types in MySQL. It is shared for your reference and learning. Without further ado, let's take a look at the detailed introduction.

1. Problem Description

Today I encountered a data refresh requirement, which is to modify the weight of the product (the field type is float). After the weight of the product is modified, it needs to be recorded in the log table (the field type is varchar). The table structure is as follows:

Temporary refresh data table:

CREATE TABLE `temp_170830` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key ID',
 `goods_sn` varchar(255) NOT NULL DEFAULT '' COMMENT 'Product code',
 `goods_weight` float(9,4) NOT NULL DEFAULT '0.0000' COMMENT 'Product weight',
 `actual_weight` float(9,4) NOT NULL DEFAULT '0.0000' COMMENT 'Actual weight',
 `new_actual_weight` float(9,4) NOT NULL DEFAULT '0.0000' COMMENT 'New actual weight',
 `create_user` varchar(30) NOT NULL DEFAULT '' COMMENT 'Created by',
 PRIMARY KEY (`id`),
 KEY `idx_goods_sn` (`goods_sn`)
) ENGINE=InnoDB AUTO_INCREMENT=8192 DEFAULT CHARSET=utf8 COMMENT='Temporary refresh weight table';

Log table:

CREATE TABLE `log_weight` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key ID',
 `goods_sn` 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 'Value before update',
 `new_value` varchar(50) NOT NULL DEFAULT '0.00' COMMENT 'Updated value',
 `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 modification log';

As shown in the table created above, I need to flush 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 statements are 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 I had finished here, after all, I just inserted some log records. Later, for a simple check, I found that the data was a little wrong, as shown in the following figure:

Screenshot of temporary table data:

Log table data screenshot:

By comparison, we can find that the inserted log record data has many more decimal places at the end for no reason. I don't know where they come from. Later, I thought that it might be because the floating point data is not divisible. When it is converted to varchar, the decimal places at the end are also brought out. I am not sure for the time being. I will add more after I confirm it. Then I found a method of converting to varchar, concat, and adjusted it 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;

Successfully solved the logging issue.

To summarize:

1 When recording price and weight numeric fields, try not to use floating point types! ! ! , floating-point numbers have many pitfalls (for example, floating-point types cannot be judged as equal!!!), so it is best to use int integer type. When decimals need to be displayed in business, read them out and divide them by the corresponding number of digits. For example, 99.98 yuan should be stored as 9998, and when read out, use 9998/100 to display it.

2 When converting float to varchar, you should first convert the float to varchar using the concat function and then store it in the varchar field.

Well, the above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.