MySQL的Limit Offset不起作用

Question

下面是我的查询语句，用于分页：

SELECT DISTINCT email_list.*, email_counter.phone as e_phone,email_counter.email as e_email,email_counter.marketing as e_marketing   
FROM Data_TLS_builders as email_list 
LEFT JOIN wp_pato_email_list_counters as email_counter on email_counter.email_id = email_list.URN    
LIMIT 120 OFFSET 150

与其从120开始，到150结束，应该显示30个结果，但是mysql返回了120个结果并忽略了偏移量。我尝试过LIMIT 120,150，但结果仍然相同。有什么解决办法吗？

- user892134

---

我认为你想要使用 LIMIT 30, OFFSET 120 来显示从第120行开始的30个结果。 - Kamehameha

在使用 LIMIT 时，应该始终使用 ORDER BY。否则，所选择的行是不确定的。 - Gordon Linoff

快来阅读文档 https://dev.mysql.com/doc/refman/5.0/en/select.html - paparazzo

Answer 1

SELECT DISTINCT email_list.*, email_counter.phone as e_phone,email_counter.email as e_email,email_counter.marketing as e_marketing FROM Data_TLS_builders as email_list LEFT JOIN wp_pato_email_list_counters as email_counter on email_counter.email_id = email_list.URN LIMIT 30 OFFSET 120

Limit指定记录数。

OFFSET指定要跳过多少条记录。

上述查询从121条记录中返回30条记录。

- Vishnu

Answer 2

您需要切换限制参数。

解决方案1：

SELECT DISTINCT email_list.*, email_counter.phone as e_phone,email_counter.email as e_email,email_counter.marketing as e_marketing FROM Data_TLS_builders as email_list LEFT JOIN wp_pato_email_list_counters as email_counter on email_counter.email_id = email_list.URN LIMIT 30 OFFSET 120

解决方案2:

SELECT DISTINCT email_list.*, email_counter.phone as e_phone,email_counter.email as e_email,email_counter.marketing as e_marketing FROM Data_TLS_builders as email_list LEFT JOIN wp_pato_email_list_counters as email_counter on email_counter.email_id = email_list.URN LIMIT 120, 30

你还可以查看文档：http://www.w3schools.com/php/php_mysql_select_limit.asp。

- Michael Stork

Answer 3

LIMIT指定要显示的结果数量。 OFFSET指定要从哪里开始。

如果您想显示从第120个开始的30个结果，应该是： LIMIT 30 OFFSET 120 或者 LIMIT 120, 30

- Carlo V