Hello,
At least I do not see any restriction on table partitioning mentioned in Unsupported MySQL features. The output from SHOW PLUGINS or INFORMATION_SCHEMA.PLUGINS shows support for InnoDB (i.e. "the only supported storage engine" and which supports table partitioning) as expected and partition (later is only for 5.7 as in 8.0 table partitioning is handled by the storage engine). I tried the following SQL on a Cloud SQL MySQL 5.7 instance:
And it was executed successfully. I then tested with:
INSERT INTO part_test (`name`, birth_date)
VALUES ('Person 1', '1979-09-20'),
('Person 2', '2005-12-12');
And finally checked that partitions are properly populated:
SELECT partition_name, partition_method, part_description, table_rows
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'part_test';
CREATE TABLE part_test (
id INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
birth_date DATE NOT NULL,
PRIMARY KEY (id, birth_date)
)
ENGINE = InnoDB
PARTITION BY RANGE( YEAR(birth_date) )
(
PARTITION part0 VALUES LESS THAN (1960),
PARTITION part1 VALUES LESS THAN (1970),
PARTITION part2 VALUES LESS THAN (1980),
PARTITION part3 VALUES LESS THAN (1990),
PARTITION part4 VALUES LESS THAN (2000),
PARTITION part5 VALUES LESS THAN (2010),
PARTITION part6 VALUES LESS THAN (MAXVALUE)
);
And it was executed successfully. I then tested with:
INSERT INTO part_test (`name`, birth_date)
VALUES ('Person 1', '1979-09-20'),
('Person 2', '2005-12-12');
And finally checked that partitions are properly populated:
SELECT partition_name, partition_method, part_description, table_rows
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'part_test';
+----------------+------------------+-----------------------+------------+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_DESCRIPTION | TABLE_ROWS |
+----------------+------------------+-----------------------+------------+
| part0 | RANGE | 1960 | 0 |
| part1 | RANGE | 1970 | 0 |
| part2 | RANGE | 1980 | 1 |
| part3 | RANGE | 1990 | 0 |
| part4 | RANGE | 2000 | 0 |
| part5 | RANGE | 2010 | 1 |
| part6 | RANGE | MAXVALUE | 0 |
+----------------+------------------+-----------------------+------------+
7 rows in set (0.05 sec)
| PARTITION_NAME | PARTITION_METHOD | PARTITION_DESCRIPTION | TABLE_ROWS |
+----------------+------------------+-----------------------+------------+
| part0 | RANGE | 1960 | 0 |
| part1 | RANGE | 1970 | 0 |
| part2 | RANGE | 1980 | 1 |
| part3 | RANGE | 1990 | 0 |
| part4 | RANGE | 2000 | 0 |
| part5 | RANGE | 2010 | 1 |
| part6 | RANGE | MAXVALUE | 0 |
+----------------+------------------+-----------------------+------------+
7 rows in set (0.05 sec)
You could find information about table partitioning in MySQL in the reference manual for 5.7 and 8.0 respectively.
I hope this helps.
Regards,
--
Georgi D. Sotirov
On Wednesday, January 6, 2021 at 10:15:35 PM UTC+2 venka...@gmail.com wrote:
Hello ,
Can we create Table partitions in cloud-sql ? Any document on this would be a great help.Thanks
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/58951ef9-8959-4305-99bb-7ad7a45f7b09n%40googlegroups.com.
No comments:
Post a Comment