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;
        }
    }