SELECT
data.TABLE_NAME,
CONCAT(
'PARTITION BY ',
data.PARTITION_METHOD,
'(',
data.PARTITION_EXPRESSION,
') PARTITIONS ',
MAX(data.PARTITION_ORDINAL_POSITION),
data.sub_var,
' (',
GROUP_CONCAT(
CONCAT(
'PARTITION ',
REPLACE(data.PARTITION_NAME, '`', ''),
'`',
IF(data.PARTITION_METHOD = 'RANGE', CONCAT(' VALUES LESS THAN (', data.PARTITION_DESCRIPTION, ')'),
IF(data.PARTITION_METHOD = 'LIST', CONCAT(' VALUES IN (', data.PARTITION_DESCRIPTION, ')'), '')
),
IFNULL(data.sub_body, CONCAT(' COMMENT="', REPLACE(data.PARTITION_COMMENT, '"', '\\"'), '"'))
)
ORDER BY data.PARTITION_ORDINAL_POSITION SEPARATOR ', '
),
')'
) AS PARTITION_SQL,
CONCAT(data.PARTITION_EXPRESSION, ',', IFNULL(data.SUBPARTITION_EXPRESSION, '')) AS COLUMNS_COMPARE
FROM (
SELECT
IF(ISNULL(PARTITIONS.SUBPARTITION_ORDINAL_POSITION), '', CONCAT(
' SUBPARTITION BY ',
PARTITIONS.SUBPARTITION_METHOD,
'(',
PARTITIONS.SUBPARTITION_EXPRESSION,
') SUBPARTITIONS ',
MAX(PARTITIONS.SUBPARTITION_ORDINAL_POSITION)
)) AS sub_var,
IF(ISNULL(PARTITIONS.SUBPARTITION_ORDINAL_POSITION), NULL, CONCAT(
' (',
GROUP_CONCAT(
CONCAT(
'SUBPARTITION `',
REPLACE(PARTITIONS.SUBPARTITION_NAME, '`', ''),
'`',
' COMMENT="',
REPLACE(PARTITIONS.PARTITION_COMMENT, '"', '\\"'),
'"'
)
ORDER BY PARTITIONS.SUBPARTITION_ORDINAL_POSITION SEPARATOR ', '
),
')'
)) AS sub_body,
PARTITIONS.TABLE_NAME,
PARTITIONS.PARTITION_NAME,
PARTITIONS.PARTITION_ORDINAL_POSITION,
PARTITIONS.PARTITION_METHOD,
PARTITIONS.PARTITION_EXPRESSION,
PARTITIONS.PARTITION_DESCRIPTION,
PARTITIONS.PARTITION_COMMENT,
PARTITIONS.SUBPARTITION_EXPRESSION
FROM information_schema.PARTITIONS
WHERE
PARTITIONS.TABLE_SCHEMA = 'am' AND
PARTITIONS.TABLE_NAME IN ('') AND
PARTITIONS.PARTITION_ORDINAL_POSITION IS NOT NULL
GROUP BY PARTITIONS.TABLE_NAME, PARTITIONS.PARTITION_NAME
) data
GROUP BY data.TABLE_NAME;
试试这个。