mysql虚拟列处理日期

mysql 虚拟列

CREATE TABLE `table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `buy_time` int(10) unsigned NOT NULL COMMENT '时间',
  `buy_day` int(9) GENERATED ALWAYS AS (date_format(from_unixtime(`buy_time`),'%Y%m%d')) VIRTUAL,
  `buy_month` int(9) GENERATED ALWAYS AS (date_format(from_unixtime(`buy_time`),'%Y%m')) VIRTUAL,
  `buy_week` int(9) GENERATED ALWAYS AS (yearweek(date_format(from_unixtime(`buy_time`),'%Y%m%d'),1)) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `idx1` (`buy_day`),
  KEY `idx2` (`buy_month`),
  KEY `idx3` (`buy_week`)
) ENGINE=InnoDB AUTO_INCREMENT=228214 DEFAULT CHARSET=utf8 COMMENT='用户科目表';

创建虚拟列：

alter table 表名 add column 虚拟列名 varchar(255) GENERATED ALWAYS AS (json_extract(`attach`,'$.虚拟列名')) VIRTUAL;

# yearweek语法
alter table user add column buy_day int(9) GENERATED ALWAYS AS (FROM_UNIXTIME(buy_time,'%Y-%m-%d')) VIRTUAL,add column buy_month int(9) GENERATED ALWAYS AS (FROM_UNIXTIME(buy_time,'%Y-%m')) VIRTUAL,add column buy_week int(9) GENERATED ALWAYS AS (YEARWEEK(FROM_UNIXTIME(buy_time,'%Y-%m-%d'))) VIRTUAL;
alter table crm_course add index idx_bday(buy_day),add index idx_bmonth(buy_month),add index idx_bweek(buy_week);

# 
alter table user modify column buy_day int(9) GENERATED ALWAYS AS (FROM_UNIXTIME(buy_time,'%Y%m%d')) VIRTUAL,modify column buy_month int(9) GENERATED ALWAYS AS (FROM_UNIXTIME(buy_time,'%Y%m')) VIRTUAL,modify column buy_week int(9) GENERATED ALWAYS AS (YEARWEEK(FROM_UNIXTIME(buy_time,'%Y%m%d'), 1)) VIRTUAL;

修改虚拟列：

alter table 表名 modify column 虚拟列名 varchar(255) GENERATED ALWAYS AS (json_extract(`attach`,'$.虚拟列名')) VIRTUAL;

参考：
http://www.sohu.com/a/223918804_610509
http://www.xz577.com/j/17021.html
https://www.cnblogs.com/ganymede/p/3811873.html

php处理第几周：

系统自带的date(‘W’,time()） 有bug 比如，一年中的第一天如果不是周一的话，返回值是
52或者53。 可以实测一下。就是说， 系统默认认为这一天是去年的第52/53周。

/*

没有问题： 20190101 周二， 周数 201901
*/
echo get_weeks_num('2017-01-01');
 
function get_weeks_num($time){
        $month = intval(date('m',$time));//当前时间的月份
        $fyear = strtotime(date('Y-01-01',$time));//今年第一天时间戳
        $fdate = intval(date('N',$fyear));//今年第一天 周几
        $sysweek = intval(date('W',$time));//系统时间的第几周
        //大于等于52 且 当前月为1时， 返回1
        if(($sysweek >= 52 && $month == 1)){
            return 1;
        }elseif($fdate == 1){
            //如果今年的第一天是周一,返回系统时间第几周
            return $sysweek;
        }else{
            //返回系统周+1
            return $sysweek + 1;
        }
    }