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.

Answer 2

Alternative:

ALTER TABLE x MODIFY COLUMN logtype
      ENUM('unk', 'EMAIL_SENT', 'EMAIL_OPEN', ...);

That will be one pass, 1 byte per column, still show (for read/write as strings), etc.

create table so36768171 ( logtype VARCHAR(22) );
INSERT INTO so36768171 VALUES ('EMAIL_SENT'),('EMAIL_SENT'),('EMAIL_OPEN'),('junk');
ALTER TABLE so36768171 MODIFY COLUMN logtype ENUM('unk', 'EMAIL_SENT', 'EMAIL_OPEN');
  Records: 4  Duplicates: 0  Warnings: 1
show warnings;
  +---------+------+----------------------------------------------+
  | Level   | Code | Message                                      |
  +---------+------+----------------------------------------------+
  | Warning | 1265 | Data truncated for column 'logtype' at row 4 |
  +---------+------+----------------------------------------------+
SELECT logtype, 0+logtype FROM so36768171;
  +------------+-----------+
  | logtype    | 0+logtype |
  +------------+-----------+
  | EMAIL_SENT |         2 |
  | EMAIL_SENT |         2 |
  | EMAIL_OPEN |         3 |
  |            |         0 |
  +------------+-----------+
  4 rows in set (0.00 sec)