from mysql partition select_How to select rows from partition in MySQL

可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效，请关闭广告屏蔽插件后再试):

问题:

I made partition my 300MB table and trying to make select query from p0 partition with this command SELECT * FROM employees PARTITION (p0);

But I am getting following error ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(p0)' at line 1

How to write select query to get data from specific partition?

回答1:

Depending on you MySql version, PARTITION keyword does not exist until MySQL 5.6.2. You would be using MySQL 5.5 or even 5.1, but not 5.6. In case, you are using MySQL 5.1, then you can do some workaround like below SELECT partition, count(ID) FROM ( SELECT ID, case when condition then p1 when condition then p2 ..... end as partition FROM table ) s1 GROUP BY partition

Note : The above solution is just workaround to get you desire output.

You may also try this query to count total number of rows for your partition. SELECT table_rows as 'count(*)' FROM information_schema.partitions WHERE table_schema = schema() and table_name ='employees' and partition_name = 'p0';

Note : you may change table_schema = schema() to table_schema = 'yourschema'

回答2:

Actually since MySQL 5.6 the supported syntax is: SELECT * FROM table PARTITION (partitionName);

回答3:

You are right, explicit selection of PARTITION is not supported in 5.1.54 Version. See this post

回答4:

Not sure why my answer was converted into a comment ;)

Putting it back. Check this question on DBA. It's not supported in current version of MYSQL.

You may also check out MYSQL dev article

回答5:

CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE) PARTITION BY LIST COLUMNS (name) (PARTITION p0 VALUES IN ('ETH/USD'), PARTITION p1 VALUES IN('BTC/USD'), PARTITION p2 VALUES IN('BTC/KES'), PARTITION p3 VALUES IN('ETH/KES'));

2.select PARTITION_NAME from information_schema.partitions;

INSERT INTO trb3 VALUES ('1', 'ETH/USD', NULL), ('2', 'BTC/USD', NULL), ('3', 'BTC/KES', NULL), ('4', 'BTC/KES', NULL), ('5', 'ETH/KES', NULL),

('6', 'ETH/KES', NULL), ('7', 'BTC/USD', NULL), ('8', 'BTC/USD', NULL), ('9', 'ETH/USD', NULL), ('10', 'ETH/USD', NULL) ; select * from trb3 PARTITION(p0);

select * from trb3 PARTITION(p3);

INSERT INTO trb3 PARTITION(p3) VALUES ('11', 'ETH/KES', NULL);