Converting varchar column values into integer in MySql

Question

We have a table in MySql , in that table we have a varchar column called 'type'.

Table having around 50 million records.

select distinct log_type from logs limit 3;

+-------------------+

| type              |

+-------------------+

| EMAIL_SENT        |

| EMAIL_OPEN        |

| AD_CLICKED        |                   

+-------------------+

as of now we have 70 distinct types. In future we will have more types.

now we want to convert this varchar column into integer column.

Just we want to update like following

for EMAIL_SENT I will use 1
for EMAIL_OPEN I will use 2
and so on

and then result column will be like following

+-------------------+

| type (int)        |

+-------------------+

| 1                 |

| 2                 |

| 3                 |                   

+-------------------+

We are also changing few other columns so we are creating new table and load all values from existing table like following

insert into new_table select * from old_table

how can we do this varchar to int conversion while loading values into new table.

Answer 1

Just use a giant case:

insert into new_table(LogTypeId, . . .)
    select (case logtype
                 when 'EMAIL_SENT' then 1
                 when 'EMAIL_OPEN' then 2
                . . .
            end), . . .
    from logs;

Your strategy of creating a new table is a much better idea than trying to update the existing one.

Converting varchar column values into integer in MySql

Sign up