如何从 MySQL 中的分区中选择行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14112283/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to select rows from partition in MySQL
提问by Kad
I made partition my 300MB table and trying to make select query from p0partition with this command
我对我的 300MB 表进行了分区，并尝试p0使用此命令从分区中进行选择查询
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?
如何编写选择查询以从特定分区获取数据？
回答by Ravi
Depending on you MySql version, PARTITIONkeyword 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
根据您的 MySql 版本，PARTITION关键字在MySQL 5.6.2之前不存在。您将使用 MySQL 5.5 甚至 5.1，但不会使用 5.6。如果您正在使用MySQL 5.1，那么您可以做一些如下的解决方法
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'
注意：您可以更改table_schema = schema()为table_schema = 'yourschema'
回答by rudygodoy
Actually since MySQL 5.6 the supported syntax is:
实际上从 MySQL 5.6 开始支持的语法是：
SELECT * FROM table PARTITION (partitionName);
回答by Hanky Panky
回答by Omer Mohamed Nafaal
The correct form of the queryis as below it works for me fine.
的正确形式query如下所示，它对我很有效。
select * from employees partition (`p0`);
回答by bonCodigo
It's not supported in current version of MYSQL.
当前版本的 MYSQL 不支持它。
Check this question on DBA. You may also check out MYSQL dev article
回答by Toby Allen
I think its worth pointing out to others that may stumble upon this page, that a 300MB table does not need a partition.
我认为值得向可能偶然发现此页面的其他人指出，300MB 的表不需要分区。
300MB is a trivial amount of data for any modern (or even not so modern) database and thus everything will work better if you leave your data in one table in one database in one partition.
300MB 对于任何现代（甚至不那么现代）的数据库来说都是微不足道的数据量，因此，如果您将数据保留在一个分区的一个数据库中的一个表中，一切都会更好。
回答by Mohammad Ali Abdullah
SELECT * FROM invoice_detailPARTITION (p1);
从invoice_detail分区中选择 * (p1);
