Check constraint with If-Else-Statement in MySQL?-CodePudding

Is it possible to define a check constraint within a table like this?

CREATE TABLE game
(
duration SMALLINT UNSIGNED NOT NULL CHECK ( duration = duration > 65535 ? duration/1000 : duration)
);

For context: I want to safe game data including the game duration. FOr some reason its sometimes stored in seconds and sometimes in milliseconds. Is this possible or do I have to do this If-Statement within the sourcecode outside of SQL like

CREATE TABLE game
(
duration SMALLINT UNSIGNED NOT NULL
);

int durationFromJSON = json.duration;
short duration = = durationFromJSON > 65535 ? durationFromJSON /1000 : durationFromJSON;
preparedStatement = connection.prepareStatement("INSERT INTO game(duration) VALUES ?");
preparedStatement.setShort(1, duration);
preparedStatement.executeUpdate();

Note: A game usually lasts between 10 and 60 minutes. In this case the duration checked of 65535 (limit of SMALLINT UNSIGNED) should be fine because a game will never last for only 65.535 seconds and never longer than 65,535 seconds.

CodePudding user response：

When you insert values into a table you can't process the value. There are 2 options: a trigger to modify the values after insert or a virtual column using an if or case statement.
Here is an example of the latter, which seems simpler: less code to add and all in the same table definition.

create table game(
id int,
dur int, 
duration int
GENERATED ALWAYS AS (case when dur > 65000 then dur /1000 else dur end) STORED);

insert into game (id, dur) values
(1,1000),
(2,100000);

select * from game;

id |    dur | duration
-: | -----: | -------:
 1 |   1000 |     1000
 2 | 100000 |      100

db<>fiddle here