自动更改MYSQL中的字段 [英] Changing a field in MYSQL automatically

问题描述

我在mysql db中有一个users表，用于存储用户的出生日期.根据用户的出生日期，我在此表中有一个名为user_level的字段，该字段根据用户年龄填充1或2，即13岁以下为1，13岁以上为2.

I have a users table in a mysql db which stores a users date of birth. Based on the users date of birth I have a field in this table called user_level which is populated with 1 or 2 based on a users age ie 1 for under 13, or 2 for 13 and over.

有没有一种方法可以在用户生日设置为13岁时将其从1动态更改为2?

Is there a way to dynamically change this field from 1 to 2 when a users birthday takes place making them now 13?

推荐答案

使用mysql调度程序(只要您的mysql版本为5.1.6 +).

Use the mysql scheduler (so long as your mysql version is 5.1.6+).

CREATE EVENT birthdayEvent
  ON SCHEDULE
    EVERY 1 DAY
    STARTS '2015-05-01 04:00:00' ON COMPLETION PRESERVE ENABLE 
  DO
    update user set user_level = 2 where user_birthday = date(now() - interval 13 year);

确保使用set global event_scheduler = on启用了调度程序.然后，该活动将每天凌晨4点运行，并为13岁生日之前的每个用户更新用户级别.

Make sure the scheduler is enabled with set global event_scheduler = on. Then this event will run every day, at 4am, and update the user levels for everyone whose birthday fell 13 years ago.

这篇关于自动更改MYSQL中的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！