Cycling through enums in MySQL

Question

Hey guys,

I have a table in my database with an enum column with 3 values:

('Offline', 'Online', 'Expired')

I want to be able to do an update that cycles the value of the this column to the next value in the list... and wrap back to the start.

i.e.

 'Offline' will change to 'Online'

 'Online' will change to 'Expired'

 'Expired' will change to 'Offline'

I understand that there are many ways to work around this, but I don't want to have to enter the values or total number of values.

Are there any built in functions to support this??

Is there a function that returns the length of an enum set of values?? <-- with this a mod() operation could be used to achieve the desired result.

This is where I got to in pseudo code:

UPDATE enum_tbl SET enum_col = (enum_col % count_of_poss_values) + 1;

And if not I believe there really should be..

Answer 1

It's not a very elegant solution, but if you are not using a strict SQL MODE you should be able to do this with 2 updates:

1) Increment all of the column values by 1. The columns that were set to the max enum value will be set to 0 and MySQL will throw a warning. 2) Then you can execute a second update to set those column values to 1 instead of 0.

Example:

mysql> create table my_table (
    -> id int not null primary key auto_increment,
    -> enum_column enum ('Offline', 'Online', 'Expired')
    -> );
Query OK, 0 rows affected (0.25 sec)

mysql> 
mysql> \W
Show warnings enabled.
mysql> insert into my_table (enum_column) values ('Offline'), ('Online'), ('Expired');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 
mysql> select * from my_table;
+----+-------------+
| id | enum_column |
+----+-------------+
|  1 | Offline     |
|  2 | Online      |
|  3 | Expired     |
+----+-------------+
3 rows in set (0.00 sec)

mysql> 
mysql> update my_table set enum_column = enum_column+1;
Query OK, 3 rows affected, 1 warning (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 1

Warning (Code 1265): Data truncated for column 'enum_column' at row 3
mysql> update my_table set enum_column = 1 where enum_column = 0;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> 
mysql> select * from my_table;
+----+-------------+
| id | enum_column |
+----+-------------+
|  1 | Online      |
|  2 | Expired     |
|  3 | Offline     |
+----+-------------+
3 rows in set (0.00 sec)