批量更新mysql表的十进制值并更改列字段类型

Currently the balance_amount column in MySQL is using double type, and it contains values such as 11.28839999999999. I want to round such values to 2 decimal places to become 11.29, and I want to run a mass update to all the other 80 tables in the system so that it will show 2 decimal places only. After which, I want to change it from double to decimal (10,2) type.

目前MySQL中的balance_amount列使用double类型,它包含诸如11.28839999999999之类的值。我想将这些值舍入到2个小数位以变为11.29,并且我想对系统中的所有其他80个表运行批量更新,以便它仅显示2个小数位。之后,我想将它从double更改为十进制(10,2)类型。

CREATE TABLE IF NOT EXISTS `account` (
  `account_id` varchar(15) NOT NULL,
  `description` varchar(100) DEFAULT NULL,
  `balance_amount` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

So how can I achieve it?

那么我怎样才能实现呢?

1 个解决方案

#1

You can round the corresponding columns in each table to 2 decimal places using:

您可以使用以下方法将每个表中的相应列舍入到2个小数位:

Update <table-name> SET <column-name> = ROUND(<column-name>, 2);

Then alter the corresponding column in each table to be a DECIMAL(10, 2):

然后将每个表中的相应列更改为DECIMAL(10,2):

ALTER TABLE `<table-name>` CHANGE COLUMN `<column-name>` `<column-name>` DECIMAL(10,2) NULL DEFAULT NULL ;

But this is something you should be doing in your maintenance window, not live hours and you should backup your database as a disaster management measure before doing this.

但这是您应该在维护窗口中进行的操作,而不是实时工作,您应该在执行此操作之前备份数据库作为灾难管理措施。

#1