How do I convert a number enum column to tinyint?

41
January 12, 2019, at 02:00 AM

Developing in Laravel 5.7, using a MySQL database. On a couple of my database columns I have the type of enum - didn't do my research and made the enum full of numbers (0-2, or 0-3). After reading the pros and cons, I want to move away from enums and convert them to tinyints.

What's the best way to change the type of the column in my table to tinyint and convert the strings '0','1','2','3' to tinyint?

I don't really want to lose my data in the process.

https://laravel.com/docs/5.7/migrations#modifying-columns has information about modifying columns, however it does not support enums:

Only the following column types can be "changed": bigInteger, binary, boolean, date, dateTime, dateTimeTz, decimal, integer, json, longText, mediumText, smallInteger, string, text, time, unsignedBigInteger, unsignedInteger and unsignedSmallInteger.

Answer 1

To be on a safe side I'd do this using temporary column;

ALTER TABLE tbl ADD COLUMN _temp_col CHAR(1) COLLATE 'latin1_general_ci'; -- CHAR(1) is OK if you only have numeric ENUMs
UPDATE tbl SET _temp_col = col; -- ENUM values would be copied as is
ALTER TABLE tbl MODIFY COLUMN col TINYINT(1) UNSIGNED;
UPDATE tbl SET col = _temp_col; -- Values would be auto-converted to ints
ALTER TABLE tbl DROP COLUMN _temp_col;
Answer 2

Experimenting with MySQL-8.0 generated the following conversion.

The ALTER TABLE seems to convert 'x' -> x+1. So I guess that be altered per the subsequent UPDATE below

select version();
| version() |
| :-------- |
| 8.0.13    |
create table x (y enum ('0','1','2','3') );
insert into x values ('1'),('0'),('2'),('3')
select * from x
| y  |
| :- |
| 1  |
| 0  |
| 2  |
| 3  |
alter table x modify y tinyint unsigned;
select * from x;
|  y |
| -: |
|  2 |
|  1 |
|  3 |
|  4 |
update x set y=y-1
select * from x
|  y |
| -: |
|  1 |
|  0 |
|  2 |
|  3 |

db<>fiddle here

READ ALSO
Ask for a quick update method for records that containing duplicate values

Ask for a quick update method for records that containing duplicate values

I have a table with millions of records, but each object could have multiple columns that share the same values while some other columns have different values

43
How to enter username in form and then search in database to produce if else statement

How to enter username in form and then search in database to produce if else statement

How would I get the $UN Variable to be set by the input provided by the formThen use the variable to search in the database to provide a users details

28
WHERE IN clause with multiple values

WHERE IN clause with multiple values

I'm trying to select items from a table when any values from a list appear in an array in the table

29
Best practice for storing HTML in mysql DB

Best practice for storing HTML in mysql DB

I'm working on a mini blog which allows users to add a post using a WYSIWYG editor to the site therefore I will be storing this post in my DB

20