MySQL – How to Update Column Values to Integers

MySQLmysql-5.6

I have a column in my table that I need to change the data type from VARCHAR to DOUBLE. Now I cannot convert it to DOUBLE data type easily because the values on that column have numbers and strings. 

> 0 41  14:16:57
  ALTER TABLE `ControlAutomation`.`constantcomponentstabular`
     DROP COLUMN `String`,
     CHANGE COLUMN `ConstantValue` `ConstantValue` DOUBLE NULL DEFAULT NULL

> Error Code: 1265. Data truncated for column 'ConstantValue' at row 217966 1.891 sec

I know what is causing this issue, its because there are strings on the column that I want to change to DOUBLE.

Here is the solution that I have in mind using Python

  1. Query the table.
  2. Check whether the value can be converted to float.
  3. If not, UPDATE value of that column to 0.

This process takes a long time, My question is how to best change those values to any number so I will be able to ALTER the table.

I am using MySQL 5.6.32 and InnoDB as storage engine.

Best Answer

Let me suggest to add a new column and then update this new column before to drop the old one.

  1. Backup, backup, backup

create table mytable (ConstantValue varchar(100));

insert into mytable values
('1252.36'),('abc'),('55969'),('def'),('66955663'),('A8879'),('-41.25'),('+636');
  1. Add a new column with a default value of 0.

alter table mytable
    add column NewConstantValue double default(0);
  1. Set alphanumeric values to 0. I've borrowed this regex expression from MySql forums

update mytable
set    ConstantValue = '0'
where  ConstantValue not regexp '^(-|\\+)?([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';
  1. Update new column with current values.

update mytable
set    NewConstantValue = cast(ConstantValue as double);
  1. Check new values before to drop old column.

select * from mytable;

ConstantValue | NewConstantValue
:------------ | ---------------:
1252.36       |          1252.36
0             |                0
55969         |            55969
0             |                0
66955663      |         66955663
0             |                0
-41.25        |           -41.25
+636          |              636
  1. Drop ConstantValue column.

alter table mytable
    drop column ConstantValue;
  1. Rename new column as ConstantValue.

alter table mytable
    change column NewConstantValue ConstantValue double default(0);
  1. Check final result.

select * from mytable;

| ConstantValue |
| ------------: |
|       1252.36 |
|             0 |
|         55969 |
|             0 |
|      66955663 |
|             0 |
|        -41.25 |
|           636 |

dbfiddle here

Related Question