6

I have the same problem like this one: how to select dynamically in select * from <table_name> partiton (Partition name)? but in Mysql.

When using:

select concat('p', year(now()), month(now()));

Response is:

+----------------------------------------+
| concat('p', year(now()), month(now())) |
+----------------------------------------+
| p20153                                 |
+----------------------------------------+

When trying to use:

select max(ttime) from table partition(select concat('p', year(now()), month(now()))));

I get an 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 'select concat('p', year(now()), month(now()))))' at line 1

of course when trying select max(ttime) from table partition(p20153) works as expected:

root@localhost [tom]> select max(ttime) from table partition(p20153);
+---------------------+
| max(ttime)          |
+---------------------+
| 2015-03-16 09:54:34 |
+---------------------+
1 row in set (0.00 sec)

Notice the dynamic part is the partition name and not the table name. Sample data:

root@localhost [tom]> select id, ttime, value from table partition(p20153) limit 10;
+----------+---------------------+----------+
| id       | ttime               | value    |
+----------+---------------------+----------+
| 13964275 | 2015-03-01 00:05:11 | 16450824 |
| 13964291 | 2015-03-01 00:08:12 | 15964352 |
| 13964332 | 2015-03-01 00:09:42 | 14701984 |
| 13964379 | 2015-03-01 00:13:27 |    26128 |
| 13964411 | 2015-03-01 00:16:29 | 11073744 |
| 13964452 | 2015-03-01 00:20:34 | 14747992 |
| 13964486 | 2015-03-01 00:23:35 |   177800 |
| 13964507 | 2015-03-01 00:26:36 | 16786408 |
| 13964542 | 2015-03-01 00:28:28 |  8749552 |
| 13964571 | 2015-03-01 00:31:30 | 16932344 |
+----------+---------------------+----------+
10 rows in set (0.00 sec)
Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
eladelad
  • 99
  • 2
  • 10

2 Answers2

0

My solution is not clean but it worked (my case is creating a new partition with dynamic name). In your case, using SET to create variables for partition name as well as query.

SET @pt= concat('p', year(now()), month(now()));

SET @query = concat('select max(ttime) from table partition(', @pt, ')');

You can confirm partition name and query syntax by:

SELECT @pt, @query;

Now execute your query:

PREPARE stmt FROM @query;

EXECUTE stmt;
le hien
  • 131
  • 2
  • 15
-1

The Syntax you are using in your query is not right try this

select max(ttime) from 
(
  select concat('p', year(now()), month(now())) as ttime 
)
temp
Khurram Ali
  • 1,659
  • 4
  • 20
  • 37
  • Not sure how this query helps as ttime will get the partition name and I just select max of one value. I think I've mislead you, I've added sample data and expected result to the question. – eladelad Mar 16 '15 at 07:23