Mysql – Default value as expression from other columns

foreign keyMySQL

I have 2 tables:

users

user_id (int ai pk)
premium

premium_subscription

subscription_id (int ai pk)
user_id (int) -> fk to user.user_id
expiration (datetime)

I would like to implement a boolean value based on the fact if there's a subscription and its expiration date is ahead of the time I made the query.

I made this if statement, that works in a SELECT query. However, I'm not able to implement it as the value of a table.

IF(timestampdiff(SECOND, CURRENT_TIMESTAMP, (SELECT expiration FROM premium_subscription WHERE user_id = *)) > 0, 1, 0);

Is there any way to apply this expression to this column? Thanks in regards! ^^

Best Answer

DEFAULT has a lot of restrictions.

you can use here a daily EVENTs which runs every day and set premium accourdind to the expiration date

DROP EVENT IF EXISTS daily_event;
CREATE EVENT daily_event
ON SCHEDULE EVERY '1' DAY
STARTS '2020-12-12 00:00:01' -- mustbe in the future
DO
    UPDATE users u1
    INNER JOIN premium_subscription ps ON u1.user_id = ps.user_id
    SET premium = IF(NOW() > ps.expiration , 1, 0);

To enable the event scheduler see manual

Related Solutions

Mysql – Two nullable columns one required to have value

A version of JD Schmidt's answer, but without the awkwardness of an extra column:

CREATE TABLE foo (
  FieldA INT,
  FieldB INT
);

DELIMITER //
CREATE TRIGGER InsertFieldABNotNull BEFORE INSERT ON foo
FOR EACH ROW BEGIN
  IF (NEW.FieldA IS NULL AND NEW.FieldB IS NULL) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '\'FieldA\' and \'FieldB\' cannot both be null';
  END IF;
END//
CREATE TRIGGER UpdateFieldABNotNull BEFORE UPDATE ON foo
FOR EACH ROW BEGIN
  IF (NEW.FieldA IS NULL AND NEW.FieldB IS NULL) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '\'FieldA\' and \'FieldB\' cannot both be null';
  END IF;
END//
DELIMITER ;

INSERT INTO foo (FieldA, FieldB) VALUES (NULL, 10); -- OK
INSERT INTO foo (FieldA, FieldB) VALUES (10, NULL); -- OK
INSERT INTO foo (FieldA, FieldB) VALUES (NULL, NULL); -- gives error
UPDATE foo SET FieldA = NULL; -- gives error

Mysql – Identical query, tables, but different EXPLAIN and performance

Just make a simple join. Sub-queries does not provide the best result quite often

EXPLAIN SELECT l.id, l.level_name, l.date_published, l.rating
FROM levels AS l
INNER JOIN users_favorites AS uf 
ON uf.level_id = l.id
WHERE l.user_id = 2;

Best Answer

Related Solutions

Mysql – Two nullable columns one required to have value

Mysql – Identical query, tables, but different EXPLAIN and performance

Related Question