Search code examples
mysqlmysql-8.0table-partitioningrange-partitions

MySQL RANGE table partitioning by months for 2 years records

I'm exploring on MySQL table partitioning and I have a table which have 2 years of records. I'm trying to create a new table which is similar to the current table I have so that I can copy the data from the table to this new table which the record will be partition by month for year 2021 and 2022. I have this query below, but this hits an error Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed which I don't really understand

CREATE TABLE TnTest (
  id bigint NOT NULL AUTO_INCREMENT,
  tnDate date NOT NULL,
  tnDateTime datetime NOT NULL,
  tnType varchar(40) NOT NULL,
  checksum varchar(255) NOT NULL,
  PRIMARY KEY (id, tnDate),
  UNIQUE KEY checksum (checksum, tnDate),
)ENGINE=InnoDB AUTO_INCREMENT=50015 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  PARTITION BY RANGE (extract(year_month from 'tnDate'))
(
  PARTITION P0 VALUES LESS THAN (202101),
  PARTITION P1 VALUES LESS THAN (202102),
  PARTITION P2 VALUES LESS THAN (202103),
  PARTITION P3 VALUES LESS THAN (202104),
  PARTITION P4 VALUES LESS THAN (202105),
  PARTITION P5 VALUES LESS THAN (202106),
  PARTITION P6 VALUES LESS THAN (202107),
  PARTITION P7 VALUES LESS THAN (202108),
  PARTITION P8 VALUES LESS THAN (202109),
  PARTITION P9 VALUES LESS THAN (202110),
  PARTITION P10 VALUES LESS THAN (202111),
  PARTITION P11 VALUES LESS THAN (202112),
  PARTITION P12 VALUES LESS THAN (202201),
  PARTITION P13 VALUES LESS THAN (202202),
  PARTITION P14 VALUES LESS THAN (202203),
  PARTITION P15 VALUES LESS THAN (202204),
  PARTITION P16 VALUES LESS THAN (202205),
  PARTITION P17 VALUES LESS THAN (202206),
  PARTITION P18 VALUES LESS THAN (202207),
  PARTITION P19 VALUES LESS THAN (202208),
  PARTITION P20 VALUES LESS THAN (202209),
  PARTITION P21 VALUES LESS THAN (202210),
  PARTITION P22 VALUES LESS THAN (202211),
  PARTITION P23 VALUES LESS THAN (202212),
  PARTITION P24 VALUES LESS THAN MAXVALUE
);

From the query, I was try to create a table which have a total of 24 partitions(consists of months in year 2021 and 2022) so that when I copy the record to this table it will be partition according to the month for the respective year.

Solution

  • When referencing column names, don't put them inside single-quotes. Use back-ticks for delimiting identifiers, or you may leave them bare (no quotes) if they don't need to be delimited.

    INCORRECT:

    PARTITION BY RANGE (extract(year_month from 'tnDate'))

    CORRECT:

    PARTITION BY RANGE (extract(year_month from tnDate))

    ALSO CORRECT:

    PARTITION BY RANGE (extract(year_month from `tnDate`))

    Putting the column name inside single-quotes makes it a string literal, not a column identifier.

    MySQL assumes when it sees a quoted string literal in your EXTRACT(YEAR_MONTH FROM ...)) that it might be a quoted date. I.e. you might have written EXTRACT(YEAR_MONTH FROM '2023-11-16')). This would be a legal expression in other queries, but this is not allowed as a partitioning expression. MySQL doesn't even check if the quoted string is a valid date, it returns the error because it's not allowed in the partitioning expression regardless.