from mysql partition select_MySQL 查询指定分区数据

今天写一个数据迁移脚本，需要对表按分区查询，在执行select * from xxx partition (p_xxx)的时候报错。

mysql> select * from sjkk_gcjl partition (p_20150105) ;

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 '(p_20150105)' at line 1后来发现是使用的版本5.5.41不支持对指定分区的查询。在5.6增强了分区表的分区的相关操作。其中包括支持了对指定分区的查询。具体增强的其他特性，可以查看官方文档

MYSQL 5.5的官方文档

SELECT

[ALL | DISTINCT | DISTINCTROW ]

[HIGH_PRIORITY]

[STRAIGHT_JOIN]

[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]

[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

select_expr [, select_expr ...]

[FROM table_references

[WHERE where_condition]

[GROUP BY {col_name | expr | position}

[ASC | DESC], ... [WITH ROLLUP]]

[HAVING where_condition]

[ORDER BY {col_name | expr | position}

[ASC | DESC], ...]

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

[PROCEDURE procedure_name(argument_list)]

[INTO OUTFILE 'file_name'

[CHARACTER SET charset_name]

export_options

| INTO DUMPFILE 'file_name'

| INTO var_name [, var_name]]

[FOR UPDATE | LOCK IN SHARE MODE]]

MYSQL 5.6的官方文档

SELECT

[ALL | DISTINCT | DISTINCTROW ]

[HIGH_PRIORITY]

[STRAIGHT_JOIN]

[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]

[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

select_expr [, select_expr ...]

[FROM table_references

[PARTITION partition_list]

[WHERE where_condition]

[GROUP BY {col_name | expr | position}

[ASC | DESC], ... [WITH ROLLUP]]

[HAVING where_condition]

[ORDER BY {col_name | expr | position}

[ASC | DESC], ...]

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

[PROCEDURE procedure_name(argument_list)]

[INTO OUTFILE 'file_name'

[CHARACTER SET charset_name]

export_options

| INTO DUMPFILE 'file_name'

| INTO var_name [, var_name]]

[FOR UPDATE | LOCK IN SHARE MODE]]