How to use math expression in mysql sql?

Question

See below. If use like this:

select product_id, name from product limit (1-1)*5, 5; 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 '(1-1)*5, 5' at line 1

but if use like this:

select (1-1)*5 ; +---------+ | (1-1)*5 | +---------+ | 0 | +---------+ It's ok.

How to use math expression in first statement？

dev.mysql.com/doc/refman/5.5/en/select.html: “LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants.” And it goes on to list two exceptions, placeholders in prepared statements, and parameters or variables in a stored procedure (the latter only from 5.5.6 on). So looks like other than that, you can use only integers there, and not expressions. — C3roe
– C3roe, Commented Jan 18, 2015 at 3:26
Why would you need to do that in the first place? It looks like you are working on some pagination functionality, so why wouldn't you just calculate the page number in your application logic? — Mike Brant
– Mike Brant, Commented Jan 18, 2015 at 3:54

C3roe · Accepted Answer · 2015-01-18 03:42:44Z

2

http://dev.mysql.com/doc/refman/5.5/en/select.html says,

LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants.

And it goes on to list two exceptions,

placeholders in prepared statements, and
parameters or variables in a stored procedure (the latter only from 5.5.6 on).

So looks like other than that, you can use only integers there, and not expressions.

answered Jan 18, 2015 at 3:42

C3roe

97.7k15 gold badges100 silver badges172 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

zhuguowei Over a year ago

Thanks. It seems that if want to support this funtion, you have to modify mysql source code.

Collectives™ on Stack Overflow

How to use math expression in mysql sql?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related