见识到另外一种创意，惊讶！

　　1.创建小数据表 0-9

# 创建小数据表 0-9DROP TABLE IF EXISTS aa_numbers_small;CREATE TABLE aa_numbers_small(    number INT);INSERT INTO aa_numbers_small VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);SELECT * FROM aa_numbers_small;

　　2.创建大数据表 0-10000

# 创建大数据表 0-10000DROP TABLE IF EXISTS aa_numbers;CREATE TABLE aa_numbers(number BIGINT);INSERT INTO aa_numbersSELECT thousand.number*1000+hundred.number*100+ten.number*10+ones.numberFROM aa_numbers_small thousand, aa_numbers_small hundred, aa_numbers_small ten, aa_numbers_small onesLIMIT 1000000;SELECT * FROM aa_numbers;

　　3.创建时间维表

# 创建时间维表DROP TABLE IF EXISTS aa_date;CREATE TABLE aa_date(    date_id          BIGINT PRIMARY KEY,    DATE             DATE NOT NULL,    DAY              CHAR(10),    day_of_week      INT,    day_of_month     INT,    day_of_year      INT,    previous_day     DATE NOT NULL DEFAULT '0000-00-00',    next_day         DATE NOT NULL DEFAULT '0000-00-00',    weekend          CHAR(10) NOT NULL DEFAULT "Weekday",    week_of_year     CHAR(2),    MONTH            CHAR(10),    month_of_year    CHAR(2),    quarter_of_year INT,    YEAR             INT,    UNIQUE KEY `date` (`date`));SELECT * FROM aa_date;

　　4.填充数据

# 填充数据INSERT INTO aa_date(date_id, DATE)SELECT number, DATE_ADD('2018-01-01', INTERVAL number DAY)FROM aa_numbersWHERE DATE_ADD('2018-01-01', INTERVAL number DAY) BETWEEN '2018-01-01' AND '2018-12-31'ORDER BY number;

　　5.根据日期update其他列数据

# 根据日期update其他列数据UPDATE aa_date SETDAY             = DATE_FORMAT( DATE, "%W" ),day_of_week     = DAYOFWEEK(DATE),day_of_month    = DATE_FORMAT( DATE, "%d" ),day_of_year     = DATE_FORMAT( DATE, "%j" ),previous_day    = DATE_ADD(DATE, INTERVAL -1 DAY),next_day        = DATE_ADD(DATE, INTERVAL 1 DAY),weekend         = IF( DATE_FORMAT( DATE, "%W" ) IN ('Saturday','Sunday'), 'Weekend', 'Weekday'),week_of_year    = DATE_FORMAT( DATE, "%V" ),MONTH           = DATE_FORMAT( DATE, "%M"),month_of_year   = DATE_FORMAT( DATE, "%m"),quarter_of_year = QUARTER(DATE),YEAR            = DATE_FORMAT( DATE, "%Y" );

　　6.日期函数测试

# 测试SELECT DATE_FORMAT('2018-05-29', '%W'); # TuesdaySELECT DAYOFWEEK('2018-05-29'); # 3SELECT DATE_FORMAT('2018-05-29', '%d'); # 29SELECT DATE_FORMAT('2018-05-29', '%j'); # 149SELECT DATE_ADD('2018-05-29', INTERVAL -1 DAY); # 2018-05-28SELECT DATE_SUB('2018-05-29', INTERVAL 1 DAY); # 2018-05-28SELECT DATE_ADD('2018-05-29', INTERVAL 1 DAY); # 2018-05-30SELECT IF(DATE_FORMAT('2018-05-26', '%W') IN ('Saturday','Sunday'), 'Weekend', 'Weekday'); # WeekendSELECT DATE_FORMAT('2018-05-29', '%V'); # 21SELECT DATE_FORMAT('2018-05-29', '%M'); # MaySELECT DATE_FORMAT('2018-05-29', '%m'); # 05SELECT QUARTER('2018-05-29') # 2SELECT DATE_FORMAT('2018-05-29', '%Y');# 2018

---

END 2018-05-29 14:34:57