Solving some problems in converting floating point type to character type in MySQL

Type conversion is a requirement that we often encounter in daily development. Recently, we encountered a problem when converting floating point type into character type, so I will summarize and share it. The following article mainly introduces it to you. Friends in need can refer to the relevant information about the problems that may be encountered when converting floating point to character in MySQL.

Preface

This article mainly introduces you to a problem encountered when converting floating point type to character type in MySQL. Share It’s here for everyone’s reference and study. I won’t say much more, let’s take a look at the detailed introduction.

1 Problem description

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

Temporarily refresh the data table:

CREATE TABLE `temp_170830` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
 `goods_sn` varchar(255) NOT NULL DEFAULT '' COMMENT '产品编码',
 `goods_weight` float(9,4) NOT NULL DEFAULT '0.0000' COMMENT '产品重量',
 `actual_weight` float(9,4) NOT NULL DEFAULT '0.0000' COMMENT '实际重量',
 `new_actual_weight` float(9,4) NOT NULL DEFAULT '0.0000' COMMENT '新的实际重量',
 `create_user` varchar(30) NOT NULL DEFAULT '' COMMENT '创建人',
 PRIMARY KEY (`id`),
 KEY `idx_goods_sn` (`goods_sn`)
) ENGINE=InnoDB AUTO_INCREMENT=8192 DEFAULT CHARSET=utf8 COMMENT='临时刷重量表';

Log table:

CREATE TABLE `log_weight` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
 `goods_sn` varchar(50) NOT NULL DEFAULT '' COMMENT '产品编码',
 `which_col` varchar(100) NOT NULL DEFAULT '' COMMENT '修改字段',
 `old_value` varchar(50) NOT NULL DEFAULT '0.00' COMMENT '更新前值',
 `new_value` varchar(50) NOT NULL DEFAULT '0.00' COMMENT '更新后值',
 `update_user` varchar(100) NOT NULL DEFAULT '' COMMENT '创建人',
 `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 '记录修改时间',
 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='重量修改日志';

As shown in the table built above, I need to change the actual_weight of the temp_170830 table and new_actual_weight fields are flushed 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 I was done here. After all, I just inserted some log records. Later, for a simple verification, I found that something was wrong with the data, as shown in the figure below:

Temporary table data screenshot:

Screenshot of log table data:

By comparison, it can be found that the inserted log record data has many decimal places at the end for no reason. I don’t know where it comes from. Where did it come from? Then I thought about it, maybe the original floating point data cannot be divided, and when it was converted to varchar, the following ones were also brought out. I am not sure at the moment. I will confirm it later. Supplement, and then I temporarily found a way to convert varchar to 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;

Smoothly solved the logging problem.

The summary is as follows:

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

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

The above is the detailed content of Solving some problems in converting floating point type to character type in MySQL. For more information, please follow other related articles on the PHP Chinese website!